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