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
| Formula | Resuts |
|---|---|
| Formule | Results |
| Exp(0) | 1 |
| Abs(-10) | 10 |
| Atn(1) | 0.7854 |
| Cos(1) | 0.5403 |
| Fix(2) | 2 |
| Int(10.9) | 10 |
| Log(0.3) | -1.204 |
| Rnd() | 0.26 |
| Sgn(0) | 0 |
| Sin(0) | 0 |
| Tan(0) | 0 |
| Sqr(4) | 2 |
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
SKILLS 