Rough Notes, and posts to be referenced from elsewhere, on VBA Windows API

[DocAElstein]

…. Continued from last post….
( …. as the saying goes, Two's complement, (three’s probably some other crazy idiotic computer mathematicians abortion) )

Bollox, what went wrong
It did not take long to find the problem. .. VBA ( and most computer stuff) apparently does not always use the simple binary logic

Just to review what I was dong, the simple binary logic: So I was following the simple logic like..
A number 7 is somehow in computer binary a variation of

            8 4 2 1
0 0 0 0 0 0 0 1 1 1

, and correspondingly -7 I was thinking , (and in a few places I was even seeing stated….)

            8 4 2 1
1 0 0 0 0 0 0 1 1 1

It aint quite like that, , ( at last in most modern day computer things)
It seems mostly we have something called Two's complement
Crazy, but some computer dick thought it up:. This is the basic jist of it; ….. For the negative number, you do a few weird things…..
_ you first turn the positive number upside down / flip / invert or call it what you want to get this ( 0s become 1’s and visa versa )

            8 4 2 1
1 1 1 1 1 1 1 0 0 0

_ Now you do a math add of 1,
( but important is that you do it in base 2 -
1+0 = 1
1+1 =0 and carry over a 1 to add to the next to the left
etc. )

            8 4 2 1
1 1 1 1 1 1 1 0 0 1

That’s it, Crazy, but that's how it goes: in most computer systems, -7 would have the form something of the form
1 1 1 1 1 1 1 0 0 1
Just to get that clear, especially the add in base 2 bit, the same again for binary 6, and binary (2’s compliment) -6.
First the positive number, 6

            8 4 2 1
0 0 0 0 0 0 0 1 1 0

Now, for -6 …..
First a flip of the "normal" binary for 6

            8 4 2 1
1 1 1 1 1 1 1 0 0 1

, next, the final step, is to add the 1, in binary maths, which is base 2. So as it is base 2, you wont get a 2 furthest right. You get a 0 and then have to carry the 1 over to the next left****, so you get finally for -6

            8 4 2 1
1 1 1 1 1 1 1 0 1 0

( **** A nice Laymen way of thinking about adding a 1 in binary, is to start from the right and try to find an " empty" place ( i.e., a 0 ), to dump the 1 in . )


If the positive number was 4, then to get -4, after the flip, you would have had to carry over the added 1 twice and so finally for -4 you would have

            8 4 2 1
1 1 1 1 1 1 1 1 0 0

Compare that with positive 4 and you can see its easy to get confused

            8 4 2 1
0 0 0 0 0 0 0 1 0 0

---

So, what went wrong?
So we know now what the 2’s codswallop is all about, … how does that explain the failings of the last post

The second function is sound. – Just to review that: The basic idea, as Hans said , is that in a VBA If … And … Then with two numbers either side of the And , the first thing to do is convert the two numbers to binary, and then, do a bitwise And
, - just for convenience show them in the same vertical plane…..
Example:
6 And 5

            8 4 2 1
0 0 0 0 0 0 0 1 1 0  ---- 6
0 0 0 0 0 0 0 1 0 1  ---- 5

The "result" of that, according to the bitwise comparison, is

            8 4 2 1           
0 0 0 0 0 0 0 1 0 0

Any amount of 1s and you get a True, so 5 And 6 is True. ( The actual result of 5 And 6 is what that last binary number is in decimal which is 4, but anything other than 0 is True )

Do the same for 4And 2 and the result is got from this

            8 4 2 1
0 0 0 0 0 0 0 0 1 0  ---- 2
0 0 0 0 0 0 0 1 0 0  ---- 4

, which finally is

            8 4 2 1           
0 0 0 0 0 0 0 0 0 0

That is 0 in binary or decimal
https://i.postimg.cc/hvzXKctd/5-And-6-4-And-2.jpg 5 And 6 4 And 2.JPG





So far so good.
can take the second small function, Function NumbersInVBAIf_And_Then(ByVal Dec1 As Long, Dec2 As Long) As Boolean , as OK


Perhaps better said, the final function, Function NumbersInVBAIf_And_Then(ByVal Dec1 As Long, ByVal Dec2 As Long) As Boolean , is also OK. With the right numbers
There is not much there for it to do wrong: It does a simple job of detecting any position where both decimal numbers have a 1. Simple and correct.

So what is the damm problem?? I think we know. The original simple Decimal to Binary function got the correct binary number for positive numbers, but usually wrong numbers for the negative as it was not based on the Two's complement codswollop for negative numbers
So we just need a new function to convert decimals correctly, in the case of negative numbers, in the Two's complement styleo

---

---

---

Two’s Compliment Function

There is nothing clever or difficult required. We just need to apply the logic carefully.

I done a coding quite quick, it won’t be the best, but it will do. 2 main sections, Rem 1, for positive given decimal numbers, Rem 2 for any negative given decimal numbers

Rem 1 - in the coding
Positive decimal numbers can be handled as in the main part of the previous simple decimal to binary coding, Function NumberInBinary(ByVal DecNumber As Long) As String

Public Function NumberInBinary2sCompliment(ByVal DecNumber As Long) As String
    If Not DecNumber < 0 Then
    Rem 1 Positive decimal number
     Let NumberInBinary2sCompliment = NumberInBinary2sCompliment & "0" ' The first digit ( or last 32th if you prefer ) is included, 0 is for a positive number
    ' Here we go again with the classic school maths way of converting a decimal number to binary
    Dim N As Long ' N is effectively the power of two at any time
        For N = 30 To 0 Step -1  '  We can think of this as looping from left to right, down the power of 2 values.           30 seems to be the limit before something is too big
            If DecNumber / (2 ^ N) >= 1 Then ' We need a   1   in the position,  for this power of 2
             Let NumberInBinary2sCompliment = NumberInBinary2sCompliment & "1" ' putting effectively a   1   in the position,  for this power of 2
             Let DecNumber = DecNumber - (2 ^ N) ' We have effectively accounted for an amount equal to this power of 2, so the decimal number we will further investigate must effectively be reduced
            Else ' We cant effectively eat up am amount from the decimal of this value of power 2 as the decimal total is smaller, so the binary string needs a 0 at this position
             Let NumberInBinary2sCompliment = NumberInBinary2sCompliment & "0"
            End If
        Next N ' We effectively go to the next power of 2 down
    Exit  Function ' We are finished here for a positive decimal number
    Else ' The case of a negative decimal number

For the positive number we just divide the decimal number by decreasing powers of 2.
__ If that gives >= 1 then we add a 1 to the binary number string, effectively then that is at a position for that power of 2. The decimal number is then reduced accordingly by that power of 2 amount.
__ If that division gave a number less than 1 then a 0 is added to the binary number string
Then we repeat this for the next power of two down, and so on.
For this, our new coding, for this case of a positive number, the function would finally end , Exit Function when all powers of two had been considered. This section is mostly a copy of the simple decimal to binary function, as in my Function NumberInBinary(ByVal DecNumber As Long) As String

For the Case of a negative decimal number, its just a case of carefully following the exactly the process of 2's compliment in some way

The following coding description is the first way I came up with, so I doubt it’s the most efficient It will do for now
I will do that in the next post as its specifically for getting the 2’s compliment and it may be good to have a separate post to reference later


…. Continued in the next post