none
Mod function in VBA RRS feed

  • Question

  • I think I have a faulty understanding of how the MOD function is used with decimal values.

    The practical requirement I have to enforce is a time entry application where a user inputs on a form text box the amount of time they spent on an activity.  Decimal hours are permitted, but the smallest level of precision is supposed to be a quarter hour (0.25 hours).  So the user can say he spent .25 hours on something, or 3 hours, or 4.75 hours.  In each of these examples if I take the input and divide by .25, there is no remainder.  that is, .25 / .25 = 1    3 / .25 = 12    4.75 / .25 = 19.

    Given the requirement for this application, the following input would NOT be allowed:   .6    4.2   5.002  In each of these cases, if I divide by .25 the result would not be even -- there would be a remainder, and that's what I'm trying to detect in VBA code.  Strangely, when I do something like this     RETURN_VAL = 4.2 MOD .25 , I get a divide by zero error.

    Does anybody know how to meet my requirement so I can detect an input value from a form that is not divisible by .25 ?

    Tuesday, March 6, 2018 9:30 PM

Answers

  • Unlike the worksheet function MOD in Excel, the Mod operator in VBA works with whole numbers only. Both operands are converted to whole numbers before the remainder is computed.

    You could check whether

    Int(4 * [TimeWorked]) = 4 * [TimeWorked]

    where TimeWorked is the time in decimal hours.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 6, 2018 10:13 PM

All replies

  • Unlike the worksheet function MOD in Excel, the Mod operator in VBA works with whole numbers only. Both operands are converted to whole numbers before the remainder is computed.

    You could check whether

    Int(4 * [TimeWorked]) = 4 * [TimeWorked]

    where TimeWorked is the time in decimal hours.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 6, 2018 10:13 PM
  • Hans,

    Many thanks for your prompt response.  You not only provided me with a good solution, but also helped me understand why I was getting the divide by 0 error. 

    Regards,

    Rich

    Wednesday, March 7, 2018 1:42 PM