Referring to Microsoft Excel array in VBA
 

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

Referring to Microsoft Excel array in VBA
 

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)

"Alan Beban" wrote in message
...
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

Referring to Microsoft Excel array in VBA
 

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

On 4/21/04 15:58, in article , "Alan
Beban" wrote:

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

Referring to Microsoft Excel array in VBA
 

Thanks, Bob. That works.

Alan Beban

Bob Phillips wrote:

Alan,

How about

Evaluate(Names("myName").RefersTo)

Is this what you want?

Referring to Microsoft Excel array in VBA
 

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

TH wrote:

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

On 4/21/04 15:58, in article , "Alan
Beban" wrote:


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

Referring to Microsoft Excel array in VBA
 

You can also use the simpler:

[myName]

or Evaluate("myName")


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Bob Phillips" wrote in message
...
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)

"Alan Beban" wrote in message
...
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