Adam Heeg Adam Heeg - 1 year ago 102 Question

Is there existing Documentation about why the VBA Val function behaves differently than .Net implementation of same code (hex conversions)?

I'm converting code from VBA and I need to confirmed proof about the behavior of the Val function in order to faithfully reproduce it in .Net.

The issue is this line of VBA code

lHexNum = Val("&h" & HexNum) ' HexNum = 3B05000004F137

Is producing this output


Which should be this,


but I don't know why it isnt.

I have used 2 methods in .Net which both confirm the expected output of 16612521184391480 (as well as using a simple hex calculator).

Convert.ToInt64(HexNum, 16);


Microsoft.VisualBasic.Conversion.Val("&h" + HexNum);

However, I still need to perfectly replicate the actual output from the VBA program which right now gives the 323895 output.

The only reasoning I can find is if I remove the 3B05 from the HexNum I then get matching output. Since I cannot test this against enough live data to be 100% sure this works in all cases I cannot use this hack.

Does anyone have references or more information on how and why an Access 2003 application is getting the 323895 output from the Val function and why even the matching Microsoft.VisualBasic.Conversion.Val method cannot get the same output?

Answer Source

Val() returns a Double. Assuming lHexNum is declared as a 32 bit Long, VBA will do an implicit conversion and it doesn't throw an error even if it overflows. Since VBA doesn't have a 64 bit integer data type, it just throws away the upper bytes.

The same is true for VB6, which I verified below returns the value you expected as 323895.

Dim HexNum As String
HexNum = "3B05000004F137"
Dim lHexNum As Long
lHexNum = Val("&h" & HexNum)
Debug.Print lHexNum

In .NET however, a Long is a 64 bit value. It is able to hold the entire hex value so nothing gets thrown away. Technically, this is more correct than what VBA is doing since you are losing some of your original data during the conversion with VBA. You can't just change your variable to a Int32 either because C# will throw an overflow exception if the value is too large at runtime.

If you want the same behavior as VBA/VB6, you need to first cast the Double to an Int64, then cast it back to an Int32 so it gets truncated. Like this:

        lHexNum = (Int32)(Int64)(Microsoft.VisualBasic.Conversion.Val("&h" + HexNum));

The result is that the upper 32 bits of the Int64 get thrown away, and you end up with the 323895 you desire.

I am using the Int64 and Int32 data types to be more explicit, however you could also use int in place of Int32, and long in place of Int64.