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


    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
Mathematicals functions

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 Number

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

    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

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

Normal Law

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

    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 between several values you can use the Max function.

Sub FindMax()

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

Google photo

You are commenting using your Google 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