unexpected result when looping a select case statement RRS feed

  • Question

  • I have written a UDF to display a label for the next scheduled service for a vehicle.  The assumption here is that a vehicle is serviced every 6,000 miles.

    Here is the module code:

    Function NextService(CurrentMileage As Integer)
    Dim n As Integer, interval As Integer
    n = 0
    interval = 6000
    Do While n <= 150000
      Select Case CurrentMileage
        Case Is <= n
          NextService = n: Exit Function
      End Select
     n = n + interval
    End Function
    Current Mileage Next Service Result
    6000 6000
    29999 30000
    30000 30000
    30001 #VALUE!
    36000 #NUM!

    The above table displays the results of the UDF.  Does anyone have any ideas about why it is returning the #VALUE! error once the current mileage value reaches 30,001?  How about why it's returning #NUM! when current mileage is 36,000?


    Thursday, August 18, 2011 8:00 PM


  • You have declared your variables as Integers which have  a range of -32768 to +32767.  So when n>32767, you get the #VALUE! error.  However, when your Input (CurrentMileage) is > 32767, you get the #NUM! error.

    Declare your variables as Type Long and you should be OK.  There is rarely any reason to use the Integer type vs the Long Type.


    • Marked as answer by Knot Friday, August 19, 2011 2:05 AM
    Friday, August 19, 2011 12:12 AM