Access Excel named ranges through a loop using VB.Net

Question

I'm trying to use visual basic for applications to loop through a list of named ranges from an excel sheet as the first step in a process for altering their formulas. Its all part of an Excel plug in I work with using Visual Studo I have a basic loop, which came directly from the MS docs.


        Dim n As Excel.Name

        Dim names As Excel.Names = destStreetEstimates.Names

        Dim test_string As String = destStreetEstimates.Name
        MsgBox(test_string)

        For Each n In names
            MsgBox(Prompt:=n.Name)
        Next n

This doesn't work, it looks like com objects are being put together into a list when I turn on the VS debugger (yay), but the com objects aren't being turned into anything useful I can loop through. (boo).

Am I missing something? I've gone through the Docs several times, nothing I've read there matches with the errors I'm getting. But I'm still pretty green.

https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-named-ranges

I've looked at other guides, most of them seemed to be discussing how to programmatically generate named ranges. I have to start with the ranges I've been given, its about 70, and while I have all their names in a config file there's a good chance the sheet will have additional names I haven't been given in the future and my function has to work in that case too.

donPablo · Accepted Answer · 2022-02-19 01:12:16Z

0

See https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges

Sub NamedRange_Loop()
'PURPOSE: Named Ranges in the Active Workbook
'SOURCE: www.TheSpreadsheetGuru.com

Dim nm As Name

'Loop through each named range in workbook
  For Each nm In ActiveWorkbook.Names
    Debug.Print nm.Name, nm.RefersTo
  Next nm
  
'Loop through each named range scoped to a specific worksheet
  For Each nm In Worksheets("Sheet1").Names
    Debug.Print nm.Name, nm.RefersTo
  Next nm

End Sub

answered Feb 19, 2022 at 1:12

donPablo

1,9691 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access Excel named ranges through a loop using VB.Net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related