Solved

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

Posted on 2012-03-09
9
341 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
0
Comment
Question by:anthonymellorfca
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37703090
I think you can only use a string for that purpose.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37703102
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
0
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 250 total points
ID: 37703434
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.
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37703645
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37703671
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37703895
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
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37704436
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
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37712178
see above :-)
0
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
ID: 37712191
I was being cute.  Evaluate sees it as a variant array.

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 2016 Excel Issue 4 27
ActiveX Listbox Multi Select in Excel 2010 8 20
Excel printing page management 2 25
Vlookup formula error 15 13
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now