User FAQs

1) What is the best way to check for an empty string ?
Always check the length of the string using the LEN function.
Avoid comparing with an empty string ("") as this requires 6 bytes of memory.

If VBA.Len(myString) = 0 Then 

2) What is the best way to assign an empty string ?
Always assign your string to vbNullString when you need to empty the contents.
Avoid using an empty string ("") as this is slower and takes up more memory.
Zero-Length String ("") - Is an actual string literal and has a size of 6 bytes.
vbNullString - Is a special character that denotes an empty string which is equivalent to zero.

Dim myString As String 
myString = VBA.Constants.vbNullString 

3) How would you declare a Variable Length String ?

Dim myString As String 

4) How would you declare a Fixed Length String with 10 characters ?
A fixed length string is padded with null characters CHR(10) when it is declared.
The CHR returns the character with the corresponding ASCII / ANSI number.

Dim myString As String * 10 

5) How would you check the last 2 characters in a string ?

Dim myString As String 
myString = "someText" 
If (Mid(myString, Len(myString) - 1) = "xt") Then 
End If 

6) How would you check for specific characters in a string ?
The Like Operator can be used to compare two strings and provide pattern matching.

If (sFirstName Like "a?b*" = True) Then 
End If 

7) What is the difference between the MID function and the MID statement ?
The MID Function returns a substring from a larger string.

Dim sText As String 
sText = Mid("hello world", 1, 3) 
Debug.Print sText                     '= "hel"   

The MID Statement replaces some characters inside a string variable.

Dim sText As String 
sText = "hello world" 
Mid(sText, 1, 3) = "XXX" 
Debug.Print sText                     '= "XXXlo world"  

8) What is the difference between the LEFT function and the LEFT$ function ?
LEFT - This function can handle Null being passed in as an argument.
LEFT$ - This function cannot handle Null.
For more information refer to the Functions $ page.

9) What is the difference between the vbNewLine constant and the vbCrLf constant ?
There is no difference, they both represent a carriage return and line feed.

VBA.Constants.vbNewLine    '= CHR(13) + CHR(10)  
VBA.Constants.vbCrLf       '= CHR(13) + CHR(10)  

10) Can you have this ?
Yes this is valid and defines a subroutine with an optional argument that has a default value of an empty string.

Public Sub SubName(Optional ByVal sValue As String = vbNullString) 
End Sub