In Excel VBA , we can write codes that can perform arithmetic operations using standard arithmetic operators. However, for more complex mathematical calculations, we need to use the built-in mathematical functions in Excel VBA. There are numerous built-in mathematical functions in Excel VBA. Among them are Abs, Exp, Int, Fix, Rnd, Round, sqr and more. We shall deal with trigonometric functions and Financial Functions in coming lessons.

In Excel VBA, the Abs function returns the absolute value(positive value) of a given number. The syntax is

Abs(Number)

Private Sub CommandButton1_Click()

Cells(1,1)=Abs(-100)

End Sub

Running the program will display 100 in cell A1

The Exp of a number x is the value of e^{x}.The syntax is:

Exp(Number)

Private Sub CommandButton1_Click()

Cells(1,1)=Exp(1)

End Sub

Running the program will display 2.718282 in cell A1

Int is the function that converts a number into an integer by truncating its decimal part and the resulting integer is the largest integer that is smaller than the number.

The syntax is

Int(Number)

Private Sub CommandButton1_Click()

Cells(1,1)=Int(2.4)

Cells(2,1)=Int(4.8)

Cells(3,1)=Int(-4.6)

Cells(4,1)=Int(0.32)

End Sub

Running the program will display the results as 2 in cell A1,4 in cell A2,-5 in cell A3 and 0 in cell A4.

Fix and Int are the same if the number is a positive number as both truncate the decimal part of the number and return an integer. However, when the number is negative, it will return the smallest integer that is larger than the number. The syntx is:

Fix(number)

Private Sub CommandButton1_Click()

Cells(1,1)=Fix(2.4)

Cells(2,1)=Fix(4.8)

Cells(3,1)=Fix(-4.6)

Cells(4,1)=Fix(-6.32)

End Sub

Running the program will display the results as 2 in cell A1,4 in cell A2,-4 in cell A3 and -6 in cell A4.

The Rnd function returns a random value between 0 and 1.Rnd is very useful when we deal with the concept of chance and probability. The syntax is:

Rnd

Private Sub CommandButton1_Click()

Dim x As Integer

For x = 1 To 10

nbsp;Cells(x, 1) = Rnd()

Next x

End Sub

Running the program will displays ten random numbers between 0 and 1 from cell A1 to cell A10.

Round is the function that rounds up a number to a certain number of decimal places. The Format is Round (n, m) which means to round a number n to m decimal places. For example, Round (7.2567, 2) =7.26

Sqr is the function that computes the square root of a number. For example, Sqr(4)=2, Sqr(9)=2 and etc.

*Copyright ® 2008** *Dr.Liew Voon Kiong* . *All rights reserved
[Privacy Policy]

*Contact: *Facebook Page