To know the data type of the variable:
Function Get_Variable_Type(myVar)
' ---------------------------------------------------------------
' Written By Shanmuga Sundara Raman for http://vbadud.blogspot.com
' ---------------------------------------------------------------
If VarType(myVar) = vbNull Then
MsgBox "Null (no valid data) "
ElseIf VarType(myVar) = vbInteger Then
MsgBox "Integer "
ElseIf VarType(myVar) = vbLong Then
MsgBox "Long integer "
ElseIf VarType(myVar) = vbSingle Then
MsgBox "Single-precision floating-point number "
ElseIf VarType(myVar) = vbDouble Then
MsgBox "Double-precision floating-point number "
ElseIf VarType(myVar) = vbCurrency Then
MsgBox "Currency value "
ElseIf VarType(myVar) = vbDate Then
MsgBox "Date value "
ElseIf VarType(myVar) = vbString Then
MsgBox "String "
ElseIf VarType(myVar) = vbObject Then
MsgBox "Object "
ElseIf VarType(myVar) = vbError Then
MsgBox "Error value "
ElseIf VarType(myVar) = vbBoolean Then
MsgBox "Boolean value "
ElseIf VarType(myVar) = vbVariant Then
MsgBox "Variant (used only with arrays of variants) "
ElseIf VarType(myVar) = vbDataObject Then
MsgBox "A data access object "
ElseIf VarType(myVar) = vbDecimal Then
MsgBox "Decimal value "
ElseIf VarType(myVar) = vbByte Then
MsgBox "Byte value "
ElseIf VarType(myVar) = vbUserDefinedType Then
MsgBox "Variants that contain user-defined types "
ElseIf VarType(myVar) = vbArray Then
MsgBox "Array "
Else
MsgBox VarType(myVar)
End If
' Excel VBA, Visual Basic, Get Variable Type, VarType
End Function
Sunday, April 08, 2007
Visual Basic for Applications (VBA) Forum (recent threads)
CodeKeep VBA Feed
Visual Studio Tools for Office Forum (recent threads)
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.
Office Business Applications (OBA) Team Blog
MSDN Code Gallery Published Resources For Tag VSTO
microsoft.public.vsnet.vstools.office Google Group
Posts
That's a very nice pice of code, but if you would like a much simpler wat to acheive much the same thing, then try this:
ReplyDeleteMsgBox TypeName(VARIABLE_GOES_HERE)
And seriously, that's all you need!
Hi,
ReplyDeleteNot one of those keywords are recognised : vartype or typename...
Is it VBA with excel ?
Yes, this is on VBA with excel, and it works fine with mine. (Though I am using Excel 2007, so not sure if it was supported on older versions) It may also be that you have some libraries disabled. But if you check out the TypeName variable in microsoft help, it should point you to which library to enable. Here's a link to the usage if the TypeName function http://office.microsoft.com/en-us/access-help/typename-function-HA001228928.aspx
ReplyDeleteP.S. In case you were looking for the .NET version of the same thing then use TypeOf instead of TypeName. ;)
ReplyDeleteTypeName(Variable) also works with 2010. This is a great function if you are looking to discern a variant input to a user defined function.
ReplyDeleteWe may have different code for handling an Input Range versus a String supplied directly.
Function UDF(Variable as Variant) as String
UDF = TypeName(Variable)
End Function
I'm using MS Office 2003 and Redge's UDF function works great.
ReplyDelete