Is there a universal way to get a nice textual representation of a variable in VBA?

Question

In VBA, is there a nice way to get a textual representation of a variable / object? Like Java's various String.valueOf(...) methods.

Answer 1

There is nothing built in beyond 'CStr', which has the limitations you've noticed. The only thing you can do is write your own. Luckily, VBA has enough built-in operators ('IsArray', IsNumeric','VarType', etc.) to make it easy. (And that does make you wonder, "why not just make 'CStr' more powerful?"...)

The functions below are an example. They're for Excel/VBA code, so you probably don't care about the implementations of the routines called within them to format arrays, etc., but they should give you the basic idea.

'Diagnostic formatting of variant as string
Public Function fmt(arg) As String
    If IsObject(arg) Then
        fmt = fmtObj_(arg)
    ElseIf IsArray(arg) Then
        fmt = fmtArr_(arg)

    'Note that this test must come after IsArray() since IsMissing() returns
    'True for an empty array parameter (created by calling Array())
    ElseIf IsMissing(arg) Then
        fmt = "<Missing>"
    Else
        Select Case VarType(arg)
            Case vbDouble

                'Since we're in Excel, don't include double type char (#)
                fmt = CStr(arg)
            Case vbString
                fmt = """" & arg & """"
            Case vbEmpty
                fmt = "<Empty>"
            Case vbBoolean, vbDate
                fmt = CStr(arg)
            Case vbError
                fmt = fmtExcelVBAError_(arg)
            Case vbLong
                fmt = CStr(arg) & "&"
            Case vbInteger
                fmt = CStr(arg) & "%"
            Case vbCurrency
                fmt = CStr(arg) & "@"
            Case vbNull
                fmt = "<Null>"
            Case Else
                fmt = "<Typename - " & TypeName(arg) & ">"
        End Select
    End If

    If Len(fmt) > MAX_FMT_LEN_ Then
        fmt = Left$(fmt, MAX_FMT_LEN_) & " <...>"
    End If
End Function


'As fmt(), but "normal" conversion for strings, numbers, and Empty
Public Function toStr(arg) As String
    If IsObject(arg) Then
        toStr = fmt(arg)
    Else
        If VarType(arg) = vbString Or VarType(arg) = vbEmpty Or IsNumeric(arg) Then
            toStr = CStr(arg)
        Else
            toStr = fmt(arg)
        End If
    End If
End Function