Excel VBA Lesson 6: Mathematical Functions

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit

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

<<Lesson 5>> [Contents] <<Lesson 7>>

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit