Syntax Question (Any)

Topic

New Reply

In my recent forays into VBA, I was reminded of a question I’ve had for a long time and have never found an answer to.

What is the dollar sign ($) used for and what difference does it make? For example both Mid and $Mid exist in the help files, but they both lead you to the same explanation of the Mid function. Is there a time when one should be used over another?

Peter

Reply | Quote

Replies

The functions whose names end in $ always return a string, while their counterparts without the $ return a variant (that is a string in most situations).

The versions with $ will cause an error on Null values, the versions without can handle Nulls. Example:

Dim varName As Variant
varName = “Peter”

Mid(varName, 3, 2) and Mid$(varName, 2, 2) both return “te”

but after

varName = Null

Mid(varName, 3, 2) returns Null and Mid$(varName, 3, 2) causes an error message “Invalid use of Null”.

If you know in advance that you’re working with valid strings only (they may be equal to the empty string “”), the versions with $ are more efficient, because they have to do less type checking. If Nulls can occur, you’re better of with the versions without $.

Reply | Quote

The functions whose names end in $ always return a string, while their counterparts without the $ return a variant (that is a string in most situations).

The versions with $ will cause an error on Null values, the versions without can handle Nulls. Example:

Dim varName As Variant
varName = “Peter”

Mid(varName, 3, 2) and Mid$(varName, 2, 2) both return “te”

but after

varName = Null

Mid(varName, 3, 2) returns Null and Mid$(varName, 3, 2) causes an error message “Invalid use of Null”.

If you know in advance that you’re working with valid strings only (they may be equal to the empty string “”), the versions with $ are more efficient, because they have to do less type checking. If Nulls can occur, you’re better of with the versions without $.

Reply | Quote