# Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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.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

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).

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

<<Go Back to Calculating Percentages in Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

1. 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 presence and targeted presence we will calculate their weekly percentages here.

In the process of creating PivotTable, make sure to check the Add this data to the Data Model option.

â€¢ Drag down the SalesPerson and Email Id fields to the Rows area and Weekly Presence and Targeted Presence fields to the Values area.

â€¢ To add a new measure for calculating percentages right-click on the table name Range and select the Add Measure option.

â€¢ In the Measure dialog box, enter Percentage as Measure Name and use the following formula in the Formula box
=[Sum of Weekly Presence]/[Sum of Targeted Presence]

â€¢ Choose Number as Category and then select the Percentage option.
â€¢ Press OK.

â€¢ Drag down the newly created Percentage measure to the Values area.

Afterward, you will get the Percentage column in your PivotTable.

Now, if you change any data of your main dataset then the values of the PivotTable will be updated also.
For instance, we have changed the values of the Weekly Presence column in the main dataset.

â€¢ Now go to the sheet with PivotTable and then go to the PivotTable Analyze tab >> Refresh group >> Refresh option.

After that, the percentages will be updated also.

To send these percentages to individual employees automatically using VBA script you can follow this article https://www.exceldemy.com/send-bulk-email-from-outlook-using-excel/