Lesson 6: Mathematical Functions

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.  

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 e^x.The syntax is:

  Exp(Number)

Example 6.2

Private Sub CommandButton1_Click()

  Cells(1,1)=Exp(1)

End Sub

Running the program will display 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 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)

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 display 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 displays 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.