Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named array value in a string variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named array value in a string variable
MsgBox ActiveSheet.Evaluate(ActiveWorkbook.Names("test"). RefersTo)(2)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "cubbybear3" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named array value in a string variable
Thank you Bob. That was just what I needed!!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named array value in a string variable
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? "Bob Phillips" wrote: MsgBox ActiveSheet.Evaluate(ActiveWorkbook.Names("test"). RefersTo)(2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "cubbybear3" wrote in message oups.com... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying To Append String Variable To Another String Variable | Excel Programming | |||
how to parse string into array or variable? | Excel Programming | |||
Passing string as array variable (Pivot VBA) | Excel Discussion (Misc queries) | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
VBA - Convert my variable range array to single cell string | Excel Programming |