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!

Recording our first Excel VBA Macro!

Recording Macros in Excel isn't difficult at all... and the great thing is, we can start learning a bit about programming in Excel by getting Excel to do the programming for us!

The Excel macro recorder is the best teacher that you can have and will remain the best assistant for the rest of your life.

Follow these instructions to create your first Macro:

1. Start the Macro recorder
2. In Cell A1, enter the value 25
3. In Cell A2, enter the value 42
4. And in A3, enter 56
5. Set the font-size of A3 to 16.
6. Set the background color of A2 to yellow.
   
7. Stop the recorder.
   

Once we've recorded our Macro, see if you can find a way to look at the code that you've created! Can you understand the code?

After you've tested your first Macro a couple of times, see if you can make the following modifications to your code:

• Modify your code so that the value that is put into cell A2 is 999 instead of 42, re-run your macro to test it!
  
• Modify your code so that the font-size of cell A3 is set to 8 instead of 16, and again, re-run your macro.
  
• Modify your code so that the background colour of cell A2 is something other than Yellow. As usual, re-run your macro to make sure that your change worked!
• Challenge: See if you can add a button to your worksheet so that when you click on your button, your macro runs!
• Save your worksheet as VBAMacreExamples.xls
  

Remember if you get stuck, you can always find help nearby! We'll also take a look at a few other features of VBA - such as the VBA Editor, the VBA Debugger, With - End With statements and using buttons to trigger macros. We'll also take a look at some of the vocabularly we'll be using while learning VBA. These are all tools that we'll be using and re-using over the next term!

Some points you will want to file away in your mind:

• What does VBA stands for?
• Where or why is it used in Excel (and other applications)?
  
• How did you get to the VBA editor from Excel?
• What's the easiest way to figure out how write the code to do something in Excel VBA?
• Why is Debugging useful?

Welcome to Advanced Applications!

Over the next 18 weeks we'll be learning how we can customise standard office applications (spreadsheets, databases etc.) for clients by creating our own little computer programs (a.k.a. macros), as well as learning some nifty little features of the standard office Applications.

We will be using MS Word, MS Excel and MS Access in class and a groovy free program that will help you learn Computer Programming called Alice! Your textbook is Advanced Applications of MS Excel.

But, let's get started! Learning the basics of programming is just learning to solve problems with some special tools. Problem solving is a skill that needs practice! I always like to get started with a brain-teaser... just to exercise our minds a bit.

Other things we need to cover before we get started:

• Join email group
• SAGS
• Overview of Assignments
• Required Text
• Deciding on breaks!

Then we'll get started by flexing our Excel skills with a little revision exercised. Once you've finished the worksheet, have a think about your Excel skills... do you feel confident, are there areas you feel you need to brush up on?

After a break, we'll get stuck into the first exercise from our Text. After lunch, we'll start having fun learning programming with Alice!