Mod Operator in Excel

Visual Basic for Application (VBA) Mod is not considered to be a function, but rather than it is termed to be an operation that can be efficiently used to calculate out the remainder digit by just dividing a particular number with the divisor.

Moreover, in simple words, the mod operator will give out the remainder value, which is the left part of the number that cannot be divided completely.

Besides all these, we can say that the MOD is nothing but MODULO, which means the mathematical operation, and they are very similar to the division. Still, in the case of the outcome or the result, they are slightly different where the division takes the divided amount, but the respective MOD will take out the remainder of the division, respectively.

Let us now understand this with the help of the Example:

1. If we divide 23 by two divisions, the result is 11.5, but MOD is the remainder of the division, which is none other than 3. (Number 2 can divide only 20 and not 23, so the rest will be three effectively).
2. If we will divide the number that is 25 by three divisions, then the result is 8.333, but MOD is the remainder of the division, which is none other than 1. (Number 3 which will divide only 24 and not 25 so that the rest will be one, respectively).

And in case of the standard Microsoft Excel, the Mod is coined to be the function. But in the case of the VBA (Virtual Basic for Application), it is not a function. Instead, it is more than the function or can be termed to be the mathematical operator used to perform mathematical operations.

Syntax of the MOD in VBA

To remind us, this is not considered to be a function to have syntax. But, for the better acknowledgement to the user, we will put this in the words which are as follows:

Number 1 MOD Number 2 (Divisor)

Number 1: Number 1is nothing, but what is the number we are trying to divide?

Number 2: Similarly, Number 2 is termed to be the divisor, meaning we will divide Number 1 by this divisor effectively.

And the MOD is the result efficiently given byNumber 1 / Number 2.

How to use VBA MOD Function in excel?

We will now discuss how we can make use of the VBA MOD Function with the help of various examples, which are as follows:

# Example: 1

We will now pressAlt + F11to go to the VBA coding mode. After that, we will move to the insert menu and select theModuleto open a new one, as seen in the figure below.

Now soon after that, we will open out the syntax body, as depicted in the below attached figure. As we have named the macro subcategory VBA_MOD. And by doing this, it will become much easier and faster way for the respective users to identify the Code to run that needs to be run.

And the Code is as follows:

Now, as we calculate the Mod that includes the numbers, let us define an Integer"A". And this can be any respective alphabet, as these could be shown in the below-attached screenshot.

And the VBA (Virtual Basic for the applications) Code is as follows:

After that, we will implement the respective test and the same Example we have seen in the above screenshot. And for this, in the defined integer, we will be writing12 Mod 5. And this is a way that can be used to write the Mod operation in VBA (Virtual Basic for the application) and with the answer in the message box withthe msg function, as shown in the below-attached screenshot.

And the VBA (Virtual Basic for the applications) Code is as follows:

And once we are done with the above, we will run the entire Code with the help of the F5 key or by just making an effective click on the play button, as depicted in the below-attached figure. Soon after that, we will see the output or the result of 12 Mod 5, which is none other than two that are considered to be the remainder obtained after dividing 12 by 5, respectively.

# Example 2:

And it was known that another way could be effectively used to calculate the MOD in VBA. And for this, we will see the decimal number and then find MOD.

1. We will first Press Alt + F11 to go to the VBA coding mode. And then, after that, I will move to the insert menu and select Module to open a new module, as depicted in the figure below.
2. Now after that, in opened new Module frame, the syntax. And here, we have named the macro subcategory VBA_MOD1. By this name, we will be able to differentiate the name of Marco sequence respectively and avoid confusion.
And the VBA (Virtual Basic for the applications) Code is as follows:

1. After that, we will select an active cell where we need to see the result or the outcome with the help of ActiveCell.FormulaR1C1as cell C1.
2. After that, the Mod reference cells -2 and -1 are considered cells A1 and B1, respectively, as depicted in the screenshot below.
3. Type the VBA (Virtual Basic for the applications) Code as given below:

1. After that, in this particular step, we will select the reference cell as the Range where we will get the output with Range ("C3"). Select. And this will allow the particular cell C3 to effectively take the Range of individual cells from the -2 and -1 limits.
2. The VBA (Virtual Basic for the applications) Code is as follows:

1. After performing the above steps, we will run the Code with the help of the F5 key. Instead, we can click on the play button located just below the menu bar of the VBA Application window, as shown below in the attached figure.
2. From the above-attached screenshot, we can see that the output of VBA Coding is coming as1.4.
3. And furthermore, we can usually assign the written Code to a button in which we can directly click and then run the Code instead of running the Code by selecting the macro. And for this, we need to go to the Developer tab and then click onDesign Mode, as depicted in the below-attached figure.
4. And this will allow us to design the different tabs without affecting the written Code.
5. After that, we will move to theInsertoption under the Developer tab beside the Design Mode option. Then after that will select a Button as depicted in the figure below.
6. After that, we will draw the selected button and then name it MOD with the operation we perform.
7. And then, we will be right-clicking on the respective created MOD Button and then will select Assign Marco from the correct click list respectively.
8. From the Assign Marco window, we will select the created macro. And once we are done with this, we will click on the Ok button, as shown in the below-attached figure.
9. And for the purpose of testing the assigned macro in the created button, then in that case we need to get exit from Design Mode. Then we will put the cursor where we need to see the output or the outcome, as depicted in the below-attached figure.
10. And after that, we will click on MOD Button in order to get the output as depicted below

Pros of VBA MOD

The variables that are associated with the VBA MOD are as follows:

1. We can effectively calculate the Mod of various criteria using the VBA Mod in quick response time.
2. And to obtain the result or the outcomes from the Microsoft Excel function and the VBA Code will remain the same.

Things to Remember

The following things need to be remembered by any individual while working with the MOD operator in Microsoft Excel are as follows:

1. An individual must remember to save the file in the Macro Enable Worksheet, as this will allow an individual to use that particular file multiple times without losing the written Code efficiently.
2. And the other important thing that needs to be remembered by an individual is that they must always compile the Code before running it. And it will detect oit the error.
3. Moreover, it is well recommended to assign the respective written Code to a Button, by doing this will saves time respectively.