VBA: Unexpected Behaviour of Variant Data Type -

as per msdn, variant data types:

“numeric data can integer or real number value ranging -1.797693134862315e308 -4.94066e-324 negative values , 4.94066e-324 1.797693134862315e308 positive values.”

however, following code gives error though final values after computation fall within acceptable range:

sub test()  dim v1, v2, v3, v4  v1 = 569847501 + 54678                  '  okay v2 = 7784687414# + 98565821345#         '  okay v3 = 7784687414# + 1132747441           '  okay v4 = 1132747441 + 1788441323            '  fails  end sub 

msdn points out:

” however, if arithmetic operation performed on variant containing byte, integer, long, or single, , result exceeds normal range original data type, result promoted within variant next larger data type. byte promoted integer, integer promoted long, , long , single promoted double.”

the documentation states type should promoted when arithmetic operation exceeds normal range original data type. why isn’t v4 promoted double?

you're working numeric literals, aren't variants. they're interpreted compiler smallest necessary type accommodate literal value, although byte values default type integer.

debug.print typename(1132747441) 'long debug.print typename(1788441323) 'long 

as @comintern points out, you're assigning result of 2 longs in expression, expression overflows before assigned variant v4

as @dazedandconfused pointed out, can coerce literal values more suitable type, , expression evaluate, , variant can assigned.

in order behavior microsoft documents variant type, need coerce literal values variant, before using them in expression. both variants contain longs, you'll auto re-typing documentation asserts.

sub test()    dim v1, v2, v3, v4    debug.print typename(1132747441) 'long   debug.print typename(1788441323) 'long    dim v5, v6    v5 = 1132747441   v6 = 1788441323    debug.print typename(v5) 'long   debug.print typename(v6) 'long    v4 = v5 + v6 'okay   debug.print typename(v4) 'double    v4 = 0   debug.print typename(v4) 'integer    v4 = cvar(1132747441) + cvar(1788441323) ' okay   debug.print typename(v4) 'double     v1 = 569847501 + 54678                  '  okay   v2 = 7784687414# + 98565821345#         '  okay   v3 = 7784687414# + 1132747441           '  okay   v4 = 1132747441 + 1788441323            '  fails  end sub