Functions $

Some of the VBA string functions have two forms.
One that returns a String data type and one that returns a Variant/String data type.
The functions that return a Variant/String data type do not include a dollar sign.
The functions that return a String data type include a dollar sign ($).


Which Functions are Faster ?

There are rumours that the Dollar Functions are faster but this is really not worth worrying about.
Unless you are iterating through hundreds of thousands of times, there is no difference.
We ran some tests iterating 300,000 times and the results can be seen at the bottom of this page.
Data Types > String-Variant (scroll to the bottom).


Passing Null to these Functions

The functions that return a String data type will generate an an error if you call them with a value that is Null.
The functions that return a Variant/String data type can handle Null values without an error.


Functions that return a String

The following table lists all the functions that return a string data type.

FunctionDescription
CHR$Returns the character with the corresponding ANSI number (String).
CHRB$Returns the character with the corresponding ANSI number (String).
CHRW$Returns the character with the corresponding ANSI number (String).
COMMAND$Returns the argument portion of the command line used to launch the application (Variant).
DIR$Returns the name of a file or directory matching a pattern or attribute (String).
ENVIRON$Returns information about the current operating system environment (String).
ERROR$Returns the error message corresponding to a given error number (String).
FORMAT$Returns the text string of a number or date in a particular format (String).
HEX$Returns the number converted to hexadecimal (String).
INSTR$Returns the position of a substring within a larger string (Long).
LCASE$Returns the text string with all characters converted to lowercase (String).
LEFT$Returns a number of characters from the left of a string (String).
LEFTB$Returns a number of characters from the left of a string (String).
LTRIM$Returns the text string without leading spaces (String).
MID$Returns the text string which is a substring of a larger string (String).
MIDB$Returns the text string which is a substring of a larger string (String).
OCT$Returns the number converted to octal (Variant).
REPLACE$Returns the text string with a number of characters replaced (String).
RIGHT$Returns the number of characters from the right of a text string (String).
RIGHTB$Returns the number of characters from the right of a text string (String).
RTRIM$Returns the text string without trailing spaces (String).
SPACE$Returns the specified number of spaces (String).
STR$Returns the text string of a number (String).
STRCOMP$Returns the result of a string comparison (Integer).
STRING$Returns a repeating character of a given length (String).
TRIM$Returns the text string removing leading and trailing spaces (String).
UCASE$Returns the text string with all the characters converted to uppercase (String).

Exceptions - Date

The Date function should always be used because the Date$ function doesn't behave correctly.
The Date$ always returns information in mm-dd-yyyy format regardless of the Windows localization settings, whereas the Date function uses the localization settings.


Exceptions - Replace

The Replace function always creates a copy of the input string, even if no replacement occurs.
Making a copy of the input string is a slow operation and should be avoided if possible.
If a replacement is unlikely, check first (with InStr or InStrB) and only use the Replace function when absolutely necessary.
If a replacement is likely or certain to occur then just use the Replace function (no need to check first).

Dim sText As String 
Dim sToBeReplaced As String
If InStr(sText, sToBeReplaced) <> 0 Then
  sText = Replace(sText, sToBeReplaced, "abc")
End If

Because this function always makes a copy of the input string there is no difference between Replace and Replace$.


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext