What is the largest possible decimal value in VBA?

Question

I've been trying to create something similar to a DEC_MAX constant in vba.

---

Issue is, it is a bit tricky, because there is no Decimaldata-type!
The closest you can get to a functioning decimal is the CDec() function which is defined:

Return the Decimal data value that is the result of Expression being Let-coerced to Decimal

So naturally, I thought that any potentially overfowing value would be co-erced to the maximum achievable Decimal. I tried inserting the max Decimal vb.net value from MSDN Documentation

This is however note true, as attempting to do so will result in an Overflow:

So how would one go about calculating the closest possible approximation of Decimal maximum here? I tried this "computer-bricking" ugly loop of a code:

Private Sub brick_my_Excel()
  On Error Resume Next
  x = 79228162514264337593543950335 'let's let it auto-coerce i guess
  Do 
     Debug.Print(x)
     x = x - 1
  Loop
End Sub

This however supresses the overflow altogether, printing the x in almost string-like fashion without paying much attention to the calculation.

So,

1. How would one go about calculating it?
2. What is the largest possible expression we can pass to the CDec() function?

Answer 1

The only way I can figure out how to do this is to completely bypass VBA and "build" the maximum value in memory. The DECIMAL structure is 16 bytes and is defined as:

typedef struct tagDEC {
  USHORT    wReserved;
  BYTE      scale;
  BYTE      sign;
  ULONG     Hi32;
  ULONGLONG Lo64;
} DECIMAL;

Since you can't explicitly declare a Decimal in VBA, CDec(0) will give you one to play around with that has the correct Variant type. The sign and scale are independent of the 12 byte value, so just setting all the bits in that area of memory will give you the max value (the max will have a scale of 0):

#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
        "RtlMoveMemory" (Destination As LongPtr, Source As Any, _
        ByVal length As Long)
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias _
        "RtlMoveMemory" (Destination As Long, Source As Any, _
        ByVal length As Long)
#End If

Private Const VT_DECIMAL As Integer = &HE
Private Const BIT_MASK As Long = &HFFFFFFFF
Private Const DATA_OFFSET = 4
Private Const SIZEOF_LONG = 4

Public Function MaxDecimal() As Variant
    'Get a decimal to work with.
    Dim dec As Variant
    dec = CDec(0)

    Dim vtype As Integer
    'First 2 bytes are the VARENUM.
    CopyMemory ByVal VarPtr(vtype), ByVal VarPtr(dec), LenB(vtype)

    'Make sure the VARENUM is a VT_DECIMAL.
    If vtype = VT_DECIMAL Then
        'Fill the top 12 bytes of it's data area with truthy bits
        CopyMemory ByVal VarPtr(dec) + DATA_OFFSET, BIT_MASK, SIZEOF_LONG
        CopyMemory ByVal VarPtr(dec) + DATA_OFFSET + SIZEOF_LONG, BIT_MASK, SIZEOF_LONG
        CopyMemory ByVal VarPtr(dec) + DATA_OFFSET + SIZEOF_LONG * 2, BIT_MASK, SIZEOF_LONG
    End If

    MaxDecimal = dec
End Function

Note that this is not obviously not going to get it into a Const for you, but it does get you the correct maximum value:

Public Sub Test()
    MsgBox MaxDecimal
End Sub

Answer 2

I'm not sure what the question is because there was a lot of intentions/questions put forward:

What is the largest possible decimal value in VBA?

As you know: 79228162514264337593543950335

I've been trying to create something similar to a DEC_MAX constant in vba.

You won't get the Variant into a constant, but a String might do for you?

How would one go about calculating it?

Like :

Public Const MAX_DEC_STRING = "79228162514264337593543950335"

Public Function MAX_DEC() As Variant
    MAX_DEC = CDec(MAX_DEC_STRING)
End Function

Sub test()
    Dim v As Variant, x As Variant
    v = MAX_DEC
    x = CDec("79228162514264337593543950334")   '7922816251426433759354395033

   MsgBox v - x
End Sub

What is the largest possible expression we can pass to the CDec() function?

As Above

Answer 3

I concure with 79228162514264337593543950335 as being the largest number and confirm you have to pass it as a string to CDec(). Perhaps the practical limit for many coding situtions is the slightly smaller 9,999,999,999,999,999,999,999,999,999 that's (28) 9's "9 octillion" or "9 billion billion billion" and this is the reason I had for posting an answer so you might consider stopping here in your code for clarity.

?cdec("9999999999999999999999999999")

BONUS: I have a couple of functions that use CDec() for making base conversions using custom character strings, a sort of simple cypher that can be easily reversed if you know the custom string.

?ass("My Secret",,true) '9 Characters max for CDec()

77|121|32|83|101|99|114|101|116

?i2x("6077121032083101099114101116",,,,"03689aåcos©")

a30©o©s3©©å9s080ca9åsa880©o