# Predict The Output III



## Colin Legg (Jun 26, 2009)

Following on from Oorang's thread 'Predict The Output', I thought this might provide some entertainment for those who haven't tried it before.

The same rules, predict the outputs of the procedure and then run it to see how many you get right! 


```
Sub Predict_The_Output_II()
 
    Debug.Print 2 Or 4
 
    Debug.Print 2 And 4
 
    Debug.Print Not 2
 
End Sub
```


----------



## RoryA (Jun 26, 2009)

*Re: Predict The Output II*

If it hadn't been you posting it, I suspect I wouldn't have thought hard enough about the last one...


----------



## Richard Schollar (Jun 26, 2009)

*Re: Predict The Output II*

Hmm, that's not a private poll


----------



## RoryA (Jun 26, 2009)

*Re: Predict The Output II*

That's just embarrassing!  (If you'd kept quiet no-one would have known which was which)


----------



## Colin Legg (Jun 26, 2009)

*Re: Predict The Output II*

The original 'Predict The Output' thread had a public poll so I copied it for this one. If a private poll would be better then please would you do the honours? 


Unless everyone gets 3/3, I'll post an explanation of the results later once everyone who wants to has had a go! 


EDIT: I just noticed that this thread should really have been called 'Predict The Output III'


----------



## MorganO (Jun 26, 2009)

*Re: Predict The Output II*

Hmmm... I was expecting the output to be boolean... that surprised me!  Will be looking forward to seeing an explanation of this one!


----------



## RoryA (Jun 26, 2009)

*Re: Predict The Output II*

We'll all be a bit wiser when Colin explains...


----------



## Richard Schollar (Jun 26, 2009)

*Re: Predict The Output II*



rorya said:


> We'll all be a bit wiser when Colin explains...


 
Oh, aha! Ahahahahahahahaha!

You're so funny


----------



## Colin Legg (Jun 26, 2009)

*Re: Predict The Output II*

lol Rory... don't give the game away!


----------



## RoryA (Jun 26, 2009)

*Re: Predict The Output II*

I know - I crack me up. 
(I was going to add something about two's comp, three's a crowd..)


----------



## Colin Legg (Jun 26, 2009)

*Re: Predict The Output II*

It's been switched over to a private poll. I think it reset the votes so anyone who had already voted might have to re-vote.


----------



## RoryA (Jun 26, 2009)

*Re: Predict The Output II*

The 0 option seems to be missing too.


----------



## Colin Legg (Jun 26, 2009)

*Re: Predict The Output II*

Hi Richard,

Two people had voted 0 which isn't a choice on the poll anymore.

Cheers


----------



## Richard Schollar (Jun 26, 2009)

*Re: Predict The Output II*

For some weird reason it won't accept '0' as a poll question when it's a private poll so I've replaced with Zero.  All scores have been zeroed out I'm afraid.


----------



## cornflakegirl (Jun 26, 2009)

*Re: Predict The Output II*

I don't get the code. And I don't get Rory's jokes. Feel thick now!


----------



## RoryA (Jun 26, 2009)

*Re: Predict The Output II*



cornflakegirl said:


> And I don't get Rory's jokes. Feel thick now!


 
You should feel *happy* about that part.


----------



## cornflakegirl (Jun 26, 2009)

*Re: Predict The Output II*

But your jokes are normally funny. In a dad way


----------



## RoryA (Jun 26, 2009)

*Re: Predict The Output II*

That reminds me - when are you due to pop?


----------



## cornflakegirl (Jun 26, 2009)

*Re: Predict The Output II*

Couple of months. If it doesn't claw its way out first. (Having particularly uncomfortable day today!  )


----------



## Greg Truby (Jun 26, 2009)

*Re: Predict The Output II*

The first two didn't cause me any problems. But the *NOT 2* bucked off and into the dirt. Completely didn't expect that one. What I had expected to see would more or less have been the same as 
	
	
	
	
	
	



```
? not(cbool(2)) + 0
```
I still ain't quite sure of the logic on that one. I understand arithmatically what it's doing. But I don't get why MS would program it to behave like that.


----------



## schielrn (Jun 26, 2009)

*Re: Predict The Output II*



MorganO said:


> Hmmm... I was expecting the output to be boolean... that surprised me! Will be looking forward to seeing an explanation of this one!


I was expecting the same. So as you know I am one of the 0's.


----------



## Greg Truby (Jun 26, 2009)

*Re: Predict The Output II*



Greg Truby said:


> ...I don't get why MS would program it to behave like that.


Upon further reflection I now get why the NOT operator does what it does. The algorithm makes perfect sense in the context of the integer values of TRUE & FALSE in VBA. Undoubtedly, I'm spoiled by the Boolean worksheet functions which handle the coercion a little more intelligently.  That was a good little excercise, Colin.  Always good to know where there's a low-flying beam just waitin' ta smack me in the knoggin.


----------



## Fazza (Jun 27, 2009)

*Re: Predict The Output II*

I like the jokes, Rory. Makes me wonder if you do cryptic crosswords; would suit I think.


----------



## DonkeyOte (Jun 27, 2009)

Colin, when are you providing the analysis - this went so far above my head my neck still hurts from looking at it.


----------



## Greg Truby (Jun 27, 2009)

DonkeyOte said:


> Colin, when are you providing the analysis - this went so far above my head my neck still hurts from looking at it.


Should you not want to wait that long -- now that we're onto the second page, here are a couple of hints: think binary & bit math for the AND & OR operators. For the NOT, just type *? true + 0* into the immediate window and then think about what you need to do arithmatically to always jump back and forth between the integer values of TRUE & FALSE.


----------



## Colin Legg (Jun 27, 2009)

Hi Luke,



DonkeyOte said:


> Colin, when are you providing the analysis - this went so far above my head my neck still hurts from looking at it.


 
I'll post the explanation tomorrow evening. I don't want to post it yet in case it spoils the thread for anyone who hasn't seen it.

Glad you're all enjoying it!!


----------



## xenou (Jun 27, 2009)

Missed (Not 2) also...
I expected the answer to be 32,765


----------



## RoryA (Jun 27, 2009)

*Re: Predict The Output II*



Fazza said:


> I like the jokes, Rory. Makes me wonder if you do cryptic crosswords; would suit I think.



Yep - I like to do the Times on the train home in the evenings.
I still think anyone who _likes_ my jokes should worry though...


----------



## Fazza (Jun 28, 2009)

*Re: Predict The Output II*

I'm reminded of the (peculiar?) British humour & the clever advertising that I noticed when I travelled on trains in London. I don't think it travels well - across oceans!


----------



## Colin Legg (Jun 28, 2009)

Time for the explanation! 


As you may have guessed from hints on earlier posts, this thread is all about *bits*. But what is a bit? We're all used to the base 10 (decimal) system (digits 0 to 9) and most of us have seen the base 2 (binary) system (digits 0 and 1). A bit is a base 2 digit, either 0 or 1 (off or on).

Sometimes you may have declared a variable in your VBA code as a Byte data type. You can see in the VBA helpfile that a Byte is an *unsigned*, *eight bit* number ranging in value from 0 to 255. All of this is easier to understand when we summarise as follows:


```
*Decimal     Binary (Byte)*
0           0000 0000       'smallest value
1           0000 0001
2           0000 0010
3           0000 0011
4           0000 0100
...etc...
```
Each 0 and 1 in the binary column is a bit, and eight bits makes a byte. It's that simple!







Let's revisit the code:

```
Sub Predict_The_Output_III()
 
     Debug.Print 2 Or 4
 
     Debug.Print 2 And 4
 
     Debug.Print Not 2
 
End Sub
```
 
What data type(s) are these numbers? Are they bytes? We can determine this by using the TypeName() function:

```
Debug.Print TypeName(2) 'returns Integer
Debug.Print TypeName(4) 'returns Integer
```
We can also use the TypeName() function to see that the output of each these operations is an integer.


Okay, so we're dealing with integers, not bytes here. You can see in the VBA helpfile that, in VBA, an integer is a *signed*, *sixteen bit* number ranging in value from -32,768 to 32,767. The 'sign factor' makes these slightly different to bytes: the first bit (highlighted in red) determines whether the number is negative or positive.

```
*Decimal     Binary (Integer)*
-32768      1000 0000 0000 0000     'largest negative value
...etc...
-3          1111 1111 1111 1101
-2          1111 1111 1111 1110
-1          1111 1111 1111 1111
0           0000 0000 0000 0000
1           0000 0000 0000 0001
2           0000 0000 0000 0010
3           0000 0000 0000 0011
4           0000 0000 0000 0100
5           0000 0000 0000 0101
6           0000 0000 0000 0110
...etc...
32767       0111 1111 1111 1111     'largest positive value
```
I've included a few more numbers there so we can refer to them later on.




So far so good, but what does all of this have to do with OR, AND and NOT? OR, AND and NOT are all bitwise operators: they manipulate bits. We can use truth tables to summarise the results of the operations:

*OR* has the following truth table:

```
0 Or 0 = 0
1 Or 0 = 1
0 Or 1 = 1
1 Or 1 = 1
```
*AND* has the following truth table

```
0 And 0 = 0
1 And 0 = 0
0 And 1 = 0
1 And 1 = 1
```
*NOT* has the following truth table

```
0 = Not 1
1 = Not 0
```
NOT is slightly different to OR and AND because it only operates on a single bit: it is an unary operator.




Now we are armed with this information let's try to predict the outputs of the procedure.

*Firstly*

```
Debug.Print 2 Or 4
```
Let's write the integers 2,4 in binary:

```
*Decimal     Binary*
2           0000 0000 0000 0010
4           0000 0000 0000 0100
```
 
If we apply our OR truth table to each bit we get the following:

```
*Decimal     Binary*
2           0000 0000 0000 0010
4           0000 0000 0000 0100
=
*?           0000 0000 0000 0110*
```
If you look back at the integer summary table you can see that ? = 6 which is the output in the immediate window if you run the procedure.



*Secondly *

```
Debug.Print 2 And 4
```
If we apply our AND truth table to each bit we get the following:

```
*Decimal     Binary*
2           0000 0000 0000 0010
4           0000 0000 0000 0100
=
*?           0000 0000 0000 0000*
```
If you look back at the integer summary table at you can see that the output ? = 0



*Lastly *

```
Debug.Print Not 2
```
If we apply our NOT truth table to each bit we get the following:

```
*Decimal     Binary*
2           0000 0000 0000 0010
=
*?           1111 1111 1111 1101*
```
If you look back at the integer summary table at you can see that the output ? = -3



Hope that explains the mystery!


----------



## Fazza (Jun 28, 2009)

Much appreciated & well explained, thanks.


----------



## MorganO (Jun 28, 2009)

Ahh yes... it is coming back to me now!  I remember doing those truth tables during my year attempting a computer-science degree many a year ago, ultimately ended up with a management degree!  Explains why I spend a lot of time here on the board learning this programming stuff over and over again!

Thanks for the exceptional answer - you have a teachers style for sure.

Owen


----------



## Jon von der Heyden (Jun 29, 2009)

Wow, clever stuff! 

For sure Colin, you explain things better than anyone!


----------



## cornflakegirl (Jun 29, 2009)

Great explanation. I am left with one question - why is this even possible in VBA? Under what circumstances would someone want to do this?

EDIT - two questions - still don't get Rory's second joke...


----------



## RoryA (Jun 29, 2009)

It was about two's complement. I did say it wasn't funny...
It's actually very useful in VBA and is why you can just add things like MsgBox constants together - e.g.:

```
msgbox("Bit masked buttons", vbCritical + vbYesNo + vbSystemModal)
```
You are effectively using an Or bitmask.


----------



## cornflakegirl (Jun 29, 2009)

I just skimmed that Wiki article. My brain now hurts, and I still don't get the msgbox constants thing. (You're right about the joke not being funny though  )


----------



## Colin Legg (Jun 29, 2009)

Hi Emma

I take your point. Understanding how they work is one thing but practical application is another, so let me give the example which was given to me when I first read about all of this.

Suppose you want to determine if an integer is odd or even. We can use AND to create a lightning quick function to do this:


```
Sub test()
 
    Debug.Print IsOdd(0)
    Debug.Print IsOdd(1)
    Debug.Print IsOdd(2)
    Debug.Print IsOdd(-3)
    Debug.Print IsOdd(-10)
    Debug.Print IsOdd(32765)
    Debug.Print IsOdd(-32768)
 
End Sub
 
Function IsOdd(ByVal intValue As Integer) As Boolean
 
    If (intValue And 1) = 1 Then IsOdd = True
 
End Function
```
 

How does this function work?

If you look at the integer table in my previous post you can see that the one bit (on the right) is always on (1) when the number is odd.

The AND performs this operation:


```
Decimal     Binary (Integer)
intValue    xxxx xxxx xxxx xxxx
AND
1           0000 0000 0000 0001
=
            0000 0000 0000 000?
```
 

There are only two possible outputs from this operation, depending on whether the x was on or off.


```
Decimal     Binary (Integer)
0           0000 0000 0000 0000
1           0000 0000 0000 0001
```
 
The answer will only be 1 if the number passed into the function was odd.


Obviously this is just the proverbial tip of the iceberg.


----------



## RoryA (Jun 29, 2009)

Emma,
See if this article helps re my MsgBox comment and bitmasks.


----------



## cornflakegirl (Jun 29, 2009)

Colin - that's pretty cool! Thanks!

Rory - thanks for that article. Don't understand it yet, but do at least have some idea what I don't understand...


----------



## Oorang (Jun 30, 2009)

Yah not 2 had me messed up. I knew it would be negative and implicitly an integer, but I forgot that when the sign bit is flipped you count backwards. Good One


----------



## pgc01 (Jun 30, 2009)

Hi Emma



cornflakegirl said:


> Great explanation. I am left with one question - why is this even possible in VBA? Under what circumstances would someone want to do this?


 
This is also a practical example. I posted it 2 weeeks ago to set/reset an attribute of a file:

http://www.mrexcel.com/forum/showthread.php?t=396338


----------



## Oorang (Jul 1, 2009)

cornflakegirl said:


> Great explanation. I am left with one question - why is this even possible in VBA? Under what circumstances would someone want to do this?



I suppose the most literal minded way of answering that question would be "because it's possible in VB6 which VBA is based on". As for why would it be possible at all, it's a way to store multiple pieces of information in one simple little number. A long integer is 32 bits. That means you could define 32 Boolean values in one field and store them in a single Long instead of 32 Boolean (true/false) fields. If one were trying to save on storage, or minimize the number of parameters being passed around, this would be pretty handy. A trivial example follows:


```
Option Explicit

Public Enum eEmployeeData
    'Shifts:
    eFirstShift = 1
    eSecondShift = 2
    eThirdShift = 4
    eSwingShift = 8
    'Pay Type:
    eSalary = 16
    eFullTimeHourly = 32
    ePartTimeHourly = 64
    'Is Contractor?:
    eContractor = 128
    'Status:
    eRetired = 256
    eFired = 512
    eQuit = 1024
    eLaidOff = 2048
    eActive = 4096
    [_max] = 4096
End Enum

Public Type EmployeeInfo
    Name As String
    Data As eEmployeeData
End Type

Sub Example()
    Dim employee As EmployeeInfo
    employee.Name = "John Q. Public"
    employee.Data = eActive + eContractor + eFirstShift + eFullTimeHourly
    MsgBox "So from this one little number """ & employee.Data & """, we know all of this about " & employee.Name & ":" & DataToString(employee.Data)
End Sub

Private Function DataToString(ByVal dataValue As eEmployeeData) As String
    Dim strRtnVal As String
    Dim lngExp As Long
    For lngExp = 0 To Log2(eEmployeeData.[_max])
        Select Case dataValue And (2 ^ lngExp)
        Case eFirstShift:       strRtnVal = strRtnVal & vbCr & "eFirstShift"
        Case eSecondShift:      strRtnVal = strRtnVal & vbCr & "eSecondShift"
        Case eThirdShift:       strRtnVal = strRtnVal & vbCr & "eThirdShift"
        Case eSwingShift:       strRtnVal = strRtnVal & vbCr & "eSwingShift"
        Case eSalary:           strRtnVal = strRtnVal & vbCr & "eSalary"
        Case eFullTimeHourly:   strRtnVal = strRtnVal & vbCr & "eFullTimeHourly"
        Case ePartTimeHourly:   strRtnVal = strRtnVal & vbCr & "ePartTimeHourly"
        Case eContractor:       strRtnVal = strRtnVal & vbCr & "eContractor"
        Case eRetired:          strRtnVal = strRtnVal & vbCr & "eRetired"
        Case eFired:            strRtnVal = strRtnVal & vbCr & "eFired"
        Case eQuit:             strRtnVal = strRtnVal & vbCr & "eQuit"
        Case eLaidOff:          strRtnVal = strRtnVal & vbCr & "eLaidOff"
        Case eActive:           strRtnVal = strRtnVal & vbCr & "eActive"
        End Select
    Next
    DataToString = strRtnVal
End Function

Private Function Log2(ByVal value As Long) As Long
    Const dblLog2_c As Double = 0.693147180559945
    Log2 = Log(value) / dblLog2_c
End Function
```


----------

