EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home VBA VBA Resources VBA Math & Trig Functions VBA MOD

VBA MOD

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 9, 2023

VBA MOD

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.

Watch our Demo Courses and Videos

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

How to Use VBA MOD Function?

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

You can download this VBA MOD Function Excel Template here – VBA MOD Function Excel Template

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.

Insert 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

VBA MOD Example 1-1

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

VBA MOD Example 1-2

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

VBA MOD Example 1-3

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.

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 into VBA coding mode. Afterward, go to the 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 VBA_MOD1. By this, we can differentiate the name of the 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 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

VBA MOD Example 2-2

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

VBA MOD Example 2-3

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.

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

Design Mode

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.

Insert Button

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.

Assign Macro

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

Assign Macro to Button

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 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
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • 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

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

EDUCBA

Download VBA MOD Function Excel Template

EDUCBA

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

Let’s Get Started

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.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

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

🚀 Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW