Convert Vartype number to string

Hello Dear Excel Forum

Is there a default way to get string from Vartype number?
For example:
Debug.Print vbString ' prints 8

I would like to convert number 8 to "vbString" or "String"

Do you mean something like that?

Please Login or Register  to view this content.

To clarify what I mean:

When I test function with variable input for type I use:
If Vartype(var)=vbString Then do something
There is intelisense when I type vbS -> vbString
vbString stores long number 8. So there must be a public type or enum which stores combination of type name and coresponding number

vbInteger=2
vbLong=3
vbString = 8

I don't want to rember the numbers so I would like to have a function which converts number to its corresponding typeName.
So when I have for example in variable var stored number 2 or 8:
var=2 ->"vbInteger" or "Integer"
var=8 ->"vbString" or "String"

For example lets have a function conv:

Please Login or Register  to view this content.

But I don't want to have unnecessary code in my project. Especially large Case or Ifs structures. So I'm asking if there is native way to do it.

I don't get what you're trying to do, or how it could usefully compact your code.

VbString is indeed 8, but so is

xlCircle
xlClipboardFormatBIFF
xlColor2
xlDBF3
xlDialogPrint
xlEdgeTop
xlEPS
xlFillYears
xlIMEModeAlpha
xlLessEqual
xlLinkStatusSourceNotOpen
xlListDataTypeListLookup
xlListDataValidation
xlLowerCaseRowLetter
xlMarkerStyleCircle
xlPaperA3
xlParamTypeDouble
xlPasteColumnWidths
xlPercentOfTotal
xlPivotCellDataPivotField
xlRangeAutoFormatColor2
xlReport9
xlScrollBar
xlSmartTagControlImage
xlTableBody
xlTitleBar
xlTotalsCalculationVar
xlTrendline
xlVALU
xlYDMFormat

If you go to the ObjectBrowser and type "vbString" in the search field, it will take you to the vbVarType class which lists all of the types that are returned by VarType function.

easiest is to use a function to convert it

Please Login or Register  to view this content.

Originally Posted by jakopak

Pike, I know. That is something I would use as last resort. I think its unfeasible to write such functions for each group of xlVariables or vbVariables.

That is one of the uses of the Object Browser, to find the classes of VBA constants.

if you are using Vartype that one function is all you will need
https://msdn.microsoft.com/en-us/lib...ffice.15).aspx

Is there a problem with kasan's suggestion of using Typename instead of Vartype. After all, it returns the name of the variable type.

Good thought Typename is for Office 2013 and later, jakopak MS-Off Ver:2010

TypeName goes back to at least Excel 2003 (the earliest version I have installed to check).

My Excel 2005 had TypeName.

Dills have it wrong
https://msdn.microsoft.com/en-us/lib...ffice.15).aspx

If you click other versions at the top of that page and then 2010, you'll see it applied there too. That's just a limitation of how far back MS maintains its documentation, I think.

As mentioned, typename has been part of VB since at least VB3 and has been part of Office since version 5/95.

I know function typeName and VarType and use it heavily. Problem with Kasan solution is that if try
Debug.Print TypeName(CStr(vbDouble)) I get "String" but I would like to get "vbDouble".
Function which Pike provided in #8 will essentially do what I want but I suspect,
there is a native way to do it. Because why not?

Maybe the xlValues are strored in Enum. Values names in enum can't be accessed too by number or can they?

SHG you have provided list of values which has value 8 in answer #5. I believe you have constructed it programmatically. Am I right?
If so, can you please provide the method?

xlCircle
xlClipboardFormatBIFF
xlColor2
xlDBF3

I did not make post #5.

Why do you wish to use Vartype on a variable and then somehow convert that result to the name of the type, instead of simply using TypeName on the variable?

If you wish to access Enum values from a library, you need an additional library like tlbinf which is not a standard library so your code is unlikely to work on any other machine. Using a Select Case approach is simpler, though I still cannot imagine the need. Most often, when questions like this are asked, it seems that the asker is fighting to try to make VBA behave like some other language rather than working with VBA.

I am sorry for confusion answer #5 provided SHG.

My motivation for example: I have defined an array of types.
ara1(0)=vbDecimal
ara1(1)=vbInteger
ara1(2)=vbLong
ara1(3)=vbDouble

Now I do some operation on this array and put result in another array ara2
Debug.Print ara2(0) -> 8
Debug.Print ara2(1) -> 7
Debug.Print ara2(2) -> 8
Debug.Print ara2(3) -> 1

Those numbers in debug message tells me nothing although I already unintentionally remember them. But I don't want to remember it for all groups of xl and vb values.

Originally Posted by xlnitwit

Most often, when questions like this are asked, it seems that the asker is fighting to try to make VBA behave like some other language rather than working with VBA.

That may be my problem. I come from R background. I need this functionality for exploring VBA and debugging higher order functions (functors) and metaprogramming.

There is no native method to do that. You will have to write your own function one way or another, or adopt a different approach to whatever you are doing with the array so that you can store actual type names rather than enum values.