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

Question

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

323895

Which should be this,

16612521184391480

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);

and

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?

Your first line of code works fine with me: Console.WriteLine(CLng(Val("&h3B05000004F137"))) The only reason I can see is that your value is inserted in a int32 instead of a int64, which would produce the value you are getting. Somewhere in your code, that conversion is done. — the_lotus, Jul 05 '16 at 15:29
What is `lHexNum` defined as in the VBA code? The `Val` function returns a double — Matt Wilko, Jul 05 '16 at 15:31
@AdamHeeg you'll need a [`LongLong`](https://msdn.microsoft.com/en-us/library/office/gg278463.aspx) to store a 64-bit integer value in whole. That data type only exists in 64-bit VBA hosts (regardless of the OS' bitness, unlike MSDN seems to imply). I believe the proper conversion function to use would be `CLngLng`, not `Val`. But Access 2003 won't have that... so good luck! — Mathieu Guindon, Jul 05 '16 at 15:35
Val() (in Microsoft.VisualBasic) is supported by VB.Net and it returns the "correct" answer, no need to reinvent the wheel. "Proof" is going to be difficult no matter which way you go. — rheitzman, Jul 05 '16 at 19:32

Marc Gravell · Answer 1 · 2016-07-05T15:32:18.470

3

Well, 323895 is (in hex) 0004F137, so as a complete guess the problem here could be that Val you are using (or: the place where you are storing the value) is 32-bit, and is thus only going to give you the values from the last 8 characters (the last 4 bytes of data)

edited Jul 05 '16 at 15:32

answered Jul 05 '16 at 15:27

Marc Gravell

1,026,079
266
2,566
2,900

that is a good technical explanation of what I'm seeing. Is it documented anywhere? – Adam Heeg Jul 05 '16 at 15:32
@AdamHeeg not anywhere I can see, but since you're talking about Access, is it possible that you're actually storing the value into a 4-byte location, and thus losing the extra bytes that way? – Marc Gravell Jul 05 '16 at 15:32
3

Long in VBA = 32-bit, Long in VB.NET = 64-bit. Progress. – Hans Passant Jul 05 '16 at 15:34
@HansPassant wow, I'd forgotten how bad it was... my VB6 days are so far behind me that I'd erased the pain – Marc Gravell Jul 05 '16 at 15:39
@MarcGravell It seems backwards to me that it takes the last 8 hex numbers instead of the first 8. Why is that? – Adam Heeg Jul 05 '16 at 15:48
1

@AdamHeeg since Val returns a double, the problem isn't with that function. The problem is with the data type you are storing the value in. What you are looking for is the documentation of the data type between [.net](https://msdn.microsoft.com/en-us/library/y595sc15.aspx) and [vba](https://msdn.microsoft.com/en-us/library/9e7a57cf(v=vs.84).aspx). – the_lotus Jul 05 '16 at 15:48

score 2 · Accepted Answer · answered Jul 05 '16 at 16:34

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.

score 0 · Answer 3 · edited May 23 '17 at 12:16

You state that lHexNum is a Long in VBA. This is 32 bits, so the max value that can be stored is 2,147,483,647 or 0x7FFFFFFF - this means your 0x3B05000004F137 is being truncated in the VBA code.

In .NET a Long is 64 bits so the hex value can fit and no truncation happens.

In order to get the same behaviour in .Net you will need to mask off the top 32 bits: see I want to get the low 32 bit of a int64 as int32

e.g.

    Dim HexNumString = "3B05000004F137"
    Dim lHexNum As Long = CLng(Val("&h" & HexNumString))

    Dim tempLong As Long = ((lHexNum >> 32) << 32) 'shift it right Then left 32 bits, which zeroes the lower half Of the Long
    Dim hexInt As Integer = CInt(lHexNum - tempLong)
    Debug.WriteLine(hexInt)

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

3 Answers3