Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

## Excel VBA MOD Function

MOD is nothing but MODULO is a mathematical operation. It is exactly the same as the division but the result is slightly different where division takes the divided amount but MOD takes the remainder of the division. For example: If you divide 21 by 2 division result is 10.50 by MOD is remainder of the division i.e. 1. (Number 2 can divide only 20, not 21, so the remainder is 1).

In the normal excel MOD is a function but in VBA it is not a function, it’s just a mathematical operator. In this article, we will look into this operator in detail.

### Formula of VBA MOD

Just to remind you this is not a function to have syntax. For our reader’s understanding let me put it in the word.

Number 1 MOD Number 2 (Divisor)

**Number 1** is nothing but what is the number we are trying to divide.

**Number 2 **this is the divisor i.e. we are going to divide **Number 1 **by this divisor.

**MOD **the result given by **Number 1 / Number 2.**

### How to use MOD in VBA?

After all the theory question is how to use. Ok, let us jump into practicality now. Follow below steps to write the code for MOD in VBA.

#### Example #1

**Step 1: **Create a macro name.

**Code:**

Sub MOD_Example1() End Sub

**Step 2: **Define one of the variables as “**Integer**”.

**Code:**

Sub MOD_Example1() Dim i As Integer End Sub

**Step 3: **Now perform the calculation as **“i = 20 MOD 2”**

As I told, in the beginning, MOD is an operator, not a function. So I have used the word MOD like how I enter a plus (+).

4.6 (247 ratings)

**Code:**

Sub MOD_Example1() Dim i As Integer i = 21 Mod 2 End Sub

**Step 4: **Now assign the value of “I” to the message box.

**Code:**

Sub MOD_Example1() Dim i As Integer i = 21 Mod 2 MsgBox i End Sub

**Step 5: **Run the code message box will show the value of “I” i.e. MOD value.

#### Example #2

**Step 1:** It always returns integer value i.e. without decimals if you supply the number in decimals. For example, look at the below code.

**Code:**

Sub MOD_Example2() Dim i As Integer i = 26.25 Mod 3 MsgBox i End Sub

**Step 2: **Divisor 3 can divide 24 so remainder here is 2.25 but MOD operator in VBA returns the integer value i.e. 2, not 2.25.

**Step 3: **Now I will modify the number from 26.51 and see the difference.

**Code:**

Sub MOD_Example2() Dim i As Integer i = 26.51 Mod 3 MsgBox i End Sub

**Step 4: **Now I will run this code and see what the result is.

Wow!!! We have got zero as the answer. The reason we got zero because VBA round the numbers like our bankers do i.e. any decimal point which is greater than 0.5 will be rounded up to next integer value. So in this case 26.51 is rounded up to 27.

Since 3 can divide the 27 by 9, we will not get any remainder values, so “I” value is equal to zero.

**Step 5:** Now I will supply the divisor value also in decimal points.

**Code:**

Sub MOD_Example2() Dim i As Integer i = 26.51 Mod 3.51 MsgBox i End Sub

**Step 6:** Run this code and see what the result is.

We got 3 as the answer because 26.51 will be rounded up to 27 and divisor value 3.51 will be rounded up to 4.

So if you divide 27 by 4 remainder is 3.

### Excel MOD Function vs VBA MOD Operator

**Step 1: **Now take a look at the difference between MOD function and VBA MOD operator. I have a value of 54.24 and divisor value is 10.

**Step 2: **Now If I apply the MOD function I will get the result as 4.25.

**Step 3: **But if you do the same operation with Excel VBA MOD we will 10 as the remainder, not 4.25.

**Code:**

Sub MOD_Example2() Dim i As Integer i = 54.25 Mod 10 MsgBox i End Sub

**Step 4: **Run this code and see what the result is.

### Things to Remember Here

- It is not a function but it is an arithmetic operator.
- This is roundup and rounds down decimal values unlike out MOD function in worksheet function.

### Recommended Articles

This has been a guide to VBA MOD Function. Here we learned how to use the VBA MODULO function along with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

- Excel VBA RoundUp | Examples
- Instance of Dictionary with Excel VBA
- VBA Worksheet
- VBA Cell References
- What is VBA Range?
- VBA Certification Course
- Combo Box in VBA

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion