Strings & Characters

A string is a sequence of characters.
There are two types of string data types: fixed length and variable length
A string may contain ordinary characters (letters, digits and punctuation) as well as special control characters (such as vbCrLf or vbTab).
A string constant is enclosed with quotation marks.
An empty string contains no characters.


Testing for empty string - Replace empty strings ("") with Len(variable) = "" or Len(variable) <> ""
Assigning an empty string - Replace empty strings with vbNullString
New Line - Use vbNewline instead of vbCrLf
String Constants - Always define them once as constants

Variable length strings

These are the most common although fixed length strings are usedful when calling API calls.
Variable length strings that can hold up to 2 billion characters.

Dim sVariableLength As String 

Fixed length strings

These are always padded with spaces to ensure they are the correct length.
Fixed length with a maximum of 65,535 characters.

Dim sFixedLength As String * 10 

VBA stores the length of a string as a long integer at the beginning of the string.
The Len() function simply retrieves this value, so it is faster than comparing the string to a zero-length string ("").
Always good practice to use the Len() function to check if a string is empty as opposed to checking if it equals an empty string (i.e. if sString = "").
Instead of using the double quotation marks in your strings you could use Chr(34) which returns an actual quotation mark.

If you ever need to check a character's ANSI value it is much faster to use the ASC() function and compare numerical values instead of performing a string comparison.

Dim sEmptyString As String 
sEmptyString = ""


In order to declare a fixed length string use: String*10 (where 10 is the number of characters)
Whenever you want to include speech marks in a string you must use four speech marks (eg "File exists: " & """" & sFileName & """")

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopNext