Going Loopy in Excel!

You might remember way back at the start of term (5 weeks ago now), we looked at three fundamentals of programming:

• sequence (the statements to draw our turtle square had to be executed in the right order),
  
• selection (in VBA, we've used IF statements to select what our program should do, such as prompting the user whether they are enjoying programming and responding to their answer), and,
  
• repitition.

See if you can recall how to create a flower pattern using AJ's Turtle Graphics application!

Out main aim today is to have a go at using repitition in Excel VBA ... getting familiar with 'While' loops and 'For' loops in VBA. We'll have some exercises to practice and, of course, it'll help us finish the next scenario for our Band application.

But first we're going to have some fun with a mysterious outdoor activity!

Checking our UserForm info

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

Have a go at adding some input validation to your StudentResults Excel application - 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 brief re-cap of the last 5 weeks (hopefully involving a quiz and a bit of charades), 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)

New train timetable from September...

If you use the train in the mountains, you might want to check out the new timetable that's due to begin on 4th September... not sure what's affected for us.

You can find the new timetable here.

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

VBA - Objects and Events

First things first! If you haven't finished creating your custom form for student information, then do so now, as we'll be building on this example a bit later. We'll re-cap what we've learned so far about VBA... Sub's, MsgBox's,InputBox's and IF statements, with a quick VBA activity sheet!

Last week Justin was asking if we can learn how to make windows open all over the screen... (like a virus I think)... to learn how to do this (as well as other useful functions!) we need to start getting to know the Excel Object model. And what do you know, Microsoft have put together the Super-Easy Guide to the Microsoft Office Excel 2003 Object Model (Remember, right-click to open the link in a new window)!

Work in groups of 2 or 3 to answer the following questions, writing your answers on a piece of paper:

1. What are the three windows that are displayed in the VBA Editor?
2. After working through Lesson 3, give an example of an Object, a Method and a Property using an object from every-day life (such as a kettle - it could have a property called currentTemperature and a method called Boil(), but you can't use that now 'cause you've got to come up with something yourself!)
3. Try to find the three objects, three methods and three properties in Hands-on Task 3!
4. After working through Lesson 4 together, what is the name of the event that you need to use to complete Hands-on challenge 4?
   

If your team finishes early, have a go at going back to your Student Info application and see if you can now add code so that your form is shown as soon as the Excel Workbook is opened.

Assignment 1 Due Date - 23rd Sept.

As agreed in class last week, the due date for Assignment 1 is Friday 23rd of September (6 weeks away now). This means that if you've just completed Chapter 4 this week, then you can continue completing one chapter each week and you'll be finished on time! (otherwise, you might need to do a bit more than one chapter each week).

The holidays will be there for some flexibility, but bear in mind that you'll have the second assignment by then too, so the Hols might be a good chance to get started with your second assignment.

Creating custom forms in Excel

Being the excellent student that you are, a teacher has requested that you develop a simple spreadsheet to record student results for her class.

Open a new spreadsheet and add the following column headings in the third row.

• Firstname
• Lastname
• Title
• D.O.B.
• Town/City
• PostCode
• Test Mark (30)
• Assign Mark (40)
• Total (as a %) (calculated using Test and Assign mark)
  
• Final Grade (F/P/C/D) (calculated based on Total)
  

You might want to add a few students to check that your formulas work correctly. In class we'll discuss a few ways that we can help our teacher to enter the information (rather than entering it directly on the spreadsheet).

But for now see if you can figure out how to create a UserForm from the VBA editor. Try out all the different options and create a form that will be able to be used to add information to our Student Records. Be creative! You can change the colours of the form, you can make the background of your form an image, or even add images to buttons... the sky is the limit! The only caution is to be very careful about how you name your fields! If you create a text box to store the first name of a student, you should call it txtFirstName... this will become very important later on!

For those who get ahead, see if you can add some VBA code to your OK button that will add the info from your form to the spreadsheet. Double click on your forms OK button to create a macro for the button. You'll then need to use the Cells VBA function, which allows you to set the value of a specific cell. For example:

• Cells(2, 1) = txtFirstName

Try it out!

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.

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!

Access 2003 Workbook...

Just a subtle reminder that you should be finishing chapter 2 of your text "Advanced Applications with MS Access 2003" this week and starting chapters 3 and 4. (Don't worry, the chapters seem to get shorter after chapter 2 ;-)). We'll review the concepts from chapter 2 together this friday.

Chapters 3 and 4 will build on your skills creating queries and forms, moving towards forms that use combo-boxes to simplyfy the user experience.

As for Friday morning, we'll be getting started with our first Visual Basic programming! (See Recording our first Macro if you want to get started early!)