Hello World and MsgBox in VBA

HelloA traditional first programming exercise is the “Hello World” program and this is what we are going to cover in this post. ¬†In the process we are going to take a look at how to use a message box in VBA to display some information to the user, how to configure these message boxes in a bit more detail and how to respond to a given user selections.

As usual I will talk about some best practise rules and and of course we are also going to get a nice warm fuzzy feeling when we see our application running ūüôā

Setting up our Spreadsheet

To set up a simple “Hello World” message box in Excel we first need to create our workbook, rename the project and create a new module for our code to live in. ¬†We can use the following steps to do this1I have assumed a complete beginner level of knowledge here and listed all of the steps in detail. ¬†I have also included some best practise steps such as renaming projects and modules.:

  1. Open a new Excel workbook and save it an an appropriate macro enabled format (e.g. *.xlsm or *.xlsb);
  2. Open up the VB Editor using the shortcut key combination <Alt> + <F11>;
  3. Give the project a sensible name other than the default:
    1. From the project explorer right click on the project and right click, then select “VBA Project Properties …”;
    2. Change the project name (e.g. to “HelloWorldProject”) and click on “Ok”;
    3. You should see the project name has changed in the project explorer;
  4. Next add a new module by right clicking on the project in the project explorer and selecting “Insert” then “Module”;
  5. Give the module a sensible name by clicking on it in the project explorer then using the <F4> shortcut key to bring up the module properties window;
  6. In the module properties window change the “(Name)” field2This is the codename of the object. to something sensible such as “mHelloWorld” and press enter. ¬†You should notice the module name changes in the project explorer and you can go and close the properties window if you like;

A general good practise is to make sure each module has  Option Explicit as the first line of code and this post explains both how and why.

A Simple Hello World using MsgBox

The simplest way to create a “Hello World” program in VBA is using a MsgBox. ¬†We can do this with just one line of code since the MsgBox has only one required input argument: The prompt (the message that is displayed in the popup box). ¬†The code below shows us how to do this by giving a prompt of “Hello World”.

When we run the program using the <F5> shortcut key we get the popup shown below.

HelloWorldInitial

Hello World popup from our one-liner

Now while this one line of code does the job and this is where many other blog posts on the topic would stop, my personal view is … well it’s a bit shite really. ¬†Those who have read my other posts will know I like to follow a number of rules when I’m coding and a couple that apply here are:

  • Always explicitly specify the important3You should judge for yourself what is “important”. ¬†The last bullet¬†in this list comes in handy here. input arguments (rather than relying on default input arguments);
  • Always explicitly specify argument names using “named arguments” (the ¬† :=¬†syntax);
  • Layout your code in a way that is as easy as possible to read (e.g. using line continuation, indenting, sensible variable names);
  • Use your Loaf! … don’t go too far with the above;

For the MsgBox there are three input arguments I consider “important”:

  1. Prompt: This tells the MsgBox what message to display. ¬†It is the only required input argument and of type ¬† String¬†¬†– if we don’t supply it then we get a compile error “Argument not optional”;
  2. Buttons: A numeric expression4Strictly an expression that evaluates to a numeric data type such as   Byte, Boolean, Integer, Long, Currency, Single, Double, Date .  Given the values it can take it might as well be an   Integer though.  that tells the input argument to tell MsgBox a number of things such as
    • Which buttons to display for a user to click on;
    • The default button if a user just presses <Enter>;
    • What icon to display on the MsgBox;
    • The modality of the input box (more on this later);
  3. Title: You can specify a   String  that is displayed in the title of the popup;

We can re-write the above one-line method using some of my good practise rules to get the below method.

When we run the program using the <F5> shortcut key we get the popup shown below.  This is very similar to the one-line method popup so it may not feel like we have achieved a lot Рbut our code has a much better internal structure.

The popup we get from our good practice version of "Hello World"

The popup we get from our good practice version of “Hello World”

More Details on the MsgBox Input Arguments

In the previous section we have used MsgBox as if it were a subroutine Рi.e. without any return values.  However, MsgBox is actually a function.  The full syntax for a MsgBox function call is

MsgBox(prompt [, buttons] [, title] [, helpfile, context]) As VbMsgBoxResult

The only part of this we won’t cover here is how to link your MsgBox to a helpfile (I will cover using help files in a separate post at some point).

The first input argument is¬†prompt¬†and this is a string expression which means you can include things5When I say “things” I really mean constants defined in the VBA type library and these are the most common examples I tend to use like a:

  • Tab character: vbLf¬† or Chr(10);
  • A line feed: vbLf¬† or Chr(10);
  • A carriage return: vbCr¬†or Chr(13):
  • Or a combination such as carriage return and line feed: vbCrLf¬†or Chr(13) + Chr(10);

When we combine any of the above with a string we need to ensure we use the concatenation operator  & and of course make sure we lay out our code sensibly using line continuation.

The¬†buttons¬†input argument is an optional numerical expression and can be used to specify a number of different things (not just what buttons to display). ¬†The general idea behind this is that the numerical expression is actually evaluated more like a binary number where each bit or combination of bits represents a set of flags that configure the MsgBox6A common coding technique is to use a bit string for specifying options a user can select and having an enumeration represent the human readable values. ¬†You then add the options together to form a combination of options selected. ¬†Again I plan to cover this in another post in detail … haven’t go around to it yet!. ¬†The possible set of input values are specified using the built in VBA enumeration¬† VbMsgBoxStyle¬†and below I have broken down the various values into tables according to¬†what¬†configuration options they specify.

Icons available from VbMsgBoxStyle

Button combinations that can be displayed from VbMsgBoxStyle

Modes of operation we can specify from VbMsgBoxStyle

Setting the default button using VbMsgBoxStyle

If we now re-write our good practise “Hello World” procedure to include some of the input arguments covered in this section¬†we can get code similar to that shown below. ¬†Notice how for the¬†buttons input parameter we ADD together the different configuration settings to achieve the overall shape we want.

This gives us the resulting popup shown below.

Good practise coding with some additional configuation.

Good practise coding with some additional configuration.

Who knew Buttons Could be so Much Fun!!!

A key thing to be aware of with the method of using enumerations, constants or similar to set up a given configuration is¬†they are numeric values. ¬†Now why is this important? … well because it’s very easy to make mistakes and get unexpected behaviour. ¬† From the above tables we can see that adding together a¬† vbAbortRetryIgnore¬† and a¬† vbYesNoCancel¬†gives a numeric value equivalent to¬†a¬† vbRetryCancel¬†so where we might have expected to see a set of buttons: Abort, Retry, Ignore, Yes, No and Cancel instead we only see buttons Retry and Cancel. ¬†The below code demonstrates this.

Buttons displayed are not what we expect!

Buttons displayed are not what we expect!

Responding to User Selections

Earlier I mentioned the MsgBox is actually a function with a return type of VbMsgBoxResult .  The last thing we will cover here is how to use this to respond effectively to user button selections.  We first declare a variable of the correct type  VbMsgBoxResult .  Next we assign the result of the function call to this variable and ensure our function call uses brackets (necessary when obtaining a return value from functions and we get a syntax error without it).  Finally we can use a simple select case to respond to the choice the user makes.  The code below shows an example.

When we run this we get a similar popup to the previous one but on pressing <Enter> then using <Ctrl> + <G> to open the immediate window we see the program has actually responded to our default user selection of Retry.

We get a very similar popup ...

We get a very similar popup …

ResponsiveImmediate

but immediate window output shows it did respond.

Example Workbook

All of the code in this post is included in the example workbook which can be downloaded below.

Example workbook for Hello World and MsgBox

Footnotes   [ + ]

Leave a Reply

Your email address will not be published. Required fields are marked *