Named array value in a string variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cubbybear3 - 22 Oct 2007 08:55 GMT

As an example, I have a named array "aryXYZ" defined as:
={"abc","456","@#$"} (and yes, I did use Ctrl+Shift+Enter)

I can get the second (or for that matter, any) element/entry into a a
cell with: =INDEX(aryXYZ,2)

What I would like to do is get the value into a string value in a
macro without loading a worksheet cell first. Any hints/clues would
be nice. Thank you. -pb

Reply to this Message

Bob Phillips - 22 Oct 2007 09:57 GMT

MsgBox ActiveSheet.Evaluate(ActiveWorkbook.Names("test").RefersTo)(2)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> As an example, I have a named array "aryXYZ" defined as:
> ={"abc","456","@#$"} (and yes, I did use Ctrl+Shift+Enter)
[quoted text clipped - 5 lines]
> macro without loading a worksheet cell first. Any hints/clues would
> be nice. Thank you. -pb

Reply to this Message

cubbybear3 - 22 Oct 2007 19:33 GMT

Thank you Bob. That was just what I needed!!

Reply to this Message

David SW Fan - 14 Nov 2007 16:22 GMT

Bob, I've tried your idea (for a different purpose) on a defined range that
results in an array and whose RefersTo is in the form of something like:

=IF(datarange=1,"n/a",datarange)

I am then able to use the worksheet INDEX function to reference any item in
the array. However, while I can use VB UBound(Evaluate...RefersTo) to
determine how many items are in the array, I get a "subscript out of range"
error when using Evaluate(...RefersTo)(2) or any index number that plainly
falls between the bounds.

Any ideas?

> MsgBox ActiveSheet.Evaluate(ActiveWorkbook.Names("test").RefersTo)(2)
>
[quoted text clipped - 7 lines]
> > macro without loading a worksheet cell first. Any hints/clues would
> > be nice. Thank you. -pb

Reply to this Message