Error 6 Overflow

[boileauj]

I am new to variables and I am having trouble naming the correct types to work with the formulas that I am making.

My code is as follow:

Sub HMG_Value()

Dim varAverage As Variant 'Average
Dim varAveDev As Variant 'Average Deviation
Dim dbHMG As Double 'Homogeneity %

varAveDev = Range("C61").Value
varAverage = Range("C62").Value

dbHMG = (1 - (varAveDev / varAverage)) * 100

Range("C52").Value = Format(dbHMG, "000.00")

MsgBox "The Homogeneity of the Batch Model before Redistribution is:" & vbCrLf & vbCrLf & _
" " & Format(dbHMG / 100, ".00%"), vbOKOnly, "Homogeneity Results"

End Sub

Can anyone tell me the correct data types to use so that I do not get the overflow message?

 

[Zack Barresse]

Hello there,

Sounds like you might just be coming up against the precision limit of Excel/VBA. You might want to think about keeping the precision down (to 15 or less characters). Since it's a run-time error, you know it's because of your values (as opposed to a compile overflow error, which is different). A Double data type should be sufficient for most things. But we don't know anything about your values, so we can't really say much besides keep the number of digits down to <15.

HTH