Format Function In VBA

[Rick Rothstein]

Hi snb,

There is a difference.Check the following code:

Sub Test()
    
    Dim dteToday    As Date
    Dim strToday    As String
    
    dteToday = Format(Now())
    strToday = Format$(Now())
    
    MsgBox dteToday & vbTab & TypeName(dteToday) & vbNewLine & strToday & vbTab & TypeName(strToday)
    
End Sub

I am afraid that does not show what you think it shows. TypeName is reporting the data type the variables were declared as, not the variable type being returned from the Format/Format$ functions. I think you will be able to see this more clearly from this modification to the above code...

Sub Test2()

  Dim dteToday    As Date
  Dim strToday    As String

  dteToday = "1/23/2013"
  strToday = DateSerial(2013, 1, 23)

  MsgBox dteToday & vbTab & TypeName(dteToday) & vbNewLine & strToday & vbTab & TypeName(strToday)

End Sub

I am not sure there is a good way to show that Format returns a Variant with a sub-type of String and that Format$ returns a pure String without invoking some Windows API functions (which I am not really all that fluent with anymore). I would point out to snb, though, that the first link I gave was to the online documentation for Office 2000, so it was referring to VBA, not the compiled version of VB. Of course, snb seems to believe the documentation is in error while I do not. The compiled version of VB and VBA share a common core functionality and those string functions (with and without the $ sign) is part of that core... I find it hard to believe that Microsoft would have tinkered with the core functionality of the language, when there would have been no compelling reason to do so, just to make use of it in VBA. Hence, I believe my original statement in this thread to be accurate, but I cannot find a good way to "prove" it.

[Transformer]

Ohhh .....got it

[Transformer]

When we start typing a function then its description is displayed with it....in case of Format$, it can be seen that it is declared as String and for Format there is no data type means Variant.
As of now, i dont have any example for format function but difference can be seen in the following example:

Left(NULL,3) returns NULL and Left$(NULL,3) throughs a run time error.

Because a Variant can be Null but String can not be NULL.

[Rick Rothstein]

When we start typing a function then its description is displayed with it....in case of Format$, it can be seen that it is declared "as String" and for Format there is no data type means Variant.

YES! PERFECT! You are absolutely correct... VBA's intellisense does make clear the difference in exactly the way you noted. Format$ is clearly identified as returning a String data type whereas Format (no $ sign) shows no data type meaning it returns a default Variant data type, the fact that its sub-type is a String value can be seen from something like this....

MsgBox TypeName(Format(1))

That was very observant of you to notice the intellisense description like that... I must have looked at it thousands upon thousands of times across the years and it just never registered with me. I would also point out that what you observed for Format/Format$ also is displayed for the other String functions that return a text value (Mid/Mid$, String/String$, Chr/Chr$, etc.).

[snb]

I think this only illustrates that the restraints for the argument of format are different from the restrainst of format$ 's argument.
That has no relation to the type of the resulting variable, which this discussion was originally about.



RR_format_002.jpg