# Math Functions

Last Updated on February 7, 2022 – 2 min read

In this article, you will discover several mathematical functions available in VBA.

## Basics Math Functions in VBA

Can you do maths with VBA? VBA Math Functions help us to perform arithmetic operations and complex mathematical operations. Some mathematics functions available in VBA are listed below.

Sub Math_Functions()

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 value in VBA

For probability and statistics, you will need to use random variables. You can create a random variable by using the Rnd function.

The function generates a number between 0 and 1. The number can be equal to 0 but strictly less than 1. By using Rnd, you can create random variables on selected intervals.

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

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

End Sub

## Random values in a range of cells

To display several random numbers in a range of cells with VBA, you can call the Rnd function for cells in a range.

Sub Range_Rnd()
'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

## Normal Law distribution

In the theory of probabilities, the Normal distribution called Gauss law is a probability law. It allows you to simulate natural and sociological phenomena.

You can simulate a normal law in VBA with the Norm_inv function. The function returns a Normal distribution 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

To know the average of values over a specified range on your sheet, you can use the Average function. The function takes as an argument a range of cells.

Sub Calcul_Mean_Range()
'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 values between several cells, you can use the Max function.

Sub Find_Max()

'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 n!.

The factorial function can be written in two ways, recursive or iterative.

The most efficient method is the recursive method by calling the function inside itself, without using a loop. The iterative method needs a For loop.

An iterative method to write Factorial function :

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

A recursive method to write Factorial function :

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