Minimum and Maximum values of different data types

There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type.  Some programming languages have built-in support through names like MaxInt.  VBA, unfortunately, is not one of them.

I decided to “translate” the documentation defining the different data types into code.  The functions Max{datatype}, Min{datatype}, and Smallest{datatype} return the appropriate value.  The Max and Min functions should be self-evident.  The Smallest function returns the smallest non-zero value supported by the data type.  Since this is simply 1 for all data types associated with integers (Byte, Integer, Long, LongPtr, LongLong, and Currency), Smallest is defined only for data types associated with real numbers (Single, Double, and Decimal).

In cases where the documentation is incorrect, the code implements the correct limits (as in the case of the Double datatype).  In cases where the software fails to return accurate results, the code makes the necessary correction (as in the case of the Decimal datatype).

The use of compile time constants ensures that the code will work correctly on versions of Office from Office 97 to Office 2010.  In the case of Office 2010, it will work on both 32-bit and 64-bit installations.

Option Explicit

 

Function MaxByte() As Byte: MaxByte = 255: End Function

Function MinByte() As Byte: MinByte = 0: End Function

 

Function MaxInteger() As Integer: MaxInteger = 32767: End Function

Function MinInteger() As Integer: MinInteger = -32768: End Function

 

Function MaxLong() As Long: MaxLong = 2147483647: End Function

Function MinLong() As Long: MinLong = -2147483648#: End Function

 

#If Win64 Then

Function MaxLongLong() As LongLong

    MaxLongLong = CLngLng("9,223,372,036,854,775,807")

    End Function

Function MinLongLong() As LongLong

    MinLongLong = CLngLng("-9,223,372,036,854,775,808")

    End Function

    #End If

 

#If VBA7 Then

Function MaxLongPtr() As LongPtr

    MaxLongPtr = CLngPtr("&H7F" & String((Len(MaxLongPtr) - 1) * 2, "F"))

    End Function

Function MinLongPtr() As LongPtr

    MinLongPtr = CLngPtr("&H80" & String((Len(MinLongPtr) - 1) * 2, "0"))

    End Function

    #End If

 

Function MinSingle() As Single: MinSingle = -3.402823E+38: End Function

Function MaxSingle() As Single: MaxSingle = 3.402823E+38: End Function

Function SmallestSingle() As Single

    SmallestSingle = 1.401298E-45

    End Function

 

Function MinDouble() As Double

    MinDouble = -1.79769313486231E+308

    End Function

Function MaxDouble() As Double

    MaxDouble = 1.79769313486231E+308

    End Function

    'The documentation incorrectly indicates the largest double _

     is 1.79769313486232E308

Function SmallestDouble() As Double

    SmallestDouble = 4.94065645841247E-324

    End Function

 

Function MinCurrency() As Currency

    MinCurrency = CCur("-922,337,203,685,477.5808 ")

    End Function

Function MaxCurrency() As Currency

    MaxCurrency = CCur("922,337,203,685,477.5807")

    End Function

 

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.

    MaxDecimal = CDec("79,228,162,514,264,337,593,543,950,335")

    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

Function MinDecimal(ByVal NbrDecimals As Byte) As Variant

    MinDecimal = -MaxDecimal(NbrDecimals)

    End Function

Function SmallestDecimal()

    SmallestDecimal = CDec("0.0000000000000000000000000001")

    End Function

Sub testMinMax()

    Debug.Print "Byte: " & MinByte & ", " & MaxByte

    Debug.Print "Integer: " & MinInteger & ", " & MaxInteger

    Debug.Print "Long: " & MinLong & ", " & MaxLong

    #If VBA7 Then

    Debug.Print "LongPtr: " & MinLongPtr & ", " & MaxLongPtr

        #End If

    #If Win64 Then

    Debug.Print "LongLong: " & MinLongLong & ", " & MaxLongLong

        #End If

    Debug.Print "Single: " & MinSingle & ", " & MaxSingle _

        & ", " & SmallestSingle()

    Debug.Print "Double: " & MinDouble & " " & MaxDouble _

        & ", " & SmallestDouble()

    Debug.Print "Currency: " & MinCurrency & " " & MaxCurrency

    Debug.Print "Decimal:" & SmallestDecimal

    Dim I As Byte

    For I = 0 To 28

        Debug.Print Format(I, "00") & ", " & CStr(MinDecimal(I)) _

            & ", " & CStr(MaxDecimal(I))

        Next I

    End Sub