Bit Manipulation using VBA

S

spurs

Active Member
Joined
Oct 18, 2006
Messages
479
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
  5. 2003 or older
Platform
  1. Windows
I have a variable called ReadIO that reads the value of an Input output module.

The decimal value for ReadIO ranges from 0 to 255 representing and 8 digit binary number

i.e. 1 1 1 1 1 1 0 1 is decimal 253

Is there any known vba code that I can use to take the value from the variable Read IO

then determine what the decimal value should be if I want to turn on or off an individual bit?

For example

suppose the value for ReadIO is 63 corresponding to binary 0 0 1 1 1 1 1 1

I want to create code to take that value (whatever it is 63 is just an example) and choose to change the value of bit 4 to a low state i.e. 0

Therefore the binary code will change to 0 0 1 1 0 1 1 1 or decimal 55

I imagine this is routine code that must exist somewhere in vb
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Original NumberBinaryPosition to ReplaceNew BinaryNew Decimal
63=TEXT(DEC2BIN(L28),"00000000")4=REPLACE(M28,8-N28+1,1,0)55
 
Upvote 0
I know you're looking for VBA solution, but I often find working it out with formulas first leads me in the direction for a vba solution.

so here's a formula to do it.

A1 = number (63)
B1 = the place of the binary you want to swap (reading binary from right to left)

=BIN2DEC(REPLACE(DEC2BIN(A1,8),8-B1+1,1,ABS(1-MID(DEC2BIN(A1,8),8-B1+1,1))))


Here it is broken out into smaller steps..

Excel Workbook
ABCDEFGHIJK
16340011111150001101115555
Sheet1
 
Upvote 0
so when using vba code can I simply go

z=BIN2DEC(REPLACE(DEC2BIN(A1,8),8-B1+1,1,ABS(1-MID(DEC2BIN(A1,8),8-B1+1,1))))

ie is bin2dec and replace and dec2bin recognized vba functions or are the worksheet functions?
 
Upvote 0
Actually, here's a FAR simpler method

=A1-CHOOSE(B1,1,2,4,8,16,32,64,128)


Although, this assumes you already know that the 4th bit is a 1, and changes it to a 0..
If the 4th bit was already a 0, then it would be wrong..
 
Upvote 0
Fantastic - the simple method works in vba quick and easy - thank you
 
Upvote 0
Ok so the syntax

X=A1-CHOOSE(B1,1,2,4,8,16,32,64,128)

will set bit B1 from hi to low ie 1 to 0

how do we reverse it so B1 is set from 0 to 1
 
Upvote 0
It's much easier in VBA than in Excel:

Code: 
  Dim i As Byte
  
  i = 63
  i = i And Not 4
  Debug.Print i
  i = i Or 4
  Debug.Print i
 
Upvote 0
Ok so based on the simpler method its just as easy to go

=A1-2^(B1-1)

which would set the bit from low to high

but how do you set it from high to low?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Creating a model - Unable to get trend function to run properly
Replies
2
Views
176
Misono
M
L
  • Solved
Evaluate each cell in a row against two other rows using VBA
Replies
5
Views
288
mumps
mumps
Y
  • Solved
Variable decimal format: displayed value differs from the value used when referenced in another formula
Replies
4
Views
282
Peter_SSs
Peter_SSs
K
  • Locked
  • Question
Need vba code for getting jpg from a file
Replies
2
Views
305
krishna008
K
I
  • Question
Data formatting for Chart
Replies
3
Views
315
IREALLYambatman
I

Forum statistics

Threads
1,214,657
Messages
6,120,773
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top