EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Math & Trig Functions VBA MOD
Secondary Sidebar
VBA Math & Trig Functions
  • VBA Math & Trig
    • VBA Random Number
    • VBA Number Format
    • VBA Integer
    • VBA MOD
    • VBA IsNumeric
    • VBA Round
    • VBA INT
    • VBA RoundUp
    • VBA Intersect
    • VBA Randomize
    • VBA Square Root

VBA MOD

By Ashwani JaiswalAshwani Jaiswal

VBA MOD

VBA MOD

VBA Mod is not a function, in fact, it is an operation which is used for calculating the remainder digit by dividing a number with divisor. In simple words, it gives us the remainder value, which is the remained left part of Number which could not get divided completely.

How to Use VBA MOD Function?

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)
You can download this VBA MOD Function Excel Template here – VBA MOD Function Excel Template

Example #1

Press Alt + F11 to go in VBA coding mode. After that go to insert menu and select Module to open a new module.

Insert Module

Now open the body of syntax as shown below. We have names the macro subcategory as VBA_MOD. By this, it will become easy for users to identify the code to run.

Code:

Sub VBA_MOD()

End Sub

VBA MOD Example 1-1

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

Code:

Sub VBA_MOD()

    Dim A As Integer

End Sub

VBA MOD Example 1-2

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

Code:

Sub VBA_MOD()

    Dim A As Integer

    A = 12 Mod  5

    MsgBox A

End Sub

VBA MOD Example 1-3

Once done, run the complete code by using F5 key or clicking on 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.

VBA MOD Example 1-4 

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 in VBA coding mode. After that go to insert menu and select Module to open a new module.

Insert Module

In opened new Module frame the syntax. Here we have named the macro subcategory as VBA_MOD1. By this, we will be able to differentiate the name of Marco sequence. Which we will see ahead.

Code:

Sub VBA_MOD1()

End Sub

VBA MOD Example 2-1

Now select an active cell where we need to see the result by ActiveCell.FormulaR1C1 as cell C1. Now Mod reference cell -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

VBA MOD Example 2-2

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

Code:

Sub VBA_MOD1()

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

    Range("C3").Select

End Sub

VBA MOD Example 2-3

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

VBA MOD Example 2-4

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 can directly click and run the code instead of running the code in by select the macro. For this go to the Developer tab and click on Design Mode as shown below.

Design Mode

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

Insert Button

Now draw the selected button and name it as MOD with the name of operation which we will be going to perform.

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

Assign Macro

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

Assign Macro to Button

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

VBA MOD Example 2-5

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

Pros of VBA MOD

  • We can calculate Mod of multiple criteria with the help of VBA Mod in quick time.
  • An 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 time without losing the written code.
  • Always compile the code before running. This will detect the error instead of getting error while 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 along with some practical examples and downloadable excel template. You can also go through our other suggested articles-

  1. VBA TRIM
  2. VBA Arrays
  3. VBA Select Case
  4. VBA Find
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more