<<Lesson 5>> [Contents] <<Lesson 7>>
In Excel VBA macro programming, 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.
6.1 The Abs Function
In Excel VBA, the Abs function returns the absolute value(positive value) of a given number.
The syntax is
Abs(Number)
Example 6.1
Private Sub CommandButton1_Click()
Cells(1,1)=Abs(-100)
End Sub
Running the program will display 100 in cell A1
6.2 The Exp Function
The Exp of a number x is the value of ex.The syntax is:
Exp(Number)
Example 6.2
Private Sub CommandButton1_Click()
Cells(1,1)=Exp(1)
End Sub
Running the program will displays 2.718282 in cell A1
6.3 The Int Function
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)
Example 6.3
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.
6.4 The Fix Function
Fix and Int is 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 syntax is:
Fix(number)
Example 6.4
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 displays the results as 2 in cell A1,4 in cell A2,-4 in cell A3 and -6 in cell A4.
6.5 The Rnd Function
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
Example 6.5
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 display ten random numbers between 0 and 1 from cell A1 to cell A10.
6.6 The Round Function
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
6.6 The Sqr Function
Sqr is the function that computes the square root of a number. For example, Sqr(4)=2, Sqr(9)=2 and etc.
