Telling people stuff from VBA (MsgBox)

Probably the most useful function that you'll ever come across in any programming language (not just VBA), is the MsgBox function! We're going to find out all about it now!

Creating a new macro from scratch

Close any Excel workbooks that you've currently go open and open your "VBAMacroExamples.xls" workbook (or creating it if you don't already have it!).

Now go to the Tools->Macro->Macros menu item, and type in the name of your new Macro: HelloWorld, then click on Create! You should find that you've got something like the following on your screen:

Sub HelloWorld()

End Sub

Every function that you ever write in VBA needs to start with Sub and end with End Sub (for those who are interested, this comes from old programming terminology and stands for subroutine or subprocedure).

You'll also notice that Excel has created a Module for you: module1 (over on the left-hand side of your screen). A module is just a place where you can store VBA functions and code.

The MsgBox function
Inside your HelloWorld function, add the line of code:

MsgBox "Hello World from Excel!"

Now see if you can run your macro! Modify this macro so that it prints "Hiya [your name]" or something else that tickles your fancy. Modify your macro again so that it displays a second message box after you click on the OK of the first.

To find out more about the MsgBox function, put the cursor on it and press F1 to bring up the context sensitive help. This is a great way to find out more about anything in VBA! In class we'll work together to add an exclamation mark icon to our message box.

Getting input from the user
Next we want to find out how we can get input from the user. Just say that you wanted to ask the user to enter a name... how can we do it? There's a very similar function to the MsgBox function, but you're going to have to find out yourself what it's called and how we can use it! (Hint: If you're looking at the help for the MsgBox function then you're really really warm! You'll see it also.)

Using an example from the Help, create a new macro in your module called Greeting (remember it always has to start with Sub and End Sub). This macro should ask the user what their name is, and then reply with a greeting using their name (or enter their name on the spreadsheet - we'll see how to do this together).

Extra activities

• Create a new macro called Greeting2 that asks for the users firstname, and then their lastname, before printing the greeting.
• Create another macro called HowOld that asks the user how old they are and then replies "Gee, 26, that's sooo old!" (or whatever the user types in)
• Create another macro that asks the user to enter their Name, address, suburb and state - all as separate InputBoxes, and then responds with a MsgBox confirming all the information (Eg: "Thankyou Geoff. You've indicate that your address is 25 Blue st, Katoomba, NSW")
  
• (Advanced) Create a new macro called Question1 that uses a MsgBox to ask a yes/no question, such as "Are you enjoying programming so far?". If the user clicks Yes, an appropriate messagedisplays, while a different message displays if they click No.
  

OK, so VBA can be useful to ask simple questions and get simple information from a user of our Excel application, but can you imagine getting all the data for a worksheet in this way? Think about entering information into our student records application where there's lots of info (firstname, lastname, project mark, etc.) How annoying would that be?! Well, there's a better way - we can create our own user specific forms - and that's what we'll be doing next!