Refer to a Name saved prior my macro Starts from VBA excel
 

Hi!

1)
If I have a name, saved prior a start my macro eg:

MyArray=("Bush","Mitterrand", "Blair")

how can I refer to Myarray(1) from VBA to get the following answer:

MyArray(1)=Miterrand

2)
1)
how can I refer to the value of Screen_Number from VBA.
at the moment I am using this code:

SUB aa()
dim Y
Y=Names("Screen_Number").Value
Y = Mid(Y, 2, 10)
MSGBOX Y
END SUB
"This works but I suppose there is another better way"



Many Thanks!

Refer to a Name saved prior my macro Starts from VBA excel
 

'-------------------------------------
Option Base 1 ' top of module otherwise array starts at zero
Sub test()
Dim MyArray As Variant
MyArray = Array("Bush", "Mitterrand", "Blair")
MsgBox (MyArray(1))
End Sub
'----------------------------------


Names refer to *Ranges* and can be at WorkBook or WorkSheet level. Thi
can be programmed, but, if done manually, Excel makes the firs
instance at WB level and subsequent use of the same name at shee
level.

Even though your code works, it is more correct to refer to a range, s
this works:-
Y = Mid(Range("Screen_Number").Value, 2, 10)

This is, however, bad (lazy) programming because it automaticall
refers to the ActiveSheet which may not be the one you want (o
requires code to make it active), or will crash if the ActiveSheet doe
not contain the name.

You might get away with this in small amounts of code, but once yo
start using more than one workbook/worksheet it is really necessary t
fully qualify your name accordingly :-

y
Workbooks("Book1.xls").Worksheets("Sheet1").Range( "Screen_Number").Value

This saves all sorts of seemingly "strange" results as the applicatio
grows in size. Interestingly, because of this qualification, VB enable
us to define macro names the same as worksheet etc. names so we can us
:-
'-----------------------------------
Sub test()
Dim Screen_Number As Range
Set Screen_Number = Worksheets("Sheet1").Range("Screen_Number")
MsgBox (Screen_Number.Value) ' no quotes round name
End Sub
'----------------------------------

Hope this helps

--
Message posted from http://www.ExcelForum.com