Excel VBA: Bit-Wise Enumerations

Leveraging the nature of numbers storage in memory, we can exploit enumerators to construct complex Boolean expressions in near-English language. This post will change the way you handle multiple-choice questions.

Last week we introduced Enumerations and how they make our lives easier when handling numeric sets of constants.

Today we’ll take it further by exploiting the Enumeration data structure to handle combinations of values.

What is Combination of Values?

The typical use case we’re addressing here, is when we have to handle multiple-choice variables that can hold any combination of values.

Suppose your program needs to accommodate the ice-cream flavors a person may like. Judy likes Chocolate and Strawberry; Mike enjoys Strawberry and Coconut; Inga prefers Coconut solo.

How can we store each person’s favorite set of flavors in a single variable?

More than that, how can we efficiently ask questions about each person’s taste to match, for example, to the available flavors we have in store currently?

You’re about to be surprised how easy and elegant this is done with the help of Bit-Wise enumerations.

How Numbers are Stored in the Computer’s Memory

Let’s quickly refresh our memory on this issue.

A bit is an atomic “flag” that can be in one of two states: 0 or 1.

A Byte is a combination of 8 bits. Each of the 8 bits in a Byte can be set to either 0 or 1, which gives us many possible unique combinations we can represent using a single Byte.

We can define that the combination of 7 “zeros” and a closing “one” will represent the number 1:

00000001 = 1

Here’s the number 2:

00000010 = 2

…and 3:

00000011 = 1

Four would be:

00000100 = 4

Are you seeing a pattern here?

The important point to note is that each bit (starting from right to left) represents a power of 2 from 0 onward.

As you remember, 2 to the power of zero is actually 1. Here’s how we denote that:

2^0 = 1

With that, let’s see all of the numeric values each bit stands for in a Byte:

Recall how we represent the number 3, shown above. We “turn on” the “1” bit and the “2” bit, and together we get 3 (1+2=3).

Following the same pattern, we can turn on bit “1” with bit “4” to represent the number 5:

00001001 = 5

Following this logic, we can now calculate the highest number we can represent with a single Byte:

11111111 = 255 (=1+2+4+8+16+32+64+128).

So, how do we represent numbers greater than 255? By joining two Bytes together. And how do we represent negative numbers? Well, there is a way, and I go into this in great detail in my online course: Computer Programming with Excel VBA, but we now know just about enough to combine our favorite ice-cream…

Bit-Wise Boolean Operations

The very fundamental Boolean expression of a bit considers 0 to be False and non-zero (e.g. 1) to be True. By the way, going the other way around, in VBA False is also treated as 0 for math operations, however, True is converted to -1 (minus 1).

Applying basic math operations on “ones” and “zeros” allow us to calculate True/False questions.

0+1 = 1 = True

0+0 = 0 = False

Also, the Boolean operators, AND / OR, are defined as follows:

As you can see, adding numbers is equivalent to combining multiple bits in a Byte – the result of an OR operation!

As the number 5 is represented by turning “on” bit 1 and bit 4, we can say that 5 represent 1 + 4, which is equivalent to 1 OR 4:

Let’s see how that helps us with enumerations and ice-cream.

Bit-Wise Enumeration

If we take care to enumerate all members of an enumerator with a single-bit unique number, we can start combining their values in a way that reflects Boolean yes/no questions.

Let’s enumerate some traits of a member in a dating application:

Enum ETraits

    Tall = 2 ^ 0

    Blond = 2 ^ 1

    Thin = 2 ^ 2

    Smart = 2 ^ 3

    Pretty = 2 ^ 4

    Calm = 2 ^ 5

    Social = 2 ^ 6

End Enum

Yes, I make it easy for me to see my Boolean intentions by calculating the numeric values as powers of 2. Instead of setting Tall to be 1, I’m setting it to be 2^0, and so on. Just as a reminder of what I’m doing here.

Now, the beauty is that I can ask whether a person is Tall and Smart, which will translate to the following Boolean expression:

Tall = 2^0 = 00000001

Smart = 2^3 = 00001000

Tall + Smart = 00001001

And there you go – we have the combination of Tall and Smart represented in a single number: 9!

The value 9 encapsulates a combination of two unique enumerated values: 1 AND 8!

Let’s find the perfect match for you by combining all much-desired traits in a single enumerated variable.

Let’s say that you are looking for a partner who is smart, social and calm.

Along comes Gabi with her traits being represented by the number 97 (or 01100001), and your program needs to provide an answer: is Gabi a perfect match for you?

The following function will return True if a traits-type variable perfectly matches your preference: smart, social and calm:

Function AreYouThePerfectPartner(PersonsTraits As ETraits) As Boolean

    AreYouThePerfectPartner = _

     CBool((PersonsTraits And Smart)) And _

           CBool((PersonsTraits And Social)) And _

           CBool((PersonsTraits And Calm))

End Function

For each required trait (such as smart), we apply a bit-wise AND that returns a non-zero (True) if the trait (smart) bit and the trait of the person being evaluated are both 1, or True.

Remember that we are manipulating numbers here, albeit bit-wise, so we explicitly cast the result to Boolean to return False (for 0) or True (otherwise):

CBool((PersonsTraits And Smart))

Finally, we glue all traits’ checks with AND so that we only return True if ALL of them are true, hence, a perfect match!

For the sake of completeness, here’s a Sub that defines Gabi’s traits and informs us whether she’s a perfect match for you, or not:

Sub DefineCandidate()

    Dim person As ETraits

    person = Calm + Social + Tall 'Results to: 01100001

    If AreYouThePerfectPartner(person) Then

           MsgBox "Found the best match!"

    Else

           MsgBox "Not your cup of tea…"

    End If

End Sub

Now that we have set you up with the perfect match, go out and get some ice-cream together. I leave it to you to implement the function that checks if they serve your favorite ice-cream!

Hey, don’t forget to share this Blog post with your colleagues – they’ll thank ya!