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 Number

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

    Dim i As Variant
    Dim Rng As Range
    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
Several random number

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

Normal Law

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

    Dim Rng As Range
    Set Rng = Range("A1:A10")
    'Use the mean function
    Mean = Application.Average(Rng)

    'Free variable
    Set Rng = Nothing

End Sub
Mean in a range

Find the Maximum in a Range

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

Sub Find_Max()

    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
Highlight blank or empty cells

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)
        Factorial_Recursive = 1
    End If

End Function


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s