• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

How to determine data type of variable

I want to create a flexible function that returns a result based on a formula by Application.Evaluate(Name)  The problem is around handling both the following two situations:

Application.Evaluate("Sheet1!A:A") -- returns an object
Application.Evaluate("LEFT(""test"",2)") -- doesn't

I have no good way of predicting beforehand if the result will be an object or not and I'd prefer not to use a On Error Resume... type solution if there is a better way - which may just mean a better understanding of how the varType function works.

varType(Application.Evaluate("Sheet1!A:A")) returns "8204" but this was not defined as a range in vba help (http://msdn.microsoft.com/en-us/library/aa263402(v=vs.60).aspx)

How do I make this robust?

Many thanks.



vCommandFinal is a string/variant
getEvaluateFormula is a variant
rowContext is an array that returns an integer based on the specified worksheet.index property
Select Case VarType(Application.Evaluate(vCommandFinal)) 
      Case vbObject
        Set vResult = Application.Evaluate(vCommandFinal)
        getEvaluateFormula = vResult(rowContext(vResult.Worksheet.Index))
      Case vbError
        Stop
        vResult = "Unknown or Invalid"
      Case Else
        Set vResult = Application.Evaluate(vCommandFinal)
        Debug.Print VarType(vResult)
        getEvaluateFormula = vResult
      End Select

Open in new window

0
_Benaiah
Asked:
_Benaiah
  • 3
  • 2
1 Solution
 
dlmilleCommented:
is there a reason you don't just use a variant variable to catch anything coming off the Evaluate command?  Unless you're parsing unknowns, the program you're writing should expect the datatype its looking for.  I'm not sure I follow why you'd need to do this.

Can you elaborate more?

Dave
0
 
Rory ArchibaldCommented:
If you check the bottom of the article you cited, you will be able to work out that 8204 is an array of variants. (8192 for array + 12 for variant). If you used TypeName, you would get "Range".

Regards,
Rory
0
 
_BenaiahAuthor Commented:
TypeName looks like exactly what I wanted
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
_BenaiahAuthor Commented:
btw whats the difference?
0
 
_BenaiahAuthor Commented:
...meaning when is it best to use varType over TypeName?
0
 
Rory ArchibaldCommented:
In theory, the main difference is that one returns a number code and the other a type string.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now