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 (

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
        vResult = "Unknown or Invalid"
      Case Else
        Set vResult = Application.Evaluate(vCommandFinal)
        Debug.Print VarType(vResult)
        getEvaluateFormula = vResult
      End Select

Open in new window

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
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".

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?

_BenaiahAuthor Commented:
TypeName looks like exactly what I wanted
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.