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 returns the number of characters in a string. The following will return the value of 3:
This example will return the value 8:
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 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 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:
The following will also give the value of 1 despite being so close to 2:
Sqr returns the square root of a number. This example will result in the value 2:
The following will result in the value 1.732:
The following will give the value 10:
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:
The following will give the value of 105:
Note that this only works on the first character of the string:
This will give 114, as this is the ASCII code for "r".
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":
The following will give the string "i":
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"