Functions
This section is intended to give an overview of the most commonly used functions in VBA. Many others are available, but you will find that these are the major ones used.
Len
Len returns the number of characters in a string. The following will return the value of 3:
MsgBox Len("abc")
This example will return the value 8:
Msgbox Len("cane")
This function is useful in conjunction with the other string-handling functions. For example, if you want the last four characters of a string that is variable in length, you would need to know the string's length.
Abs
Abs stands for absolute value and returns a value of the unsigned magnitude. The following examples both will give the value of 1:
MsgBox Abs(1) MsgBox Abs(-1)
Int
Int is short for integer and truncates a number to an integer. It does not round to the nearest whole number. This will give the value of 1:
MsgBox Int(1.2)
The following will also give the value of 1 despite being so close to 2:
MsgBox Int(1.99)
Sqr
Sqr returns the square root of a number. This example will result in the value 2:
MsgBox Sqr(4)
The following will result in the value 1.732:
MsgBox Sqr(3)
The following will give the value 10:
MsgBox Sqr(100)
Asc
Asc gives the ASCII (American Standard Code for Information Interchange) code for a given character. Values are from 0 to 255. The following will give the value of 65:
MsgBox Asc("A")
The following will give the value of 105:
MsgBox Asc("i")
Note that this only works on the first character of the string:
Asc("martin")
This will give 114, as this is the ASCII code for "r".
Chr
Chr is the reverse of Asc; it takes an ASCII code number and converts it to a character. This example will give the string "A":
MsgBox Chr(65)
The following will give the string "i":
MsgBox Chr(105)
Because this deals with the entire character set, it also includes nonprintable characters. For example, ASCII code 13 is a carriage return, which can be useful if you want to force a carriage return on something like a message box:
MsgBox "This is " & Chr(13) & "a carriage return"
