Functions

This section is intended to give an overview of the most commonly used functions in VBA. There are many others 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("shepherd") 

This function is useful in conjunction with the string handling commands. 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 rounds a number to the previous 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("richard") 

This will give 114.

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"