Referring to Microsoft Excel array in VBA

A

Alan Beban

I have a defined name, "MyArray". It refers to the array 4;3;4;3;2;5.
How do I refer to MyArray in VBA code?

TIA,
Alan Beban
 
B

Bob Phillips

Alan,

How about

Evaluate(Names("myName").RefersTo)

Is this what you want?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

TH

I don't follow your 4;3;4;3;2;5, but if you have defined a Name on the
worksheet it is referred simply as:

Range("MyArray")

assuming there are not duplicate names on multiple sheets.
If there are it would be:

Worksheets("MySheet").Range("MyArray")

Now that gets the entire array. If you want each cell separately try:

Set MyList = Range("MyArray")
For each C in MyList
if C.value = "???" Then
'do something
end if
Next

The above cycles through each cell in the array so you can do compares,
assigns, whatever.

TH
 
A

Alan Beban

Thanks for responding; but the response assumes that the name "MyArray"
refers to some range. It doesn't--it refers to the 6-row by 1-column
array with elements 4,3,4,3,2,5, respectively. I.e., click on
Insert|Names|Define; in the Names in Workbook box type MyArray; in the
Refers to box type ={4;3;4;3;2;5}; OK. Now on a worksheet array enter
into a 6-row column =MyArray.

Bob Phillips provided an answer.

Thanks again,
Alan Beban
 
Top