Long Data Type Not Storing Long Value in Excel VBA

Question

When I execute the code below in excel VBA the first message box returns the long value correctly but when I store that value in a long data type and messagebox the long variable it returns 0 (?!).

Sub Test()

Dim lFileCount As Long
Dim lAdder As Long

lFileCount = 1

MsgBox 0.35! + (0.15! / lFileCount)

lAdder = 0.35! + (0.15! / lFileCount)
MsgBox lAdder
MsgBox Format(lAdder, "0.0")

End Sub

The reason for the exclamation points is due to the longs overflowing in the project I extrapolated the code from. If I remove them it doesn't change the result. I have no idea what could be causing this, my first thought is it has something to do with how the variable stores according to the order of operations, but I have no working solution.

I'm perplexed, any help would be greatly appreciated!

Answer 1

Long is a whole number.

I changed lAdder to Variant to collect a decimal value:

Sub Test()
    Dim lFileCount As Long
    Dim lAdder As Variant
    lFileCount = 1
    MsgBox 0.35 + (0.15 / lFileCount)
    lAdder = 0.35 + (0.15 / lFileCount)
    MsgBox lAdder
    MsgBox Format(lAdder, "0.0")
End Sub

---

Looks like my usage of As Variant was not appropriate, as shown in the comments to this answer. As Double should be used for decimals:

 Sub Test()
    Dim lFileCount As Long
    Dim lAdder As Double
    lFileCount = 1
    MsgBox 0.35 + (0.15 / lFileCount)
    lAdder = 0.35 + (0.15 / lFileCount)
    MsgBox lAdder
    MsgBox Format(lAdder, "0.0")
End Sub