Microsoft Excel Help #VALUE! (asking for it not offering it)

The weather continues to be cold and rainy so I have not spent much time in the freezing cold garage working on my table. To keep myself occupied in the warmth of the house I have been playing around with Excel again... I know these posts about math, Excel and cryptography stuff are boring but it's my Blog so I'm going to write about them! Recently I was trying to do something that I thought would be pretty simple and straightforward - take a value in a cell that is 32 bits long and break it into four 8 bit (byte) sized pieces. As I am sure I have mentioned before I am not all that great of a programmer and I kind of hack my code to get it to work but this seemed simple enough for even me to do it. After all, I have done this sort of thing in the past with 16 bit values, splitting them into 2 bye numbers so I thought that doing it with a 32 bit value would be easy, just the same thing but more of it. Here is a piece of Excel Visual Basic Code that I tried (it didn't work):

  

Function BYTESPLIT(Var As Double, Place As Byte)
' Parse out bytes of values from a 32 bit number
' December 2010 http://ottobelden.blogspot.com
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
A = Int(Var / 16777216)
B = Int(Var / 65536)
C = Int(Var / 256)
D = Var And 255

  

OK it seems straight forward enough doesn't it? This is how I have done this in the past with smaller numbers and it has worked. In essence, get the number that you want to split into bytes, in this case Var, divide the value by 2^X where X is the number of bits that you want to shift to the right. Shifting to the right makes the upper 8 bits turn into the lower 8 and is the byte value that I want. Once shifted assign that to a new variable A B C or D in this case, output those values and you are done. Because this is a 32 bit value that I am interested in I'm using 2^24 = 16777216 to shift Var to the right 24 bits and assign that value to A. This will take the upper 8 bits, shift them all the way down to the right and give me the value. I'm using 2^16 = 65536 to shift 16 bits and 2^8 = 256 to shift 8 bits. Finally for the last value which would be the lower 8 bits of the 32 bit number I am performing a logical And function with the value of  255 (eight 1's) which should just clear the upper 24 bits leaving just the lower 8 assigned to the variable D. None of this is anything that I came up with, this is all pretty straight forward programming. I have done this exact thing before with 16 bit numbers and it has worked great....

  

Here is the problem, this all works fine until the upper most 32nd bit (or Most Significant Bit) is a 1. When it's a one A, B and C all work fine but D is messed up and I get the #VALUE! error in the spreadsheet. Again I am not a programmer but here is what I think is happening. (If you are a programmer please let me know what I am doing wrong!!!)

  

The Double data type in Excel is a signed floating point data type that is 8 Bytes long. A signed number means that the left most bit in there is for a +/- sign and the remaining bits are for the number itself. This is where my problem is (I think). If I put a 32 bit number in the spreadsheet like 3235686432 the upper 8 bits of this number are 11000000. That is fine in the spreadsheet and also fine if I shift those bits to the right as I mentioned above. The 1 on the far left is part of the number and not interpreted as a +/- sign. When I try to perform the logical And to clear all the upper most bits that 1 is making Visual Basic think it's a negative number and it fails to do the And. I believe this is what is happening because if I put a 31 bit number in there effectively making the upper left bit a 0 then everything works. As another test I removed the line D = Var And 255 from the Function and A, B, and C all work and return the correct values so I'm sure the problem is in the D = Var And 255 line.

  

In other languages like C and C++ you have data types that you can declare that are unsigned so all the bits are the number, the number is always positive so no need for bit to keep track of +/-. Visual Basic doesn't have that option for any of it's data types apparently, at least on line searches and reading the help files don't say anything about it. I tried various things like rotating the bits then dividing, setting the Double data type (recasting?) to an integer etc... nothing seems to work.

  

If anyone reading this knows what I am doing wrong please let me know!!

  

Not to be stopped in my spreadsheet fun I figured out a work-around. I'm not thrilled with it because I would like to get the Function to work but here is the work-around. I created another function that just does the logical And like this:

Function TEST(Var As Double)
TEST = Int(Var And 255)
End Function

This function just takes the value of Var and does the logical And to it. Now what I did to get the 4 byte values of a 32 bit value is perform some of the math in the Excel spreadsheet instead of in the function itself.  For the upper three bytes I could have used the above first BYTESPLIT function and omitted the code that gets the value for D but I decided for consistency to do it all in the spreadsheet.

To get the upper most 8 bits I don't need the TEST function I just put =INT($D$7/16777216) in the cell where I want the upper most bits and $D$7 is the 32 bit number. 

To get the next bye of numbers I do something similar: =TEST(INT($D$7/65536)) this does the division shifting the bits and then the function TEST clears the upper 8 leaving the second byte.

To get the third byte I did just like the second =TEST(INT($D$7/256)) but divided by 256 to shift 8 bits instead of 65536. Again the TEST function clears with a logical And and I get the right value.

For the last pesky lower 8 bits I used this: =MOD($D$7,256). This uses the MOD function to take the remainder after division of the 32 bit value in $D$7 and returns the lower 8 bits.

  

Once I got this work-around to work I thought great! I'll just change the BYTESPLIT function line D = Var And 255 and replace it with D = Var Mod 256 but that returned #VALUE! as well... It seems that the Visual Basic compiler just doesn't like to do some math functions on signed values.

  

Again if anyone knows a way around this to get it to work in VB I'd like to know.