Writing a value in a ByRef Variant argument holding a LongLong

Both the Variant issue you hit earlier and the LongLong constant quirk come from the same underlying reality: VBA’s LongLong support is bolted on top of an older runtime that was never originally designed for 64-bit integers, and the gaps show up in odd places.

Workarounds (which you’ve already listed) are all about storing the value in some other Variant-compatible type and then reconstructing the LongLong.

The "cannot declare MIN_LONGLONG directly" problem

You tried:

Private Const MIN_LONGLONG As LongLong = -9223372036854775808^

and VBA refused. But:

Private Const MIN_LONGLONG As LongLong = -9223372036854775807^ - 1^

is accepted.

BA’s constant parser can’t store 9223372036854775808 as a positive value in any of its literal staging types — it overflows before it even applies the - sign.

Here’s a self-contained VBA module you can maybe paste straight into your project.
It includes:

• Safe constants for MIN_LONGLONG / MAX_LONGLONG without parser overflow
• Full-precision storage helpers for passing LongLong via Variant without losing bits
• Byte-array packing/unpacking for when you must go through a Variant and need the full ±2^63 range

Option Explicit
Option Private Module

'==============================
'   LongLong Safe Constants
'==============================
Public Const MAX_LONGLONG As LongLong = 9223372036854775807^
' Can't write MIN_LONGLONG directly (parser overflow), so compute it:
Public Const MIN_LONGLONG As LongLong = -9223372036854775807^ - 1^

'==============================
'   Store/Read LongLong in Variant (full precision) via Byte()
'==============================

' Packs a LongLong into a Variant(Byte array) without losing precision
Public Function LongLongToVariant(ByVal value As LongLong) As Variant
    Dim b(0 To 7) As Byte
    Dim i As Long
    
    ' Copy the bytes from the LongLong into the byte array
    LSetLongLongToBytes value, b
    LongLongToVariant = b
End Function

' Extracts a LongLong from a Variant(Byte array) created by LongLongToVariant
Public Function VariantToLongLong(ByVal v As Variant) As LongLong
    Dim b() As Byte
    b = v
    VariantToLongLong = LGetBytesToLongLong(b)
End Function

'==============================
'   Low-level byte manipulation
'==============================

Private Sub LSetLongLongToBytes(ByVal ll As LongLong, ByRef b() As Byte)
    Dim tmp As Currency  ' We'll just reuse memory copy trick
    Dim ptrSrc As LongPtr, ptrDst As LongPtr
    
    If LBound(b) <> 0 Or UBound(b) <> 7 Then Err.Raise 5, , "Byte array must be length 8"
    
    ' Use CopyMemory (aka RtlMoveMemory) to move raw bytes
    ptrSrc = VarPtr(ll)
    ptrDst = VarPtr(b(0))
    CopyMemory ByVal ptrDst, ByVal ptrSrc, 8
End Sub

Private Function LGetBytesToLongLong(ByRef b() As Byte) As LongLong
    Dim ll As LongLong
    Dim ptrSrc As LongPtr, ptrDst As LongPtr
    
    If LBound(b) <> 0 Or UBound(b) <> 7 Then Err.Raise 5, , "Byte array must be length 8"
    
    ptrSrc = VarPtr(b(0))
    ptrDst = VarPtr(ll)
    CopyMemory ByVal ptrDst, ByVal ptrSrc, 8
    LGetBytesToLongLong = ll
End Function

'==============================
'   Safe API Declare
'==============================
#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
        (ByVal Destination As LongPtr, ByVal Source As LongPtr, ByVal Length As LongPtr)
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
        (ByVal Destination As Long, ByVal Source As Long, ByVal Length As Long)
#End If

'==============================
'   Example usage
'==============================
Public Sub DemoLongLongVariant()
    Dim original As LongLong
    Dim v As Variant
    Dim recovered As LongLong
    
    original = MIN_LONGLONG
    Debug.Print "Original:", original
    
    ' Store into Variant without losing precision
    v = LongLongToVariant(original)
    
    ' Recover it back
    recovered = VariantToLongLong(v)
    Debug.Print "Recovered:", recovered
    Debug.Print "Equal?:", (original = recovered)
End Sub

This can helps…

No precision loss: Byte-array storage means you can pass any LongLong (±2^63) through a Variant without rounding errors.

Safe constants: Avoids the literal parser overflow problem when declaring MIN_LONGLONG.

Compatible: Works in both 32-bit and 64-bit VBA.

…and additional, here a ready-to-paste VBA module that:

Declares MIN_LONGLONG and MAX_LONGLONG safely,

Provides safe Variant wrappers for full-precision storage,

And shows how to reconstruct LongLong from Byte() without losing speed.

a self-contained VBA module you can paste straight into your project if you like.

Option Explicit
Option Private Module

'==============================
'   LongLong Safe Constants
'==============================
Public Const MAX_LONGLONG As LongLong = 9223372036854775807^
Public Const MIN_LONGLONG As LongLong = -9223372036854775807^ - 1^

'==============================
'   Store/Read LongLong in Variant (full precision) via Byte()
'==============================

' Packs a LongLong into a Variant(Byte array) without losing precision
Public Function LongLongToVariant(ByVal value As LongLong) As Variant
    Dim b(0 To 7) As Byte
    LSetLongLongToBytes value, b
    LongLongToVariant = b
End Function

' Extracts a LongLong from a Variant(Byte array) created by LongLongToVariant
Public Function VariantToLongLong(ByVal v As Variant) As LongLong
    Dim b() As Byte
    b = v
    VariantToLongLong = LGetBytesToLongLong(b)
End Function

'==============================
'   Safe assignment helper
'==============================

' Assigns a LongLong into a ByRef Variant without Error 458
' Stores as Byte() internally so precision is preserved
Public Sub SafeAssignLongLongByRefVariant(ByRef target As Variant, ByVal value As LongLong)
    target = LongLongToVariant(value)
End Sub

' Retrieves a LongLong from a ByRef Variant set by SafeAssignLongLongByRefVariant
Public Function SafeGetLongLongFromByRefVariant(ByRef source As Variant) As LongLong
    SafeGetLongLongFromByRefVariant = VariantToLongLong(source)
End Function

'==============================
'   Low-level byte manipulation
'==============================

Private Sub LSetLongLongToBytes(ByVal ll As LongLong, ByRef b() As Byte)
    Dim ptrSrc As LongPtr, ptrDst As LongPtr
    If LBound(b) <> 0 Or UBound(b) <> 7 Then Err.Raise 5, , "Byte array must be length 8"
    ptrSrc = VarPtr(ll)
    ptrDst = VarPtr(b(0))
    CopyMemory ByVal ptrDst, ByVal ptrSrc, 8
End Sub

Private Function LGetBytesToLongLong(ByRef b() As Byte) As LongLong
    Dim ll As LongLong
    Dim ptrSrc As LongPtr, ptrDst As LongPtr
    If LBound(b) <> 0 Or UBound(b) <> 7 Then Err.Raise 5, , "Byte array must be length 8"
    ptrSrc = VarPtr(b(0))
    ptrDst = VarPtr(ll)
    CopyMemory ByVal ptrDst, ByVal ptrSrc, 8
    LGetBytesToLongLong = ll
End Function

'==============================
'   Safe API Declare
'==============================
#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
        (ByVal Destination As LongPtr, ByVal Source As LongPtr, ByVal Length As LongPtr)
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
        (ByVal Destination As Long, ByVal Source As Long, ByVal Length As Long)
#End If

'==============================
'   Example usage
'==============================
Public Sub DemoSafeAssign()
    Dim v As Variant
    Dim original As LongLong
    Dim recovered As LongLong
    
    original = MIN_LONGLONG
    Debug.Print "Original:", original
    
    ' Assign LongLong into ByRef Variant without error or precision loss
    SafeAssignLongLongByRefVariant v, original
    
    ' Retrieve it back
    recovered = SafeGetLongLongFromByRefVariant(v)
    
    Debug.Print "Recovered:", recovered
    Debug.Print "Equal?:", (original = recovered)
End Sub

No Error 458

No Double precision loss

Works with any valid LongLong, including MIN_LONGLONG

Hope it helps 🙂