Excel VBA Mod Operator Problem

Excel has a MOD function that returns the remainder after a division. Excel VBA also has the Mod operator. Unfortunately, they return different results when it comes to negatives.

The Excel MOD function works correctly – see examples below.

The Mod operator works ok for positive numbers – image below.

But it returns the wrong result for negatives – image below.

The fix is to create your own function for Mod in VBA – image below.

You can see the results for the NewMod function in the images below.

The Excel MOD function is useful and offers many solutions to common problems. I have written about it a few times, links below.

If you need to handle the mod calculation in VBA, and you might encounter negatives, then you will need to create your own function.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Excel VBA Mod Operator Problem

  1. Just noting that you can tap into Excel’s MOD function from within the VBA environment. This function will duplicate the one you posted in your blog (although I renamed it)…

    Function xlMOD(Num As Long, Div As Long) As Long
    xlMOD = Evaluate(“MOD(” & Num & “,” & Div & “)”)
    End Function