Mathematics Functions

In your use of Excel you will be faced with using some mathematical formulas. Various mathematical functions are already available in VBA to make your task easier.

The most VBA being a widely used language for reporting and data analysis, it seems logical that we find several mathematical functions.

Basics mathematical functions in VBA

Below is a list of basics functions, already available in VBA. You can call directly these functions in your macro :

Sub MathFunction()
'Some math function in VBA

Sheets("Math").Activate
Randomize

Cells(2, 2).Value2 = "Exp(0) = " & Exp(0)
Cells(3, 2).Value2 = "Abs(-10) = " & Abs(-10)
Cells(4, 2).Value2 = "Atn(1) = " & Atn(1)
Cells(5, 2).Value2 = "Cos(1) = " & Cos(1)
Cells(6, 2).Value2 = "Fix(2) = " & Fix(2)
Cells(7, 2).Value2 = "Int(10.9) = " & Int(10.9)
Cells(8, 2).Value2 = "Log(0.3) = " & Log(0.3)
Cells(9, 2).Value2 = "Rnd() = " & Rnd()
Cells(10, 2).Value2 = "Sgn(0) = " & Sgn(0)
Cells(11, 2).Value2 = "Sin(0) = " & Sin(0)
Cells(12, 2).Value2 = "Tan(0) = " & Tan(0)
Cells(13, 2).Value2 = "Sqr(4) = " & Sqr(4)

End Sub

Random values in VBA with Rnd

In the area of probability and statistics, it is essential to be able to create and use random variables.

To create a random variable in VBA, use the Rnd function. The latter generates a number between 0 and 1, which can be equal to 0 but strictly less than 1. From this function, we can, therefore, re-create random variables on selected intervals.

Sub RandomNumber()
'Generate a random number between 1 and 50

Number = Int(50 * Rnd) + 1
MsgBox "Random Number = " & Number

End Sub

Random values in a range using Rnd in VBA

To generate several random number in VBA in a range of cells, you can loop on this range and called for each cell the function Rnd.

Sub RangeRnd()
'Write random values in a range

'Declaration
Dim i As Variant
Dim Rng As Range

'Initialisation
Set Rng = Range("A1:A10")

'Loop in the range
For Each i In Rng
i.Value2 = Rnd * 100
Next i

'Free variables
Set Rng = Nothing

End Sub

Simulate a Normal distribution in VBA

In the theory of probabilities, the Normal law also called Gauss law, is a probability law which allows simulating different natural and sociological phenomena. If you want to simulate a normal law in VBA you can use a function already implemented: Norm_Inv. This returns a distribution law for a given mean and variance.

Sub NormalDistribSimul()
'The normal Law center to 100
'with nSimulation equal to 1000

nSimul = 1000
For i = 1 To nSimul
Cells(i, 1).Value2 = Application.Norm_Inv(Rnd, 100, 20)
Next i

End Sub

Find the Mean in a Range

Let us assume that on your worksheet you will like to know the average of the values over a specified part, the function average which takes as argument a range of data will solve your problem.

Sub CalculMeanRange()
'Calculate a Mean in a range

'Declaration
Dim Rng As Range

'Initialisation
Set Rng = Range("A1:A10")

'Use the mean function
Mean = Application.Average(Rng)

'Free variable
Set Rng = Nothing

End Sub

Find the Maximum in a Range

To find the maximum between several values you can use the Max function.

Sub FindMax()

'Declaration
Dim Max As Long

'Return the maximum between A1 & A2
Max = Application.WorksheetFunction.Max(Range("A1").Value2, Range("A2").Value2)
MsgBox "The maximum is : " & Max

End Sub

Factorial function with iterative and recursive method

In mathematics, the factorial function of a positive integer n is the product of all positive integers less than or equal to the n. The factorial function is denoted as n! .

The factorial function can be written in two ways: recursive or iterative. The most efficient method is the recursive method because it calls the function inside the function, which avoids the use of a loop. The iterative method corresponds to the use of a for loop.

With the iterative method :

Public Function Factorial_Iterative(n As Long)
'Factorial Iterative function

Factorial_Iterative = 1

For i = n To 1 Step -1
Factorial_Iterative = Factorial_Iterative * i
Next i

End Function

With the recursive method :

Public Function Factorial_Recursive(n As Long)
'Factorial Recursive function ==> faster method

If n > 1 Then
Factorial_Recursive = n * Factorial_Recursive(n - 1)
Else
Factorial_Recursive = 1
End If

End Function