In terms of the things that we can work on, we can observe **Visual Basic for Applications (VBA)** as an extension of Excel. All the things that are at our disposal in Excel, are available in VBA as well.

In the example below, we will show how to find the minimum and maximum values in our range. We will encompass two scenarios: if the range is in our Worksheet, or if it is in our VBA code.

## Find the Maximum and Minimum Value of a Range in the Worksheet

For our example, we will use a **table of different salespersons and achieved sales in the period from 2012 to 2019**:

After this, we need to open VBA, and we do that by clicking **ALT + F11 on our keyboard**. After that, we go to the **Insert tab** and **choose Module**:

When we have our **Module opened**, we will insert the following formula to get the **largest number (maximum)** in our **range (A3:I8)**:

1 2 3 4 5 6 7 |
Sub MaximumInRange() Dim r As Range Dim m As Double Set r = Range("A3:I8") m = Application.WorksheetFunction.Max(r) MsgBox "The largest number in our range Is " & m End Sub |

This formula declares two **variables, r, and m**. Then it **sets variable r** to be equal to our range. **M variable** will be equal to the **maximum value of our range**, and the message box will be used to present this value to a user.

When we execute the code by **pressing F5** on our keyboard, this is what we get:

To get the lowest value, all we have to do is **copy/paste the code** and **change Max in our formula with Min**, and the text in the message box. This will be our formula:

1 2 3 4 5 6 7 |
Sub MinimumInRange() Dim r As Range Dim m As Double Set r = Range("A3:I8") m = Application.WorksheetFunction.Min(r) MsgBox "The smallest number in our range Is " & m End Sub |

When we execute this code (by **pressing F5** while located in the formula in the VBA) this is the message that the user can see:

## Find the Maximum and Minimum Value of a Range in VBA

We use the same function to determine the **largest and smallest value if the range is located in VBA**, rather than in the worksheet. To show this, we will create the following formula:

1 2 3 4 5 6 7 8 9 10 11 |
Sub MaximumAndMinimumInVBA() Dim a, b, c, d, max, min As Integer a = 70 b = 86 c = 28 d = 43 max = WorksheetFunction.max(a, b, c, d) min = WorksheetFunction.min(a, b, c, d) MsgBox "Largest number is " & max MsgBox "Smallest number is " & min End Sub |

In this formula, we have created six variables, four to hold various numbers, one to show the maximal, and one to show the minimal value of our numbers. We use **Worksheet.Function** **with min and max**, add our numbers, and then show the values through message boxes. This is what our code looks like in the VBA:

When we execute the code **(F5 on our keyboard)** we will get **two messages **(one for the largest, and one for the smallest number in our range):