How do I find all named ranges in VB.NET?
 

I am using VB.NET to read Excel workbooks which have various named
ranges, some of which may not exist in any given workbook. I am
trying to get a list of all the range names -- otherwise I need to
use a Try block to avoid throwing an exception when I try to read
data from ranges which aren't there, and this slows things down
considerably.

I am able to find the *number* of named ranges in a particular
workbook using this expression:

xlApp.ActiveWorkbook.Names.Count

(where xlApp is my open Excel application handle, typed as
Microsoft.Office.Interop.Excel.Application). And knowing the names
I can retrieve the ranges themselves using:

xlApp.ActiveWorkbook.Names.Item("MyRangeName").Ref ersToRange

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?

(I'd also like to get a list of styles in the workbook, and I
suspect this is the same problem, since I can get the number of
styles defined using xlApp.ActiveWorkbook.Styles.Count).
--
John Brock

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.

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.
--
John Brock

Does Console have a Debug member? VS Intellisense indicated otherwise.

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

--
Regards,

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

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.

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.



--
John Brock

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.