How to use bit shift in VBA

Question

I am trying to perform some bit-shifting operations to convert 0110 0100 0000 to 1100 1000 in VBA code. This example is a decimal value of 200.

I have tried the following code but it seems like the >> is not working. Also, how do I set the data type to zero to clear all bits?

If wordmsg is a 32-bit word, How can I set bit 30 and bit 31 equal to 1?

Sub test()
    'Declaring variables
    Dim simvalue As Integer, wordmsg As Integer, test As Integer, resolution As Integer

    simvalue= 200
    resolution = 0.0625

    wordmsg = simvalue / resolution
    test = wordmsg >> 3
End sub

Answer 1

There's no bit shift operator in VBA. You can, generally speaking, multiply, divide, AND and OR your way around.

Excel exposes the Bitand, Bitlshift, Bitor, Bitrshift and Bitxor functions to VBA through Application.WorksheetFunction.

Example: test = Application.WorksheetFunction.Bitrshift(wordmsg, 3).

To manipulate 32-bit words in VBA, you can't use the Long type, because its most significant bit (MSB, bit 31) holds the sign (negative when set). Attempting to set bit 31 with e.g. myLong = 2 ^ 31 will result in an overflow. You can use the Double type as a stand-in dword, and the Application.WorksheetFunction.Bit[...] functions will work with up to 48 bits (2^48 - 1; decimal 281474976710655).

Sub BitRightShiftTest()
    Dim d As Double
    
    d = (2 ^ 31) + (2 ^ 30) + (200 / 0.0625)
    Debug.Print d
    
    d = Application.WorksheetFunction.Bitrshift(d, 3)
    Debug.Print d
End Sub

Answer 2

Try BITRSHIFT function. http://www.excelfunctions.net/excel-bitrshift-function.html. If not available, use multiplication instead.