ExcelBanter - View Single Post - How do I find all named ranges in VB.NET?

Tushar Mehta

In article , says...
In article ,
Tushar Mehta wrote:

Damn! I was doing it correctly from the beginning, except I was
using 0-based indexing, and misunderstood the error message. Ouch!
I'm new to VB.NET, and there is a lot I like about it, but while
I am sure there are valid historical reasons for mixed indexing it
is still a huge misfeature. Personally I actually prefer 1-based
indexing -- which I think makes me a minority -- but please, one
or the other!!!

LOL! Yes, it can get confusing, but that inconsistency has been around
for a while. Even if we restrict ourselves to Office, the userform
components (such as ListBox) have been zero-relative whereas
application collections have always been 1-relative.

The same issues affect .Net. It would have been nice if MS had carried
forward the VB tradition of any lower bound for an array instead of
forcing it to be zero. However, this requirement applies only to .Net
*arrays.* The Names collection, on the other hand, is a collection and
also a part of the XL object model.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
In article ,
Tushar Mehta wrote:
Does Console have a Debug member? VS Intellisense indicated otherwise.

Um..., I meant Debug.Writeline. :-/

The following works fine. Two things worth noting. Usually, I have
Option Strict On. That disallows late binding and requires all the
type casts in the code. Also, VS Intellisense indicated that the Names
collection has no default property.

Dim i As Integer
With ThisApplication.ActiveWorkbook
For i = 1 To .Names.Count
CType(CType(.ActiveSheet, Excel.Worksheet). _
Cells(i, 1), Excel.Range).Value = _
.Names.Item(i).Name
Next i
End With

Damn! I was doing it correctly from the beginning, except I was
using 0-based indexing, and misunderstood the error message. Ouch!
I'm new to VB.NET, and there is a lot I like about it, but while
I am sure there are valid historical reasons for mixed indexing it
is still a huge misfeature. Personally I actually prefer 1-based
indexing -- which I think makes me a minority -- but please, one
or the other!!!

In article , says...
In article . com,
Harlan Grove wrote:
John Brock wrote...
...
But try as I might I can't seem to figure out how to get a list of
the actual names! I tried giving the Item property an integer
index, but that throws an exception. Can anyone give me a clue?

VB.NET provides an object type for Excel defined names, doesn't it? If
that type were Microsoft.Office.Interop.Excel.Name, then try

Dim n As Microsoft.Office.Interop.Excel.Name

For Each n In xlApp.ActiveWorkbook.Names
MsgBox Prompt:=n.RefersToRange.Address, Title:=n.Name
Next n

If you're coding in Visual Studio, doesn't it provide an object
browser? If so, you should be able to find all the Excel object types
in it.

Visual Studio shows me plenty of object types, I just can't get
anything to work.

I tried your suggestion, but it still doesn't work. I didn't want
to throw up messages boxes, so I did it this way:

Dim n As Microsoft.Office.Interop.Excel.Name

For Each n In xlApp.ActiveWorkbook.Names
Console.Debug("Name is: " & n.Name)
Next

I get a COMException exception, with the additional information
"Member not found".

I wonder if this could be related to the fact that I couldn't even
define an enumerator for the Names property:

Dim e as IEnumerator = xlApp.ActiveWorkbook.Names.GetEnumerator

This statement also throws an the same exception, and yet GetEnumerator
is one of the methods that Visual Studio suggests for Names.