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

Code Overview for Calculating the Percentages via VBA

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 Percentages in Excel with VBA (Step by Step Analysis)

We’ve got a data set with the names of some products, their expected sales, and achieved sales of a company. We’ll determine the percentage of achieved sales of Full Sleeves compared to the expected sales in VBA.

⧪ Step 1 – Declaring the Variable Whose Percentage will be Calculated

We have to declare the variable whose percentage will be calculated. 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

Ee 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

To calculate the percentage, divide Value1 with Value2, then multiply the result with 100.

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

⧪ Step 4 (Optional) – Displaying the Result

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

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 for Calculating Percentages in Excel with VBA

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

⧭ 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. We’ve entered F3.

• 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

⧭ 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:

• Input 2 ranges of cells in the formula:

[This is an array formula. Press Ctrl + Shift +Enter to apply it unless you use Office 365.]

• It’ll return the percentages of the range C4:C13 with respect to the range D4:D13.

• Enter a range and a single value.

• It’ll return the percentages of the range D4:D13 with respect to 5,00,000.

• Enter two single values.

• It’ll return the percentage of 180,000 with respect to 343,000.

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

⧪ Step 1 – Inserting the UserForm

• Go to the Insert tab and select the 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.
• Drag 5 Labels (Label1, Label2, Label3, Label4, and Label5) and 5 TextBoxes (TextBox1, TextBox2, TextBox3, TextBox4, and TextBox5).
• 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.
• 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

• 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

• 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

• 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 – Code for Running the UserForm

• 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

• Run the Macro called Run_UserForm.

• The UserForm will load with the name Percentage with VBA.
• Select 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.

• If you select Range of Cells, three 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. We’ve entered D3:D13, C3:C13 (with Headers), and F3.

• Click OK.
• You’ll find the required percentages in a range starting from F3 (with a Header called Percentage).

Download the Practice Workbook

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