While working with VBA in Excel, we often have to calculate the percentage of a value with respect to another value. Calculating percentages is widely used in almost all the sectors surrounding us, starting from the marks of students in an examination to the sales growth of a business. In this article, Iâ€™ll show you how you can calculate percentages with VBA in Excel, both of a single value and a range of values.

**Table of Contents**Expand

**Calculate Percentage in Excel VBA (Quick View)**

```
Sub Calculate_Percentage()
Dim Value1 As Variant
Value1 = Range("D4").Value
Dim Value2 As Variant
Value2 = Range("C4").Value
Dim Result As Variant
Result = (Value1 / Value2) * 100
MsgBox "The Required Percentage is: " + Format(Result, "0.00") + "%"
End Sub
```

**How to Calculate Percentage in Excel VBA (Step by Step Analysis)**

Here weâ€™ve got a data set with the **names of some products**, their **expected sales,** and **achieved sales** of a company called Jupyter Group.

Now, letâ€™s try to figure out the percentage of** achieved sales** of **Full Sleeves** with respect to the expected sales in **VBA**.

Iâ€™ll show you the step-by-step procedure to accomplish this.

**â§ª Step 1: Declaring the Variable Whose Percentage will be Calculated**

First of all, we have to declare the variable whose percentage will be calculated. Here, we want to calculate the percentage of **achieved sales** of **Full Sleeves**. That is the value of cell **D3**.

```
Dim Value1 As Variant
Value1 = Range("D4").Value
```

**â§ª Step 2: Declaring the Variable with Respect to Which the Percentage will be Calculated**

Next, we have to declare the variable with respect to which the percentage will be calculated. Here itâ€™s the expected sales of Full Sleeves, the value of cell **C4**.

```
Dim Value2 As Variant
Value2 = Range("C4").Value
```

**â§ª Step 3: Calculating the Percentage**

Now the most important step, calculating the percentage. To calculate the percentage, first, you have to divide **Value1** with **Value2**, then multiply the resultant with **100**.

```
Dim Result As Variant
Result = (Value1 / Value2) * 100
```

**â§ª Step 4 (Optional): Displaying the Result**

Finally, you can display the result with a **Message Box**.

`MsgBox "The Required Percentage is: " + Format(Result, "0.00") + "%"`

**[**Here weâ€™ve used **the Format function** of **VBA** to round the percentage to **2** decimal places.**]**

Therefore, the complete **VBA** code will be:

**â§ VBA Code:**

```
Sub Calculate_Percentage()
Dim Value1 As Variant
Value1 = Range("D4").Value
Dim Value2 As Variant
Value2 = Range("C4").Value
Dim Result As Variant
Result = (Value1 / Value2) * 100
MsgBox "The Required Percentage is: " + Format(Result, "0.00") + "%"
End Sub
```

**â§ Output:**

Run the code by clicking the **Run Sub/UserForm** button in the VBA toolbar.

Itâ€™ll display the required percentage in a **Message Box**, which is **52.48%**.

**Examples to Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)**

Weâ€™ve learned to calculate the percentage with **VBA** in Excel. This time weâ€™ll explore some examples involving calculating the percentage with **VBA**.

**Example 1: Developing a Macro to Calculate the Percentage of a Range of Cells in Excel VBA**

First, weâ€™ll develop a **Macro** to calculate the percentages of all the **achieved cells** with respect to the **expected sales**.

To achieve this, we have to iterate through **2 for-loops** thatâ€™ll cover the whole range, that means, the sales of all the products.

Therefore, the **VBA** code will be:

**â§ VBA Code:**

```
Sub Percentage_of_a_Range()
Set Range1 = Range(InputBox("The Range of Cells Whose Percentage will be Calculated: "))
Set Range2 = Range(InputBox("The Range of Cells with Respect to Which the Percentage will be Calculated: "))
Starting_Cell = InputBox("The First Cell of the Output Range: ")
Range(Starting_Cell).Cells(1, 1) = "Percentage"
Number_of_Cells_1 = Range1.Rows.Count * Range1.Columns.Count
Number_of_Cells_2 = Range2.Rows.Count * Range2.Columns.Count
If Number_of_Cells_1 <= Number_of_Cells_2 Then
Â Â Â For i = 2 To Range1.Rows.Count
Â Â Â Â Â Â Â For j = 1 To Range1.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Range(Starting_Cell).Cells(i, j) = (Range1.Cells(i, j) / Range2.Cells(i, j)) * 100
Â Â Â Â Â Â Â Next j
Â Â Â Next i
Else
Â Â Â For i = 2 To Range2.Rows.Count
Â Â Â Â Â Â Â For j = 1 To Range2.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Range(Starting_Cell).Cells(i, j) = (Range1.Cells(i, j) / Range2.Cells(i, j)) * 100
Â Â Â Â Â Â Â Next j
Â Â Â Next i
End If
End Sub
```

**â§ Output:**

Run this code. Youâ€™ll get **3** **Input Boxes**. The first one will ask you to enter the range whose percentage will be calculated.

Here itâ€™s **D3:D13** (**Achieved Sales**, including the **Header**).

The second box will ask you to enter the range with respect to which the percentage will be calculated.

Here itâ€™s **C3:C13** (**Expected Sales**, including the** Header**).

The final box will ask you to enter the first cell of the range where the output will be stored. Iâ€™ve entered **F3**.

Then click **OK**. Youâ€™ll get the percentages of all the achieved cells with respect to the expected cells in a range starting from **F3** (With a **Header** of **Percentage**).

**Example 2: Creating a User-Defined Function to Calculate the Percentage of a Single Value or a Range of Values in Excel VBA**

In the previous section, we developed a **Macro** to calculate the percentage of a range of cells with respect to another range.

This time weâ€™ll create a **User-Defined function** to calculate the percentage of a single value or a range of values.

Our code will have **3 **sections. One section will work if both the inputs are **ranges of cells**.

Another section will work if the first input is a **range of cells** and the second input is a **single value**.

And the final section will work if both the inputs are **single values**.

Weâ€™ll use the **VarType function** of **VBA** to differentiate the 3 sections.

The complete **VBA** code will be:

**â§ VBA Code:**

```
Function PERCENTAGE(Value1, Value2)
If VarType(Value1) = 8204 And VarType(Value2) = 8204 Then
Â Â Â Dim Output1() As Variant
Â Â Â If Value1.Rows.Count * Value1.Columns.Count <= Value2.Rows.Count * Value2.Columns.Count Then
Â Â Â Â Â Â Â ReDim Output1(Value1.Rows.Count - 1, Value1.Columns.Count - 1)
Â Â Â Â Â Â Â For i = 1 To Value1.Rows.Count
Â Â Â Â Â Â Â Â Â Â Â For j = 1 To Value1.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Output1(i - 1, j - 1) = (Value1.Cells(i, j) / Value2.Cells(i, j)) * 100
Â Â Â Â Â Â Â Â Â Â Â Next j
Â Â Â Â Â Â Â Next i
Â Â Â Â Â Â Â PERCENTAGE = Output1
Â Â Â Else
Â Â Â Â Â Â Â ReDim Output1(Value2.Rows.Count - 1, Value2.Columns.Count - 1)
Â Â Â Â Â Â Â For i = 1 To Value2.Rows.Count
Â Â Â Â Â Â Â Â Â Â Â For j = 1 To Value2.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Output1(i - 1, j - 1) = (Value1.Cells(i, j) / Value2.Cells(i, j)) * 100
Â Â Â Â Â Â Â Â Â Â Â Next j
Â Â Â Â Â Â Â Next i
Â Â Â Â Â Â Â PERCENTAGE = Output1
Â Â Â End If
ElseIf VarType(Value1) = 8204 And VarType(Value2) <> 8204 Then
Â Â Â Dim Output2() As Variant
Â Â Â ReDim Output2(Value1.Rows.Count - 1, Value1.Columns.Count - 1)
Â Â Â For i = 1 To Value1.Rows.Count
Â Â Â Â Â Â Â For j = 1 To Value1.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Output2(i - 1, j - 1) = (Value1.Cells(i, j) / Value2) * 100
Â Â Â Â Â Â Â Next j
Â Â Â Next i
Â Â Â PERCENTAGE = Output2
Else
Â Â Â Dim Output3 As Variant
Â Â Â Output3 = (Value1 / Value2) * 100
Â Â Â PERCENTAGE = Output3
End IfÂ Â Â
End Function
```

**â§ Output:**

First, we input **2** ranges of cells.

`=PERCENTAGE(D4:D13,C4:C13)`

**[Array Formula. **So donâ€™t forget to press **CTRL + SHIFT +ENTER** unless you are in **Office 365**.]

Itâ€™ll return the percentages of the range **C4:C13** with respect to the range **D4:D13**.

Next, weâ€™ll enter a range and a single value.

`=PERCENTAGE(D4:D13,500000)`

**[**Again **Array Formula. **So donâ€™t forget to press **CTRL + SHIFT +ENTER** unless you are in **Office 365**.]

Itâ€™ll return the percentages of the range **D4:D13** with respect to **5,00,000**.

Finally, we enter two single values.

`=PERCENTAGE(180000,343000)`

Itâ€™ll return the percentage of **1,80,000** with respect to **3,43,000**.

**Example 3: Developing a UserForm to Calculate the Percentage of a Single Value or a Range of Values in Excel VBA**

Finally, weâ€™ll develop a **UserForm** to accomplish all the tasks together.

**â§ª Step 1: Inserting the UserForm**

Go to the **Insert>UserForm** option in the **VBA** toolbar to insert a new **UserForm**.

**â§ª Step 2: Dragging Tools to the UserForm**

A **UserForm** called **UserForm1** will open, along with a **Toolbox** called **Control**.

Move your mouse over the **Toolbox** and drag a **ListBox** (**ListBox1**) to the top left corner of the UserForm.

**Similarly drag 5 Labels (Label1, Label2, Label3, Label4,** and **Label5**) and **5 TextBoxes** (**TextBox1, TextBox2, TextBox3, TextBox4,** and **TextBox5**).

Finally, drag a **CommandButton** (**CommandButton1**) to the bottom right corner.

Change the displays of the **Labels** to **Value, With Respect to, Range, With Respect to,** and **Output Location (First Cell)** respectively.

Also, change the display of the **CommandButton1** to **OK**.

**â§ª Step 3: Writing Code for ListBox1**

Double click on **ListBox1**. A **Private Subprocedure** called **ListBox1_Click** will open. Enter the following code there.

```
Private Sub ListBox1_Click()
If UserForm1.ListBox1.Selected(0) = True Then
Â Â Â UserForm1.Label1.Visible = True
Â Â Â UserForm1.Label2.Visible = True
Â Â Â UserForm1.Label3.Visible = False
Â Â Â UserForm1.Label4.Visible = False
Â Â Â UserForm1.Label5.Visible = False
Â Â Â UserForm1.TextBox1.Visible = True
Â Â Â UserForm1.TextBox2.Visible = True
Â Â Â UserForm1.TextBox3.Visible = False
Â Â Â UserForm1.TextBox4.Visible = False
Â Â Â UserForm1.TextBox5.Visible = False
ElseIf UserForm1.ListBox1.Selected(1) = True Then
Â Â Â UserForm1.Label1.Visible = False
Â Â Â UserForm1.Label2.Visible = False
Â Â Â UserForm1.Label3.Visible = True
Â Â Â UserForm1.Label4.Visible = True
Â Â Â UserForm1.Label5.Visible = True
Â Â Â UserForm1.TextBox1.Visible = False
Â Â Â UserForm1.TextBox2.Visible = False
Â Â Â UserForm1.TextBox3.Visible = True
Â Â Â UserForm1.TextBox4.Visible = True
Â Â Â UserForm1.TextBox5.Visible = True
End If
End Sub
```

**â§ª Step 4: Writing Code for TextBox3**

Similarly, double click on **TextBox3**. Another** Private Subprocedure** called **TextBox3_Change **will open. Enter the following code there.

```
Private Sub TextBox3_Change()
On Error GoTo Message
Range(UserForm1.TextBox3.Text).Select
Message:
Â Â Â X = 240
End Sub
```

**â§ª Step 5: Writing Code for TextBox4**

Then double click on **TextBox4**. When the **Private Subprocedure** called **TextBox4_Change** opens, enter this code there.

```
Private Sub TextBox4_Change()
On Error GoTo Message
Range(UserForm1.TextBox4.Text).Select
Message:
X = 240
End Sub
```

**â§ª Step 6: Writing Code for TextBox5**

Also double-click on **TextBox5**. After the **Private Subprocedure** called **TextBox5_Change **opens, insert the following code there.

Private Sub TextBox5_Change()

```
On Error GoTo Message
Range(UserForm1.TextBox5.Text).Select
Message:
Â Â Â X = 240
End Sub
```

**â§ª Step 7: Writing Code for CommandButton1**

Double click on the **CommandButton1** (displayed as **OK**). A **Private Subprocedure** called **CommandButton1_Click** will open. Enter the following code there.

```
Private Sub CommandButton1_Click()
If UserForm1.ListBox1.Selected(0) = True Then
Â Â Â On Error GoTo Message1
Â Â Â Result = (UserForm1.TextBox1.Text / UserForm1.TextBox2.Text) * 100
Â Â Â MsgBox "The Required Percentage is: " + Format(Result, "0.00") + "%"
ElseIf UserForm1.ListBox1.Selected(1) = True Then
Â Â Â On Error GoTo Message2
Â Â Â Set Range1 = Range(UserForm1.TextBox3.Text)
Â Â Â Set Range2 = Range(UserForm1.TextBox4.Text)
Â Â Â Starting_Cell = UserForm1.TextBox5.Text
Â Â Â Range(Starting_Cell).Cells(1, 1) = "Percentage"
Â Â Â Number_of_Cells_1 = Range1.Rows.Count * Range1.Columns.Count
Â Â Â Number_of_Cells_2 = Range2.Rows.Count * Range2.Columns.Count
Â Â Â If Number_of_Cells_1 <= Number_of_Cells_2 Then
Â Â Â Â Â Â Â For i = 2 To Range1.Rows.Count
Â Â Â Â Â Â Â Â Â Â Â For j = 1 To Range1.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Range(Starting_Cell).Cells(i, j) = (Range1.Cells(i, j) / Range2.Cells(i, j)) * 100
Â Â Â Â Â Â Â Â Â Â Â Next j
Â Â Â Â Â Â Â Next i
Â Â Â Else
Â Â Â Â Â Â Â For i = 2 To Range2.Rows.Count
Â Â Â Â Â Â Â Â Â Â Â For j = 1 To Range2.Columns.Count
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Range(Starting_Cell).Cells(i, j) = (Range1.Cells(i, j) / Range2.Cells(i, j)) * 100
Â Â Â Â Â Â Â Â Â Â Â Next j
Â Â Â Â Â Â Â Next i
Â Â Â End If
Else
Â Â Â MsgBox "Select one between Single Value or Range of Cells.", vbExclamation
End If
Exit Sub
Message1:
Â Â Â MsgBox "Enter Valid Numbers for the Values.", vbExclamation
Exit Sub
Message2:
Â Â Â MsgBox "Enter Valid Range of Cells for the Ranges.", vbExclamation
End Sub
```

**â§ª Step 8: Writing Code for Running the UserForm**

Now is the final step. Insert a new **Module** from the **VBA** toolbar and insert the following code.

```
Sub Run_UserForm()
UserForm1.Caption = "Percantage with VBA"
UserForm1.TextBox3.Text = Selection.Address
UserForm1.ListBox1.AddItem "Single Value"
UserForm1.ListBox1.AddItem "Range of Cells"
UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.Label1.Visible = False
UserForm1.Label2.Visible = False
UserForm1.Label3.Visible = False
UserForm1.Label4.Visible = False
UserForm1.Label5.Visible = False
UserForm1.TextBox1.Visible = False
UserForm1.TextBox2.Visible = False
UserForm1.TextBox3.Visible = False
UserForm1.TextBox4.Visible = False
UserForm1.TextBox5.Visible = False
Load UserForm1
UserForm1.Show
End Sub
```

**â§ª Step 9: Running the UserForm**

Your** UserForm** is now ready to use. To run it, run the **Macro** called **Run_UserForm**.

The **UserForm** will load with the name **Percentage with VBA**. Select one between **Single Value** or **Range of Values**.

If you select **Single Value**, **2 TextBoxes** will appear. Enter the value whose percentage you want to calculate and the value with respect to which to calculate. Iâ€™ve entered **46** and **205**.

Click **OK**. A **Message Box** will display the required percentage.

Again, if you select **Range of Cells**, **3 TextBoxes** will appear asking you to input the range whose percentage you want to calculate, the range with respect to which to calculate, and the first cell of the output location.

Iâ€™ve entered **D3:D13, C3:C13 (**with **Headers**), and **F3**.

Click **OK**. And youâ€™ll find the required percentages in a range starting from **F3** (with a **Header** called **Percentage**).

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

So these are a few examples in which you can use Excel VBA to calculate the percentage. Hope the examples made everything pretty clear for you. Do you have any questions? Feel free to ask us.

**Related Articles**

- How to Calculate Percentage for Multiple Rows in Excel
- How to Calculate Percentage of Filled Cells in Excel
- How to Calculate Percentage above Average in Excel
- How to Apply Percentage Formula in Excel for Marksheet
- How to Apply Percentage Formula for Multiple Cells in Excel
- IF Percentage Formula in Excel
- How to Find the Percentage of Two Numbers in Excel
- How to Calculate Contribution Percentage with Formula in Excel
- How to Use Food Cost Percentage Formula in Excel
- Calculate Percentage Using Absolute Cell Reference in Excel
- How to Calculate Variance Percentage in Excel

**<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel**

Hello..!! Iam sending auto mulitple mails weekly of performance where i need to show percentage to them on mail body.. Percentage should reflect soon the pivot will get refresh..So request can you pls advice with the vba script for same ..

Hello

Tushar Chauhan,Thanks for reaching us. According to your stated scenario, I have created the following dataset for some imaginary employees. Using their

weekly presenceandtargeted presencewe will calculate their weekly percentages here.In the process of creating PivotTable, make sure to check the

Add this data to the Data Modeloption.â€¢ Drag down the

SalesPersonandEmail Idfields to theRowsarea andWeekly PresenceandTargeted Presencefields to theValuesarea.â€¢ To add a new measure for calculating percentages

right-clickon the table nameRangeand select theAdd Measureoption.â€¢ In the

Measuredialog box, enterPercentageasMeasure Nameand use the following formula in theFormulabox=[Sum of Weekly Presence]/[Sum of Targeted Presence]â€¢ Choose

NumberasCategoryand then select thePercentageoption.â€¢ Press

OK.â€¢ Drag down the newly created

Percentagemeasure to theValuesarea.Afterward, you will get the

Percentagecolumn in yourPivotTable.Now, if you change any data of your main dataset then the values of the

PivotTablewill be updated also.For instance, we have changed the values of the

Weekly Presencecolumn in the main dataset.â€¢ Now go to the sheet with

PivotTableand then go to thePivotTable Analyzetab >>Refreshgroup >>Refreshoption.After that, the percentages will be updated also.

To send these percentages to individual employees automatically using

VBA scriptyou can follow this article https://www.exceldemy.com/send-bulk-email-from-outlook-using-excel/After going through this article, you will notice different ways of doing this task.

Hello.. iam using this code but getting error 6 : Overflow.. Can you pls help on same to work on the code..

Hi Tushar Chauhan,

kindly let me know which code is causing this problem.

Hello Tanjima…

Thank you so much for helping me out..