CODEWITHSUNDEEP
vba
Ghanshyam Dabhi
Ghanshyam Dabhi

Reputation: 5

What is the difference between VBA.Str and VBA.Str$?

What is the difference between these two code lines

Debug.Print VBA.Str(123)

and

Debug.Print VBA.Str$(123)

Upvotes: 0

Views: 1325

Answers (2)

ThunderFrame
ThunderFrame

Reputation: 9461

Str$ and Str are identical except for at least 2 important differences:

  1. If the usage of Str$ returns anything other than a String, you'll get an error:

    Debug.Print Str(Null)   'Prints Null
Debug.Print Str$(Null)  'Throws Runtime Error 94 - Invalid use of Null

  2. Str$ returns a String, whereas Str returns a Variant. In general, you should always prefer the strongly typed Str$ over Str

There are many other functions that use $-suffixes, like Left$, Mid$ and Space$.

If we look at Left$ (which is really just an alias for _stdcall _B_str_Left) and Left (which is really just an alias for _stdcall _B_var_Left), in the Type Library MIDL, we see that the input types matter too.

[entry(616), helpcontext(0x000f6ea1)]
BSTR _stdcall _B_str_Left(
                [in] BSTR String, 
                [in] long Length);
[entry(617), helpcontext(0x000f653e)]
VARIANT _stdcall _B_var_Left(
                [in] VARIANT* String, 
                [in] long Length);

You can actually use the underlying functions in code:

Debug.Print VBA.Strings.[_B_var_Left]("abc",1) 'Prints a
Debug.Print VBA.Strings.[_B_str_Left]("abc",1) 'Prints a

And to make matters more confusing, a function like Join (that does return a String, but which doesn't have a corresponding Join$ function, can actually be used with a $ suffix:

Debug.Print Join$(Array(1, 2, 3), ".") 'Prints 1.2.3

For further discussion, see my answer at Exactly what are these _B_var_Xxxxx and _B_str_Xxxxx members in the VBA COM libraries?

Upvotes: 2

Mukul Varshney
Mukul Varshney

Reputation: 3141

Str$ is identical to Str except for the declared type of its return value. i.e. Str returns a Variant, the Str$ returns a String.

Refer the msdn link

Upvotes: 0

Related Questions