Creating Custom Forms in Excel

The last activity that we'll be doing today is creating a Userform for our StudentRecords application. First, add a new column to your StudentRecords spreadsheet where you can select whether the student is Male or Female.

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!

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) = TextBox1

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!

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

Recap exercises Week 3!

The first thing we're going to do today is recap some of what we've already learned (I can hear Kit yawning from here!) :-).

Recording Macros
Open up your first revision exercise where you created a chart displaying the cost-price/selling price (you could use any spreadsheet that has info that can be graphed!).

• Record a new macro called CreateChart to create an identical chart (remember to stop recording when you finish!)
• Delete the chart you just made and run your macro.
  
• Now see if you can remember how to get to the VBA editor. Can you modify the scary VBA code that you've created to change the title of your chart? Run it again and see if it worked!

Data validation
In the same workbook, you should also have the Shop Sales worksheet. Add some data validation to the selling price cells, so that a user can't enter a selling price that is lower than the cost price! Make sure that you give them a useful message :-)

Programming practice!
Going back to AJ's Turtle Graphics web application, you can create a function that draws a square by typing the following:

• mk square( repeat 4 [forward 100 right 90] )

From now on, if you simply type: square, you should see a square on your screen. Now,

• create your own function called flower, that draws your square 36 times while turning right by 10 degrees each time!
• create your own function called triangle, and then create flower2
• create your own function called octagon, and then create flower3!

Pretty snazzy eh? Make sure you show me your functions! In fact, create a new post with the code for your functions triangle, octagon, flower2 and flower3.

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: The Macro Recorder.(Tip: right-click on the link and choose 'Open in new Window'). Once we've recorded our Macro, see if you can discover 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 55, re-run your macro to test it!
  
• Modify your code so that the font-size of cell A3 is set to 8 instead of 24, and again, re-run your macro.
  
• Modify your code so that the background colour of cell A1 is something other than Blue. As usual, re-run your macro to make sure that your change worked!
  

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. 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?

Learning to go Loopy!

OK, it's time to get back to our turtle and learn a few more computer programming concepts!

Right-click on this link to AJLogo (our Turtle Graphics commander) and open it in a new window. Last week we followed the first tutorial and saw how to begin drawing on the screen. First, in the command window, type

• clearscreen
• showturtle
• pendown

and then have a go at drawing a simple shape. If you can't remember, you might want to look back over the first tutorial. Otherwise, type 'home' in the commander window and get ready for the second tutorial!

This time, we're going to learn about the power of computer programming: repitition (loops) and procedures! There might be a lot of new concepts in this second tutorial, so don't feel alone if you feel like you're swimming! When you've finished the exercises (you might need to help each other!), write up your experiences on your blog! Try to cover the following:

• how you found the tutorial,
  
• why repitition is powerful
• why procedures are helpful

Making sure that people enter valid information!

Have you ever received a letter in the mail from your bank telling you that you must pay $0 before a certain date, otherwise you will be charged interest on the full amount? I have! Perhaps you've a better example of how automated procedures can go wrong! How might this mistake have been avoided?

It can certainly look pretty bad for you, if you create a customised application in Excel for a business and it spits out receipts or reports with stoopid information! Now, let's find out how we can stop this from happening in our own Excel applications! Work through this first introduction to data validation from Microsoft as well as the follow-on tutorial, before completing the following mini-project in class.

Recording student results

Being the excellent student that you are, a teacher has requested that you develop a simple spreadsheet to record student results for her class. There's been lots of problems (in other institutions) with students being given the wrong grades, and letters being sent to the wrong suburb, so we want to avoid these embarrassing situations!

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 %)
  
• Final Grade (F/P/C/D)
  

This spreadsheet needs to be prepared for input validation to avoid embarrasing data-entry errors. With this spreadsheet:

1. Add data validation to the DOB column so only dates between 1/1/1900 and today are valid.
2. Add a list of valid titles to the Title column (Mr, Mrs, Ms, Miss, Dr, etc.)
3. Add a list of valid suburbs to the spreadsheet (Katoomba, Leura, Wentworth Falls, Blackheath, Medlow Bath)
4. Add validation so the test mark must be between 0 and 30 inclusive.
5. Add validation so the assignment mark must be between 0 and 40 inclusive.
6. Calculate the total mark.
7. The grade should be entered as follows:
   • 00-50 : F
   • 50-70 : P
   • 71-85 : CR
   • 86-100: D
8. Save your spreadsheet as "Studentgrades.xls"

Could this little spreadsheet be improved at all? If so, give it a go!

When you get a chance, write a quick entry on your blog explaining what data validation is in Excel, and how hard/easy it is to use. How could your student data worksheet be made even more foolproof?

Our first look at VBA

Hopefully everyone has had a chance to finish the Excel revision exercise from last week! We're going to jump straight in this week and record our first macro (as we didn't get time to do this last week!)

To start us off, right-click on this link to Excel VBA: No experience necessary - Lesson 1 and choose the option to open in new window. You can start following this tutorial yourself in Excel, but unless you're really confident stop when you get to the heading "Loopy" (We'll come back to this later!).

Once you get up to the "Loopy" heading, try the following:

1. Go back to the VBA editor (Alt-F11) and see if you can modify your Example1 macro so that, instead of putting "Hello Excel" into the cell, it puts "Hello [your own name]". How did you know which line of the VBA code to change? Can you explain what each line of your VBA code is doing? Run your macro again to make sure it works.
   
2. Practise what you've learned by recording a new macro (call it 'Example2'). Try doing a few random things (entering your name into a cell, changing the formatting of a number to percentage etc.) before stopping the macro from recording. Now go back into the VBA editor and see if you can understand what each line is doing (try explaining it to someone around you!) Can you remember how to run your macro? Give it a go!

Time to add a new entry to your blog! If you go to the Blogger site, login, then see if you can remember how to create a new post (something like "My first VBA experience!"). If possible, make sure you cover the following points (all from the tutorial):

• What does VBA stands for?
• What is it good for?
• 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?
• How was your first experience of creating a computer program in VBA?

Right! Enough VBA for the moment!

Welcome to Customising Applications for Clients!

Over the next 18 weeks we'll be learning how we can customise standard office applications (spreadsheets, wordprocessors etc.) for clients by creating our own little 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 (as this is all that we have access to), but if you would like to use other software at home (such as the free OpenOffice suite) just let me know and I'll do all I can to accommodate you!

But, let's get started! The first thing I'd like us all to do is create our own Web-Logs (a.k.a. Blogs). A Blog is just an easy way for us to setup our own website and publish on the web in minutes! For us, it'll also:

• help us to keep in touch,
  
• provide a place for you to express yourself on the net,
• provide an opportunity for you to reflect on what you're learning and help me improve the course!

Click on the "Get your own Blog" button in the top-right corner of the window and follow the instructions. You'll have your own web address in no time! For your first post, how about your favourite spot in the mountains!

Once you've created your own site, add a comment to this post with the address of your own site so I can track them and keep in touch!

In our first class this Friday, we'll start by flexing our Excel skills with a little exercise sheet. Once you've finished the worksheet, add a new post to your blog commenting on how you feel about your Excel skills... do you feel confident, are there areas you want to brush up on?

We'll then record our first Excel macro, and discuss what we've created.

On top of that, if we've got time, we'll even start learning the basics of computer programming by following a tutorial that will show us how to command a turtle (or a triangle in this case!) Don't miss it!