adding a VBA name to workbook names collection

[brachistochrone]

Hi,

The variable rng_MGXdataSemiAnnual exists in the dropdown box in the
worksheet and refers to a matrix in the worksheet. There is a similar
matrix on a number of different worksheets, each with its own name

In the VBA, I define a new variable rng_MXGdata:

rng_MXGdata = [rng_MXGdataSemiAnnual]

This new variable is not recognised as a name in the names collection.
I tried using the following:

Names.Add Name:="rng_MGXdata", RefersToLocal:=rng_MGXdata

but it doesn't seem to make rng_MXGdata appear in the dropdown box.
Perhaps this is because you can't have two names referring to the same
ranges? Do I have to delete rng_MXGdataSemiAnnual from the names
collection before rng_MGXdata can be recognised? I need it as part of
the name collection so I can use it in commands like the following:

Offset(3).Formula = "=VLOOKUP(R[-3]C,rng_MXGdata,3,FALSE)"

I will also need to finally delete rng_MXGdata from the "workspace" and
restore the original rng_MXGdataSemiAnnual so if anyone can suggest how
to do go about this I would be grateful.

Your help is appreciated.

Thanks,

Edward

 

[Otto Moehrbach]

I take it that you have a named range in the worksheet and you want to
assign another name to the same range. Say the range is now named Doodle.
Say you want to name it NextDoodle also.
A worksheet range can be named as many different names as you wish.
Do this:
Range("Doodle").Name = "NextDoodle"
When you set a VBA variable to a worksheet range, you are not naming that
range that variable.
If you have already set a VBA variable name (say MyVBAVar) to some worksheet
range, and you want to assign a worksheet name (say Doodle) to that range,
do this:
MyVBAVar.Name = "Doodle"
HTH Otto
"brachistochrone"

 

[brachistochrone]

Thanks for your help,
E

 

[brachistochrone]

Hi again,

I tried using the MyVBAVar.Name="Doodle" command that you suggested to
name the VBA variable rng_MXGdata as "rng_MXGdata".
So I was using:

rng_MXGdata.Name="rng_MXGdata"

I get the following error window:
Run-time error '424'
Object required

Do you know what the problem is? The only time the VBA variable appears
before the naming line is in:

rng_MXGdata=[rng_MXGdataSemiAnnual]

I have checked the VBA variable rng_MXGdata in the watch window and the
data seems to be in there.
Any idea what is going on?
Thanks for your help.

Edward

 

[Otto Moehrbach]

The reason for the error probably is that you haven't set the variable name
to that range yet. Look at this code. This code sets the variable name to
the range in this line:
Set MyVBAVar = Range("A1:C5")
If the range is already named (worksheet name), say FirstDoodle, then you
can set the variable either with the above line or with this line:
Set MyVBAVar = Range("FirstDoodle")
But the variable must be set (defined) before you can apply a name to that
range by using the variable name.
The two message boxes display the addresses of the MyVBAVar range and the
"Doodle" range. They should be the same address. Otto

Sub TestNames()
Dim MyVBAVar As Range
Set MyVBAVar = Range("A1:C5")
'Above sets the variable name
'It does NOT name the range.
MsgBox "MyVBAVar address is: " & _
MyVBAVar.Address(0, 0)
MyVBAVar.Name = "Doodle"
'Above DOES name the range
MsgBox "The Doodle address is: " & _
Range("Doodle").Address(0, 0)
End Sub

"brachistochrone"