Calling Named Ranges in Macro

Re: Calling Named Ranges in Macro

Robert,

Very close. Try

Range("Name").Select

Hope this helps.

Regards,
Batman.

Re: Calling Named Ranges in Macro

Robert,

The fact that a particular range name does not exist within your workbook should not cause a compile error, which implies there is something fundamentally wrong with the structure of the VBA code.

Declaring a variable is mandatory if you have used Option Explicit at the top of your code module, but if a variable didn't exist the compile error would say that it didn't exist.

Could you perhaps post more of your code so I can have a better look at what might be wrong?

Regards,
Batman.

Re: Calling Named Ranges in Macro

Robert,

Is Name a variable intended to hold the range name? If so, I misunderstood as I assumed that Name was just a representation of the fact that you would hard-code a name in that statement.

If Name is a variable, yes it should be declared. If you have declared it as a range variable you would need to use

Range(Name.Address).Select

otherwise, if you have declared it as a string you should be able to use

Range(Name).Select

Hope this helps.

Regards,
Batman.

Re: Calling Named Ranges in Macro

Robert,

The best way to work with your range names depends on a number of things - whether the names or number of ranges is likely to change over time, how often thet are referred to in your program, what you want to do with them, etc.

Let's say your 6 ranges are named "MyRange1" to "MyRange6".

If you only ever refer to each range once in the program, and the range names will never change, you can simply hard code the name in your program without declaring a variable, e.g.

Range("MyRange1").Select

You can refer to the range in a similar way by holding the name of the range in a string variable, e.g.

Dim strRange1 As String


strRange1 = "MyRange1"
Range(strRange1).Select

As an alternative to this, you could declare the variable with

Const strRange1 As String = "MyRange1"

which removes the need for the separate statement to assign the value, but you can't change the value of that variable elsewhere in the program.

You can also assign the range to a range variable, which is done and used as follows:

Dim rngRange1 As Range
Dim strAddress As String


Set rngRange1 = ActiveSheet.Range("MyRange1")
strAddress = rngRange1.Address
rngRange1.Select

In this example rngRange1 becomes a reference to the range of cells itself, and can use the properties and methods of the cells. In the code below, you will see the code needed using the 3 methods explained above to set the value of a range:

' Method 1
ActiveSheet.Range("MyRange1").Value = 50
' Method 2
ActiveSheet.Range(strRange1).Value = 50
' Method 3
rngRange1.Value = 50

Let me know if there's anything that's still not clear.

Regards,
Batman.

Re: Calling Named Ranges in Macro

Robert,

By the way, if you have created a range name in Excel, and you refer to it in VBA, VBA will recognise the name if you try to refer to it and will identify the address of the range from that.

To see how VBA will do this, go to the Visual Basic Editor, Immediate Pane (Ctrl + G) and type (with your workbook containing the names active)

? ActiveWorkbook.Names(1).Name [Enter]
? ActiveWorkbook.Names(1).Address [Enter]

Regards,
Batman.