VBA MOD

Updated June 9, 2023

VBA MOD

VBA Mod is not a function; it is an operation used for calculating the remainder digit by dividing a number with a divisor. Simply put, it gives us the remainder value, the left part of the Number that cannot be divided completely.

How to Use VBA MOD Function?

We will discuss how to use VBA MOD Function by using some examples.

Example #1

Press Alt + F11 to go into VBA coding mode. Afterward, go to the insert menu and select Module to open a new module.

Now open the body of syntax as shown below. We have named the macro subcategory VBA_MOD. This will make it easy for users to identify the code to run.

Code:

Sub VBA_MOD()

End Sub

Now, as we calculate the Mod, which includes numbers, define an Integer “A.” This can be any alphabet, as shown below screenshot.

Code:

Sub VBA_MOD()

    Dim A As Integer

End Sub

Now we will implement and test the same example we have seen above. For this, we will write 12 Mod 5 in a defined integer. This is a way to write the Mod operation in VBA and the answer in the message box with the msg function, as shown below.

Code:

Sub VBA_MOD()

    Dim A As Integer

    A = 12 Mod  5

    MsgBox A

End Sub

Once done, run the complete code using the F5 key or click the play button as shown below. We will see the output of 12 Mod 5 as 2, which is the remainder obtained after dividing 12 by 5.

 

Example #2

There is another way to calculate the MOD in VBA. For this, we will see a decimal number and find how MOD.

Press Alt + F11 to go into VBA coding mode. Afterward, go to the insert menu and select Module to open a new module.

In opened new Module frame the syntax. Here we have named the macro subcategory VBA_MOD1. By this, we can differentiate the name of the Marco sequence, which we will see ahead.

Code:

Sub VBA_MOD1()

End Sub

Now select an active cell where we need to see the result by ActiveCell.FormulaR1C1 as cell C1. Now Mod reference cells -2 and -1 are considered as cell A1 and cell B1 respectively, as shown below.

Code:

Sub VBA_MOD1()

    ActiveCell.FormulaR1C1 = "=MOD(RC[-2],RC[-1])"

End Sub

Now select the reference cell as the range where we will the output with range (“C3”). Select. This will allow cell C3 to take the range of respective cells from the -2 and -1 limits.

Code:

Sub VBA_MOD1()

    ActiveCell.FormulaR1C1 = "=MOD(RC[-2],RC[-1])"

    Range("C3").Select

End Sub

Now, Run the code using the F5 key or click on the play button, which is located below the menu bar of the VBA Application window, as shown below.

As we can see in the above screenshot, the output of VBA Coding is coming as 1.4.

We can assign the written code to a button where we can directly click and run the code instead of running the code by selecting the macro. Go to the Developer tab and click Design Mode, as shown below.

This will allow us to design different tabs without affecting the written code. Now go to the Insert option under the Developer tab beside the Design Mode option. Then select a Button as shown below.

Now draw the selected button and name it MOD with the name of the operation we will perform.

Right-click on created MOD Button and select Assign Marco from the right-click list.

Now from the Assign Marco window, select the created macro. Once done, click on Ok, as shown below.

To test the assigned macro in the created button, exit from Design Mode. Then put the cursor where we need to see the output, as shown below.

Now click on MOD Button to see the result as shown below.

As we can see in the above screenshot, we have the output as 1.4, which is Mod of number 23.4 with divisor 2.

Pros of VBA MOD

• We can calculate the Mod of multiple criteria with the help of VBA Mod in a quick time.
• The obtained result from the Excel function and VBA Code will be the same.

Things to Remember

• Don’t forget the save the file in Macro Enable Worksheet. This will allow us to use that file multiple times without losing the written code.
• Always compile the code before running. This will detect the error instead of getting an error during the actual run.
• It is recommended to assign the written code to a Button. This process saves time.

Recommended Articles

This has been a guide to Excel VBA MOD. Here we discussed how to use VBA MOD Function to remove spaces, some practical examples, and a downloadable Excel template. You can also go through our other suggested articles-

1. VBA TRIM
2. VBA Arrays
3. Excel VBA Macro
4. VBA Find