_Benaiah
asked on
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("Shee t1!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.Evalua te("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
Application.Evaluate("Shee
Application.Evaluate("LEFT
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.Evalua
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
TypeName looks like exactly what I wanted
ASKER
btw whats the difference?
ASKER
...meaning when is it best to use varType over TypeName?
In theory, the main difference is that one returns a number code and the other a type string.
Can you elaborate more?
Dave