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