By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,677 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,677 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
jb****@panix.com

Nov 21 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Nov 21 '05 #2

P: n/a
In article <11*********************@g44g2000cwa.googlegroups. com>,
Harlan Grove <hr*****@aol.com> 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
jb****@panix.com

Nov 21 '05 #3

P: n/a
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 <df**********@reader1.panix.com>, jb****@panix.com says...
In article <11*********************@g44g2000cwa.googlegroups. com>,
Harlan Grove <hr*****@aol.com> 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.

Nov 21 '05 #4

P: n/a
In article <MP************************@msnews.microsoft.com >,
Tushar Mehta <tm****************@tushar-mehta.SeeOhEm> 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 <df**********@reader1.panix.com>, jb****@panix.com says...
In article <11*********************@g44g2000cwa.googlegroups. com>,
Harlan Grove <hr*****@aol.com> 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
jb****@panix.com

Nov 21 '05 #5

P: n/a
In article <df**********@reader1.panix.com>, jb****@panix.com says...
In article <MP************************@msnews.microsoft.com >,
Tushar Mehta <tm****************@tushar-mehta.SeeOhEm> 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 <df**********@reader1.panix.com>, jb****@panix.com says... In article <MP************************@msnews.microsoft.com >,
Tushar Mehta <tm****************@tushar-mehta.SeeOhEm> 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 <df**********@reader1.panix.com>, jb****@panix.com says...
In article <11*********************@g44g2000cwa.googlegroups. com>,
Harlan Grove <hr*****@aol.com> 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.


Nov 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.