« Time is not on my side | Main | Lovely Rita PMP meter maid »

VBA - Integer Division and Mod

Many Microsoft Project users are not professional programmers so they might not be aware of some of the basics of visual basic. One of them which surprised me when I first ran across it was the "integer division" operator. Now most people know the typical add + , subtract -, multiply *, and divide / operators and what results they bring. But there are really two more which are quite useful in certain situations.

The first is the integer division operator which is a backslash "\". Do not confuse this with the forward slash "/" which is used for regular division. The results of this operator are that division takes place as usual except any non-integer remainder is discarded. Here are a couple of examples to illustrate.

10/4 = 2.5
10\4 = 2

5.423/1 = 5.423
5.423\1 = 5

As you can probably guess, integer division is a handy way of dividing and rounding down in a single step.

Another related operator is the MOD operator. It is similar to integer division only it returns only the remainder. Here are a couple of examples.

6 MOD 4 = 2
12 MOD 4 = 0

By putting them together you can break numbers into their component parts. Doing date math is an easy way to see how this works. Let's let "Days" be a number of days. We want to know how many weeks and how many days it is. The following formula would return how many weeks and how many days there are in that amount of time.

Days\7 & " Weeks, " & Days MOD 7 & " Days"

If Days is 23 days, then the result would be:

3 Weeks, 2 Days

RELATED POSTS
  • Date Math - DateAdd, ProjDateAdd in Microsoft Project
  • Microsoft Project VBA - the Instr function
  • MS Project Tip - Creating User-defined Filters
  • Microsoft Project Tip - Formulas and the IIF statement
  • Telling Time - ProjDateDiff, VB DateDiff and Application.DateDifference
  • MS Project Tip - Renaming Fields
  • Working with Custom Field Formulas

  • Comments (4)

    Karen Leslie:

    Thanks for this post.

    This is exactly what I was looking for.

    As you guessed I'm not a VBA programmer (used to be an RPG programmer) and I'm creating a spreadsheet for a personal money course and this is what I needed for some calculations.

    Thanks again!

    Regards,
    Karen Leslie

    Tony Biagioli:

    I've programmed for a long time with VBA, but never realized the backslash did integer division. Thanks

    Mike:

    I am a professional programmer but am new to VBA - my background is Java and .NET. I needed to do some integer division and all I needed was the syntax. This was exactly what I needed: thanks a lot!!

    Tatang Hendrolukito:

    Actually, "\" operator is not as simple as doing the regular division and discard the non-integer remainder as you mentioned, you can try this:

    9.5 / 2.4 = 3.95833333333333
    9.5 \ 2.4 = 5

    -- Tatang Hendrolukito

    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

    About

    The previous article is Time is not on my side.

    The next article is Lovely Rita PMP meter maid.

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34