We help IT Professionals succeed at work.

What datatype holds this {1,2;3,4} ? (It's not a string far as I can tell.)

Anthony Mellor
on
Medium Priority
408 Views
Last Modified: 2012-06-27
What datatype holds this {1,2;3,4}  Must it be variant?

The context is Evaluate(A1) where A1 contains this string {1,2;3,4}, which when evaluated is an array constant (I think).

I have experimented with various types, but they all get errors except Variant.

Anthony
Aspiring Novitiate, polishing my Datatypes
Comment
Watch Question

CERTIFIED EXPERT

Commented:
I think you can only use a string for that purpose.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

it's a two-dimensional array with two rows and two columns. The commas starts a new row, the semicolon starts a new column.

cheers, teylyn
Top Expert 2011
Commented:
As teylyn says, it's an array.  That is why only a variant works here.  You can test it with the following code:
Sub TT()
    Dim v As Variant
    v = Evaluate(ActiveSheet.Cells(1).Value)
    Stop
End Sub

Open in new window

Then, when it stops, you can see your variable in the Locals window:screenshotIf you're using Evaluate like this without knowing what is is in the cell you must assign to a Variant, then you can check what has been returned using Typename or a similar function.
Anthony MellorChartered Accountant
CERTIFIED EXPERT

Author

Commented:
ok, if it's anything but an array (as described) I don't care, so it can generate an error to its heart's content; this would as I understand it fit the bill for one of the hard data types (i.e. not the "soft" Variant), except there doesn't seem to be one that accepts arrays. in fact I can't find where it says that Variant accepts arrays, though empirically and clearly, it does.

Hence my question really, can't find it in the docs.

Anthony
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
You can declare an array with a specific data type, just like any other variable. If you omit the data type, it will be a variant.

Can you provide a bit more context on what you are trying to accomplish? In what scenario do you use evaluate? You mention Evaluate(A1), so there really is no VBA or declaration of data types involved that I can see.

Have a look at Chip Pearson's site for much info on arrays in VBA http://www.cpearson.com/excel/VBAArrays.htm

cheers, teylyn
Most Valuable Expert 2012
Top Expert 2012

Commented:
It depends on what variable you assign it to, or how your function works.  I've been down the Evaluate path with you these past days- heck I started it I think, but that's not the only way you can interpret {1,2,3,4}.  You could build your own evaluate function (like I did for you earlier as an example assuming Evaluate didn't exist) reading it as a string.

Agree with what everyone else has said, with the exception that your code not using the Evaluate function can also evaluate it as a string.

Function getString(myStr as string) as string

   getString = left(myStr,3)

End Function

Of course, Evaluate sees it as an array, unless you improperly form it and it also sees it as a string, lol.

Use this against that {1,2;3,4} and you would get:

"{1,"

Cheers,

Dave
Anthony MellorChartered Accountant
CERTIFIED EXPERT

Author

Commented:
If I seem confused, or unresponsive, see what you make of these:


by: teylynPosted on 2012-03-10 at 00:25:00ID: 37703671
You can declare an array with a specific data type, just like any other variable.

by: andrewssd3Posted on 2012-03-09 at 23:07:51ID: 37703434
As teylyn says, it's an array.  That is why only a variant works here.

by: ssaqibhPosted on 2012-03-09 at 21:39:06ID: 37703090
I think you can only use a string for that purpose.

by: dlmillePosted on 2012-03-10 at 02:14:13ID: 37703895
Agree with what everyone else has said, with the exception that your code not using the Evaluate function can also evaluate it as a string.

;-)

Yours sincerely,

Perplexed
Anthony MellorChartered Accountant
CERTIFIED EXPERT

Author

Commented:
see above :-)
Most Valuable Expert 2012
Top Expert 2012
Commented:
I was being cute.  Evaluate sees it as a variant array.

Dave

Explore More ContentExplore courses, solutions, and other research materials related to this topic.