Home > Publications & Training > Case Studies > Maximum and minimum numeric values Share Your

# 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