A primer in VBA for Excel (2)

Part 2: Hello World!

Congratulations, you are about to begin coding! Now that the setup is complete (see part 1), we can start reviewing some elementary math.

Hello World!

In a new Module, which is really just a blank text document in your editor, copy-paste the following code snippet:

'This procedure prints "Hello World!" in the console
Sub hw()
Debug.Print("Hello World!")
End Sub

You have now written your first subroutine. Run it by pressing the run button from the toolbar.

The run button is the green little triangle below the Debug menu

In the immediate window, also known as the console, you should not see:

Hello World!

There you go! Your first macro! Let’s review what we just did here:

  • Comments are preceded by a an apostrophe (). These are just there for you to read, and have no impact on the code.
  • We defined a new subroutine with the keyword Sub and gave it a name (hw). The parentheses will be added automatically. We also defined where we ended the subroutine with the keyword End Sub
  • We called a function (Debug.Print) which takes as parameter a string of text, surrounded by double quotes. The Debug.Print prints the text passed in parameter in the immediate window.

You got it? Let’s move on!

Mathematics 101

You’ll rarely need anything more sophisticated, in terms of math:

Sub backToSchool()
Debug.Print 3 + 1 - 2
Debug.Print 10 * 3 / 6
Debug.Print 2 ^ 4 / 2
Debug.Print 26 Mod 5
End Sub

Run it, and this will print:

2
5
8
1

First, notice that we have omitted the parentheses around our parameter passed to the Debug.Print function.

You’ll also notice that VBA respect the classic priority rules (2^4/2 is not the same as 2^(4/2)). For those unfamiliar with the Modulo operator, it simply returns the rest of the whole division of the first to the second argument:

26 = 5 x 5 + 1; Hence 26 Mod 5 = 1 

Strings

Strings is the term used to denote text. “Hello” is a five-character string. Strings are delimited by double-quotes. There aren’t that many fundamental operations on strings:

Sub onceUponAtime()
'We've seen this one before
Debug.Print "Hello World!"
Debug.Print "Hi, my name is " + "David."
End Sub

See that + sign? In math, this is the summation operator. When used with strings, we concatenate two strings, i.e. glue them together.

Booleans

Booleans, named after George Boole, are statements which are either true or false. There are three important operators with booleans, the And, the Or and the Not operator.

Sub lieDetector()
Debug.Print True And True
Debug.Print True And False
Debug.Print False And False
Debug.Print True Or True
Debug.Print True Or False
Debug.Print False Or False
Debug.Print True And (Not False)
End Sub

Which should print in the console:

True
False
False
True
True
False
True

Note that “True” and True are not the same thing! The first is a string, the second is a boolean!

Other operators will return booleans:

Sub lieDetector2()
Debug.Print 2 = (1 + 1)
Debug.Print 4 > 6
Debug.Print 6 >= 6
Debug.Print True = "True"
Debug.Print "David" = "Dav" + "id"
End Sub

This will print to the console:

True
False
True
False
True

All of this could be a little abstract, but without knowing it, you use it all the time in Excel, with the =IF function!

There are many other data types — arrays, objects, dates — but the three fundamental types will suffice for now.