VBA functions: Left versus Left$

[DanR]

I'm new to VBA and am unable to find any documentation that explains when to
use "Left(string, n)" and when to use "Left$(string, n)" to select the
left-most "n" characters from a string.

I have used the compiler complaints as an indication that I need to insert
the "$", but have not been able to find any documentation that explains
when/why the "$" is needed.

 

[Karl E. Peterson]

I'm new to VBA and am unable to find any documentation that explains when to
use "Left(string, n)" and when to use "Left$(string, n)" to select the
left-most "n" characters from a string.

I have used the compiler complaints as an indication that I need to insert
the "$", but have not been able to find any documentation that explains
when/why the "$" is needed.

It's rarely, if ever, *needed*. The difference is, Left$() returns a String, and
Left() returns a Variant. That's about it. Same with all the similar "string"
functions - Mid, Right, etc. In general, optimal performance is attained with the $
variation, the exception being when the assignment is to a Variant anyway.

 

[DanR]

Karl,

Thanks. I couldn't find "Variant" listed as a data type in MS VB Help, but
I notice it in my "Beginning Access 2000 VBA" book by R. Smith and D.
Sussman. It rather reminds me of variables in Perl, and how they don't have
the strict "typing" such as found in FORTRAN or C.

I will have to study my compiler complaint, and see if maybe something else
was causing the problem (although adding the "$" to Left made the complaint
go away). It was in an If statement:

Dim strConnect As String

If (Left(strConnect, 5) = "ODBC;") Then
....
End If

I don't see why the "Variant" form of "Left" should not work in this
example. But maybe when I was getting the complaint I had
"Left(tblName.Connect,5)" -- so maybe without the "$" it was returning some
sort of object, that could not be compared with "ODBC;" via the "=" operator.
Perhaps because "tblName.Connect" can be NULL?

 

[Bruce Rusk]

Sometimes the installation of VBA gets messed up and certain string
functions are not parsed properly. I have no idea whether this could be
a symptom of that, but it could well be. A detect-and-repair of Office
will usually fix it.

 

[Karl E. Peterson]

Karl,

Thanks. I couldn't find "Variant" listed as a data type in MS VB Help, but
I notice it in my "Beginning Access 2000 VBA" book by R. Smith and D.
Sussman. It rather reminds me of variables in Perl, and how they don't have
the strict "typing" such as found in FORTRAN or C.

That's the idea, yep. Typeless. Almost always, the worst choice. Except (those
*very* few cases) when it's the best. Heh...

I will have to study my compiler complaint, and see if maybe something else
was causing the problem (although adding the "$" to Left made the complaint
go away). It was in an If statement:

Dim strConnect As String

If (Left(strConnect, 5) = "ODBC;") Then
...
End If

I don't see why the "Variant" form of "Left" should not work in this
example. But maybe when I was getting the complaint I had
"Left(tblName.Connect,5)" -- so maybe without the "$" it was returning some
sort of object, that could not be compared with "ODBC;" via the "=" operator.
Perhaps because "tblName.Connect" can be NULL?

That could be it, actually. What if you tried:

Left(tblName.Connect & "", 5)

If that gets you around the "complaint", you'd have your answer. Then again, you
may have already had a better explanation if you'd posted the actual error message,
rather than characterizing it as a pouty compiler. ;-)

Later... Karl