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 Sheets("Math").Activate Randomize 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 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 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 '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
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
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 '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 between several values you can use the Max function.
Sub FindMax() '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 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) Else Factorial_Recursive = 1 End If End Function