CODEWITHSUNDEEP
excelvba
MagicM
MagicM

Reputation: 37

Is there a way of accessing a named range without knowing which sheet it is on in VBA Excel?

I have a sheet with a column of cells, each referring to a named range. Each of these named ranges are located on other sheets and I don't know on which sheets they are, I only know the names of the ranges. Each range has a unique name so there is no risk of having duplicates of ranges, they are clearly identifiable in Name Manager. I want the VBA to access these ranges and change their values.

The usual Range().select is obviously not working, because I don't know on which sheet the range is. I could brute force it and for every named range go through each sheet to find the range, but that is clearly very inefficient. Alternatively, if I could go to the range referred to by each cell that would also do the job.

Effectively, I have many cells with function like =in_total_pop. I don't know on which sheet the in_total_pop is saved, but I want to access in_total_pop.

Is there any way to achieve that? It looks to me like a surprising simple task and I struggle to think of a way to achieve that. I am a fan of named ranges but if they cannot be accessed without knowing on which sheet they are then it is a substantial disadvantage. The default naming method at least have the sheet address in the name (e.g. ='All costs'!M29), which can then be deciphered to find the range by VBA. Thank you for your help and please let me know if I am missing something obvious.

Upvotes: 1

Views: 1657

Answers (2)

AJD
AJD

Reputation: 2438

You can use the Names collection in the workbook.

For example: ThisWorkbook.Names("Bob").RefersTo. There are a few "refersTo…" options to choose from.

For further reading:

Edit:

Just to clarify, while I used ThisWorkBoook in my example, the Names element is an intrinsic collection in the Workbook object. So, if you have referenced some other workbook, you can still call the Names functionality.

For example:

Dim wb as Workbook
Dim rng as Range
Set wb = Application.Workbooks.Open("SomeFileName")
Set rng = wb.Names("Bob").RefersToRange

Upvotes: 4

manu197a
manu197a

Reputation: 110

One option could be to get the address from the range. For instance:

My_variable = Range("my_range").Address(, , , True)

With the fourth parameter set to TRUE you will get the workbook name and the sheet where the range is stored. With that instruction, you wil get something like this stored in My_variable: 

[Workbook1]Sheet1!$G$11

You can isolate this information with some code like the following. Substitute "My_range" with your range name:

Sub Get_Range_Information()

    Dim My_variable As Variant
    Dim Sheet_name As String
    Dim Range_address As String

    My_variable = Split(Range("My_range").Address(, , , True), "!")
    Sheet_name = Right(My_variable(0), Len(My_variable(0)) - InStrRev(My_variable(0), "]"))
    Range_address = My_variable(1)

   Debug.Print Sheet_name
   Debug.Print Range_address

End Sub

You can then use the variables to assign a new value to the same range.

Upvotes: 1

Related Questions