I just want to know the reason for the run-time error: Overflow when running these VBA codes

    Function target_time(Time) As Double

       target_time = 1200 * 60 * Time

    End Function

    Sub Test()
       x = target_time(24)
       MsgBox x
    End Sub

I already addressed this issue by rearranging the function formula into:

    target_time = Time*1200 * 60

I just want to know why is repositioning the variable causes the runtime error overflow

CodePudding user response：

Here's what VBA does:

• 1200 and 60 <-- Those are small enough to be a (16-bit) Integer, so I'll assume they are 16-bit Integer constants.
• 1200 * 60 <-- A multiplication of two 16-bit Integers yields an Integer - there is no automatic promotion to a 32-bit Long. Since 1200 * 60 = 72000 exceeds the maximum value of a 16-bit integer, the expression overflows.

On the other hand, Time * 1200 is not a multiplication of two 16-bit Integers, it's a multiplication of a "Variant containing an Integer" with an Integer. In this case, the result is automatically promoted to a "Variant containing a Long":

Dim v As Variant

v = 1200
Debug.Print TypeName(v)            ' prints Integer
Debug.Print TypeName(v * 60)       ' prints Long
Debug.Print TypeName(v * 6000000)  ' prints Double(!)

The reason for this can be found in section 5.6.9.3 of the VBA spec:

If one or both operands have a declared type of Variant:

• If the operator’s effective value type is Integer, Long, Single or Double, the operator’s value type is the narrowest type of either Integer, Long or Double such that the operator value is within the valid range of the type. If the result does not fit within Double, runtime error 6 (Overflow) is raised.

CodePudding user response：

As an additon to Heinzi's answer which addresses the OP's question I would like to add that I would add some of the following operators: CLng, CDbl to make sure that the result will be correct (or better closer to correct :-) )

Instead of target_time = 1200 * 60 * Time use target_time = CDbl(1200) * CDbl(60) * CDbl(Time).