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.
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
You have now written your first subroutine. Run it by pressing the run button from the toolbar.
In the immediate window, also known as the console, you should not see:
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!
You’ll rarely need anything more sophisticated, in terms of math:
Debug.Print 3 + 1 - 2
Debug.Print 10 * 3 / 6
Debug.Print 2 ^ 4 / 2
Debug.Print 26 Mod 5
Run it, and this will print:
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 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:
'We've seen this one before
Debug.Print "Hello World!"
Debug.Print "Hi, my name is " + "David."
See that + sign? In math, this is the summation operator. When used with strings, we concatenate two strings, i.e. glue them together.
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.
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)
Which should print in the console:
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:
Debug.Print 2 = (1 + 1)
Debug.Print 4 > 6
Debug.Print 6 >= 6
Debug.Print True = "True"
Debug.Print "David" = "Dav" + "id"
This will print to the console:
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.