Stripping Bits from a cell in MS EXCEL using VBA

I have a cell (lets call it A1) which is populated from an external application (DDE).
The value in the cell will be a 32 Bit Long (Double Word)

I want to check this cell every second and see if it has changed its value from 1 (one) second before. If it has changed I then want to check every BIT in the word to find any bit that has changed and whether it changed from 0 to 1  or  1 to 0

(I am making an assumption here that VBA can address individual bits using the notation 'variable.nn' )
If the value has changed I want to check each BIT (00 to 31) of the value to see if that BIT is TRUE or FALSE  (1 or 0)  Every time I find a BIT that has changed I will then run another sub routine based on the condition change ( Change to 1 or Changed to 0)

Once I have finished checking all 32 BITs I will then EXIT and wait for 1 second before i check again.

I am obviously no VBA expert so the best way I can describe what I want to do is along the lines ...... (I am using a variable called CellPrev to remember what the value if the cell was last time I checked it and on that basis I am guessing the my variable CellPrev will have to be a 'Global' ?)

EVERY SECOND call routine .....

Dim Cell as Long

Cell = A1

IF Cell == CellPrev then EXIT

If Cell.00 <> CellPrev.00 then
      IF Cell.00 = TRUE call routine Cell00_True
      ELSE
      call routine Cell00_False
      END

If Cell.01 <> CellPrev.01 then
      IF Cell.01 = TRUE call routine Cell01_True
      ELSE
      call routine Cell01_False
      END

If Cell.02 <> CellPrev.02 then
      IF etc etc


CellPrev = Cell

END
aphukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Three things:

1. You can have the Dim statement at the top of the code, before any SUB statement. This will make the scope the spreadsheet.

2. To get the nth bit from the the RIGHT, then use
IF variable AND 2^(n-1) THEN

3. I wouldn't check every single. That may severely interfere with people's ability to use Excel. Instead, use the Workbook_SelectionChange routine.
0
aphukAuthor Commented:
1. You can have the Dim statement at the top of the code, before any SUB statement. This will make the scope the spreadsheet.
Q. To be specific, I was concerned that using a variable (CellPrev) to 'remember' the value of the cell from one second before required that the variable was not declared each time the routine was called as it would be initialised to ZERO each time it was DIM 'ed and therefore I would not retain the previous value.

2. To get the nth bit from the the RIGHT, then use
IF variable AND 2^(n-1) THEN
Q. So does this mean the VBA does not support Cell.nn ?

3. I wouldn't check every single. That may severely interfere with people's ability to use Excel. Instead, use the Workbook_SelectionChange routine.
Q.
3.1 Assuming you are referring to checking all 32 bits then what exactly do you mean by 'That MAY severely interfere with people's ability to use EXCEL? Do you mean EXCEL will not be able to cope with the repeating code?
3.2 You refer to 'Workbook Selection Change' routine? What is this routine?
0
Ejgil HedegaardCommented:
I agree with Phillip, that an event handling is preferable to a timer operation, because the program only runs when something happens.
The event could be any change in cell A1, and updating the value to the same value, is also a change.
For that I would use a Worksheet_Change event, with Target A1.

If you want to check for changes or no changes every second and run some subs even if nothing has changed, I don't see an alternative to run on time.
But your code starts with checking for no change, and then exit the code, so that is not entirely true, or is it?

Here is a demo using a 1 second timing, converting the input decimal value to a 32 character long string with the bits, and comparing each character to the previous value 1 second before.
The result is shown in columns F:H.
Change the value in A1 and see what happens.
Only shortly the values will be not equal, since max. 1 second later the values are equal again.
Check-Bits.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

aphukAuthor Commented:
As I am going to have to check cells A1 to A100, as each one represents a different DDE item, it is probably better for me to check them based on time otherwise I will require 100 Worksheet_Change event checks.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No, you only need one event. If A cell changes, then you can use the Target.Row number.
0
aphukAuthor Commented:
I have split the points. Majority go to the answer that also provide sample code and was verbose
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.