14 thoughts on “Minimum and maximum values of numeric data types”

1. Hi Tushar –

   Is it correct that currency is an integer data type? Works to 4 decimal places, but the same digits as LongLong.

   I think this is a terminology issue: The MinDatatype() is less than the SmallestDatatype(). Isn’t the smallest data type rather the most precise?

   … mrt

2. Interesting: and your ‘Smallest’ functions are quite useful – the max and min are rarely encountered, other than as errors; but there are real-world situations where you need to know the smallest value that your type-specific arithmetic can distinguish from zero.

   I would caution anyone that this is not the same as ’rounding error’ – which should cancel out, in a properly-implemented rounding scheme, and usually it does – but floating-point values are an estimate, not a definite digital value like an integer; and the floating-point precision errors can be directional and cumulative, no matter what you read in the compiler docs.

   A real-world example of when you’d want to know that is a long-running ‘sliding window’ calculation: take a data sample from a time series, perform your calculations, then do the next calculation in your sequence by dropping the data for the start date and appending new values for End Date + 1. This saves the delay of repeatedly requerying the entire data set; but, over a day’s worth of sequential calculations on millisecond ‘tick’ data, the approximations due to the inherent imprecision of floating-point arithmetic will accumulate and introduce a significant error. I’ve seen brilliant-but-misguided analysts with PhD’s in mathematics run face-first into that error, insisting on using Single-Precision Floats ‘for performance’ and assuming that the approximations would all cancel out. They don’t, in least-squares regression sequences – and won’t, unless your calculation scheme is very carefully designed.

   So estimating your boundaries using a ‘smallest’ value is a useful check on how many loops you can perform before your Floating-point drift becomes significant, and an upper boundary on how far you can go in optimising for speed at the expense of precision.

3. For the non-variant numeric types, wouldn’t module-level public constant definitions like

   Public Const ByteMax As Byte = 255
   Public Const ByteMin As Byte = 0

   impose less processing and memory overhead than function calls?

4. >The documentation incorrectly indicates the largest double is 1.79769313486232E308

   …Actually the documentation is correct but the result is rounded to 15sf, The maximum value is ?[2^1023*(2-2^-52)]. This highlights the need for more than 15sf in specifying values, in this case: 1.7976931348623157E308. Note also that VBA uses subnormal numbers for the lower limits but Excel rounds these to zero.

5. Michael:

   The definition of the currency data type is a 64 bit integer scaled by 10,000. Hence, the integer data type classification.

   Precision, according to wikipedia (http://en.wikipedia.org/wiki/Significand) is the number of bits in the number. Consequently, for a double it is 53. Maybe, what I called ‘smallest’ might be better called ‘resolution?’ It might fit better with what Nigel describes.

   fzz:

   Yes, that’s possible. I took some of the initial functions from a class module and VBA does not allow public constants in a class module.

   Another problem with the use of a const is that VBE does unexpected and unnecessary rounding (VBA7, Office 2010 32bit). E.g.,

   public const xx as currency=922337203685477.5807

   becomes

   Public Const XX As Currency = 922337203685478#

   lhm:

   Thanks for the explanation of what could be. Of course, from a practical perspective, since 1.79769313486232E308 in a VBA double raises an overflow error, there’s little choice but to consider the largest value that one can have in a VBA double as 1.79769313486231E308.

6. @Tushar,

   You can use the @ postfix character to force the assigned number to remain a Currency value…

   Public Const xx As Currency = 922337203685477.5807@

7. That’s interesting, Rick. I almost never use the type declaration character so I didn’t think of trying it.

   Apparently, the VBE folks also don’t much use it (much). {grin}

   As you indicated it works for the maximum currency value. But with the minimum value

   Public Const XX As Currency =-922337203685477.5808@

   the result is “Compile error: Expected: expression” and the editor selects the part after the minus sign, i.e., 922337203685477.5808@

   So, apparently, the editor/compiler is written to first evaluate 922337203685477.5808@ and then get the negative of that value. Unfortunately, the first step yields an unacceptable number. ;-)

8. @tushar: I think you misunderstood me as perhaps I wasn’t clear enough. My point is you should specify doubles with 17 significant figures otherwise you will not be storing the maximum value. VBA will accept the longer number I posted and stores this value internally, you can compare against visual studio which does display the extra digits.

9. @Tushar,

   The fact that Public Const XX As Currency =-922337203685477.5808@ resulted in a Compile Error really surprises me. However, as is usually the case in VB, there is a way to do this with public constants. Put the following in a Standard Module and run the Test subroutine…

   Public Const CurrencyMax As Currency = 922337203685477.5807@
   Public Const CurrencyMin As Currency = -CurrencyMax - 0.0001@
   
   Sub Test()
     MsgBox CurrencyMin & " <= AnyCurrencyNumber <= " & CurrencyMax
   End Sub
   

   Of course, I have no idea what this should work and using the number directly does not... that one has me scratching my head. Also, the code was tested on XL2003 and XL2007 (which contain VBA Versions 6), but I do not have XL2010 install on my computer, so I do not know if it works in that versions VBA Version 7.

   Oh, by the way, the @ symbol at the end of the 0.0001 number is not needed as the code works fine without it, but if I was doing it this way, I would feel more comfortable using it so that I did not have to rely on VB to coerce the value from a double (which is how I think VB sees it) to a Currency data type behind the scenes.

10. @Tushar,

    Oh, you are going to love this. After I posted my last message, I got to thinking about the way VB coerces values as the need arises. Well, as it turns out, it will coerce a String value to a Currency data type with on trouble. Check this out…

    Public Const CurrencyMax As Currency = "922337203685477.5807"
    Public Const CurrencyMin As Currency = "-922337203685477.5808"
    
    Sub Test()
      MsgBox CurrencyMin & " <= AnyCurrencyNumber <= " & CurrencyMax
    End Sub
    

    Interestingly enough, the above String method did not work for the Double data type and the value discussed earlier.

11. Rick: That’s ingenious. There’s some use for VBA implicit type coercion. {grin}

    I used it to see what happens with lhm’s suggestion. Clearly VBA does “keep” the additional digits around and they appear after the first and second set of subtractions in the below code. Then, the third set of subtractions shows errors in the least significant digits in the significand.

    Option Explicit
    Public Const AA As Double = “1.7976931348623157E308”

    Sub doShow()
    Debug.Print “1.7976931348623157E308” & vbNewLine _
    & AA & vbNewLine _
    & (AA – 1E+308) & vbNewLine _
    & (AA – 1E+308 – 7E+307) & vbNewLine _
    & (AA – 1E+308 – 7E+307 – 9E+306)
    End Sub

    The results are
    1.7976931348623157E308
    1.79769313486232E+308
    7.97693134862316E+307
    9.76931348623157E+306
    7.69313486231567E+305

12. > Rick: There’s some use for VBA implicit type coercion. {grin}

    Yeah, who would have thought… ;-)

    > I used it to see what happens with lhm’s suggestion. Clearly VBA does “keep”
    > the additional digits around and they appear after the first and second set
    > of subtractions in the below code. Then, the third set of subtractions shows
    > errors in the least significant digits in the significand.
    >
    > Public Const AA As Double = “1.7976931348623157E308”

    Yes, that does seem to allow you to pump in more precision, but the reason I wrote my comment that started with “Interestingly enough…” was that you cannot do this directly (it produces an overflow error)…

    Public Const DoubleMax As Double = “1.79769313486232E308”

13. Good point – as you say the longer number would either need to be entered as a string or else as the result of an expression.

14. Hi Tushar –

    Thanks for the successful explanation.
    However, there is one thing that has not been considered in this thread: the locale.
    In a German locale, DecimalSeparator and ThousandsSeparator characters are swapped, so using constants can produce unexpected results.
    This can be taken into account much more easily using a function.

    Example:

    Private Function MinCurrency() As Currency
    Dim currencyValue As String
    currencyValue = “-922,337,203,685,477.5808”
    currencyValue = Replace(Split(currencyValue, “.”)(0), “,”, Application.ThousandsSeparator) & _
    Application.DecimalSeparator & Split(currencyValue, “.”)(1)
    MinCurrency = CCur(currencyValue)
    End Function

    Private Function MaxCurrency() As Currency
    Dim currencyValue As String
    currencyValue = “922,337,203,685,477.5807”
    currencyValue = Replace(Split(currencyValue, “.”)(0), “,”, Application.ThousandsSeparator) & _
    Application.DecimalSeparator & Split(currencyValue, “.”)(1)
    MaxCurrency = CCur(currencyValue)
    End Function

    Private Function MaxDecimal(ByVal NbrDecimals As Byte) As Variant
    ‘There is a bug in the code that handles decimal division so that _
    even when the resulting value fits into a decimal data type, _
    the software rounds the last digit. Hence, the correction below.
    Dim decimalValue As String
    decimalValue = “79,228,162,514,264,337,593,543,950,335”
    decimalValue = Replace(decimalValue, “,”, Application.ThousandsSeparator)
    MaxDecimal = CDec(decimalValue)
    If NbrDecimals > 0 Then
    MaxDecimal = MaxDecimal / CDec(10) ^ CDec(NbrDecimals)
    Dim Correction
    Correction = CDec(0.5) / CDec(10) ^ (CDec(NbrDecimals) – 1)
    MaxDecimal = MaxDecimal – Correction
    End If
    End Function

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.