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
331 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:teylyn
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Expert Comment

by:teylyn
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

9 Experts available now in Live!

Get 1:1 Help Now