Checking user input

Even though we're making it sooo easy for the users of our Excel applications by providing custom UserForms, we still can't be too sure exactly what information will be entered!

Time to find out how we can use the power of VBA to help our users enter valid information (a.k.a. - Input Validation). We'll do this by combining IF statements with MsgBox statements. Here's a sample to start us off:

Sub cmdOK_click()

'First check to see whether valid info was entered!
If txtFirstName = "" Then

  MsgBox("Please enter your first name")
  Exit Sub

End If

End Sub

We'll have a go at adding some input validation to demo Excel application which I'll show you in class - we'll gradually build this up with more functionality (such as making sure a number is entered, or putting the cursor back in the right spot on our UserForm for the user).

Once you're done, you're ready to finish Band Scenario 5!

Variables in VBA

After a bit of play with an online Spreadsheet, first up this morning is finding out exactly what variables are in VBA, and why we should declare them!

If you're really keen, you could do a google search for "vba excel variables" and see if you can find something readable!

This morning's challenge is to see if you can find out why my VBA code doesn't work! (I'll supply the code in class)

Using Excel Filters for your spreadsheets

One feature of Excel that we haven't yet seen is the Filter. A filter in Excel allows us to display only some of the items in our spreadsheets. Excel offers two options for filter data: the Autofilter and the Advanced filter.

To get started, see if you can find out more about Excel's Autofilter from Microsoft's Roadmap to Excel 2003 training (you might also want to try the Autofilter Quiz when you're finished). After a little while, we'll have an activity together to see how well we can use the Autofilter before having fun with the Advanced Filter.

Then you'll be ready to complete Band Case Study part 4!

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 then create a new workbook called "VBAExamples.xls", saving it in your own folder.

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!)

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.
• (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!