Types, loops, and Assignments!

Just in case I don't make it in tomorrow (I'm currently in and out of bed with a cold), here's an outline of the day that I was planning:

1. 9-9:30am - Finishing Easy Cash Loans. For homework we should have finished up to task 6. Now take a look at tasks 7 and following. Note: you might need to hassle Josie or Kym about the difference between comparing "65" < "639" and 65 < 639. It's all to do with the type of information you're comparing!
2. 9:30-11:00 - Last week we got our first taste of Looping in Excel. Take a few minutes to review your solutions to the Getting Started with Looping activity, before taking it a few steps further with Microsoft's Get in the loop with Excel Macros. Note: it's really important to review your work from last week first, as although the MS tutorial starts with the basics, it moves incredibly fast (If I'm in, we'll do a review of loops together)! Make sure you help each other with the hands-on workbooks! If you finish this early, you can start on the next chapter of our text that includes Band Scene 7.
3. 11-11:30 - Take a break!
4. 11:30-12:30 - Assessing our Alice assignments! It's important to do this before lunch, as some people can't be around afterwards. Everyone needs to print out 3 copies of the assessment checksheet (it's on the Intranet under IT->Cert III->Assessment Items). Make sure you print with duplex (double sided). Next, set up your Alice assignment on your computer, for other people to try out, with the three assessment sheets next to your computer. Then go for it! If everyone can walk around the computers and assess a few other assignments until each project has 3 completed assessment sheets next to it. When finished, read over your own assessment sheets to get an idea what others thought of your app, then if someone could put them all on my desk.
   
5. 12:30-1pm - Lunch.
6. 1-2:30 - I was hoping to show a few demonstrations of the second assignments, but that will depend on whether I'm in. Everyone should be able to print out the second assignment (and it's corresponding checksheet) from the intranet (see previous address). After reading through the assignment, take a choice:

• If you already have an idea of an Excel application that you can do for the second assignment, then get started straight away! Import your data, create your Documentation tab, your Fixed Data sheet etc. Start setting up your menus and forms!
  
• If you're currently stuck for ideas, please work through the next chapter of our text and then complete Band Scene 7.

Getting all of the above to happen will take a bit of coordination, so if anyone slots naturally into the role of a coordinator, feel free to try to get things happening on time!

Getting started with Looping!

It's time for us to start learning how to do while-loops! (That is, to repeat some bit of code while something is true). To get started, create a new workbook called "looping.xls" and create the following subroutine:

Option Explicit

Sub Counting()
'We're going to make up a variable to count through our while-loop:

Dim count as Integer
count = 0

While count < 10
 MsgBox("Count =" & count)
 count = count + 1
Wend
End Sub

Now add a button to your worksheet "Start Counting" and link it to your new macro. Run your code by clicking on your button and make sure it does what you expect it to do! Step through the code to watch what it does. Now, have a go at the following:

1. Call your second button "Even Numbers" and see if you can add a similar function that only counts even numbers (Hint: start with the exact code above).
2. Modify your EvenNumbers so that it counts "Odd Numbers" from 1 to 9 instead.
3. Modify your sub so that it count numbers up to 5 only. Check that they work correctly.
4. Modify your sub so that it counts the odd numbers between 90 and 100.
   

Add another Sub routine as follows:

Sub CountingCells()
 'We're going to make up a variable to count through our while-loop:
Dim count as Integer
count = 0
Range("A1").Select

While count < 10
 ActiveCell.Value = count

 'Now increment our counter, and move the active cell one down
 count = count + 1
 ActiveCell.Offset(1,0).Select
Wend
End Sub

Step through and watch your code to see what it does! Then,

1. Modify your sub so that the numbers go across the page instead of down the page.
2. Modify your sub so that the numbers go diagonally down the page.
3. Modify your sub so that it goes down the page again (back to the original), but only uses every second row.
4. Modify your sub so that, instead of entering a number in the cell, your sub sets the colour of every second cell (When you've got lots of information in a worksheet, it's sometimes really helpful to use an off-white (or pastel) colour for every second row - it helps to differentiate the rows of information). You'll probably need to record a macro to find some of the code to change the color of a cell
5. Ensure that your sub does all the cells up to the 100th row.
   
6. Using the ActiveCell object, see if you can find a property corresponding to the entire row (do ActiveCell. and see what pops up). Use this to modify your subroutine to set the colour of the entire row.
   

Remember, the power of loops is that you can repeat any simple action as many times as you want! Next week we'll learn a bit more about loops, but for now you're ready to tackle the next band scene!

The future of Word and Excel

There's a battle going on for where the future of Office applications is headed (spreadsheets, word processing).

Microsoft has invested huge amounts of money into the new Office 2007 suite. The main change is an overhaul in the user interface (see an overview of the new UI). The changes look great for someone new to wordprocessing, but for someone who already knows how to do everything in the current version of word, rather than buying the new Office 2007 suite, they might instead choose to use the free OpenOffice suite (which has a similar interface to Office 2003).

But Google seems to have other ideas... Writely (a browser-based word-processor) has been recently released as a beta by Google (they bought it around 6 months ago). Try it out and see what you think... you can save as word, pdf, share your documents etc., all for free. Of course you can't use VBA or other more advanced features, but for writing a normal document it gets the job done well. For collaborating on a document with others, it's unbelievably good!

What about Excel? A few months ago, Google also released Google Spreadsheets...

So the battle seems to be between online, browser based applications (that are free), vs the new Office 2007 series with a great new UI... Which do you think will win? Or is there a place for both?

Your own personal Loan Application!

Who could have known that you were running an EasyCashLoans business on the side while studying at TAFE?? Today's first activity is designed to re-cap most of what we've been learning so far with our Excel VBA, such as:

• Using forms to get user input onto the worksheet
• Checking the users information before entering it on the worksheet (input validation)
• Using VBA to find the next empty row to enter our information.

I'm keen for people to have enough time today to catch-up on some of the previous exercises (quite a few people have been away!), so you might want to chat with me first before starting this new one!

The scenario:
After a few successful sales on E-bay last year, you've been able to loan small cash advances to friends and family for the past 6 months, in exchange for some item of value that you can pawn if the loan isn't repayed. (Nice friend you are ;-) )

The task:
We'll be working together to develop an application to help you track your loans as your business grows. I've already done all the mundane stuff for you (creating a few forms), you'll find instructions for the rest in the actual worksheet. We'll be building on our previous learning this week by adding proper input validation to our forms, and creating a search function for your loan application, allowing you to find your customers as your business expands!

If you're working on this before class (I sent out the file previously via email) and you get stuck, try having a browse through the tutorial Create User Forms in Microsoft Excel or Mark Thorpe's Excel VBA Beginner Tutorials and see if you can get some help online!

And if you find this all too easy, have a go at adding a form to help you calculate loan repayments (using the VBA pmt() function).

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 our Student Results Excel application - we'll gradually build this up with more functionality (such as making sure a number is entered, or putting the focus back in the right field for the user).

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

Debugging your programs

We'll take a brief break from the computers and head off into the demo room to learn a few tricks for debugging our programs as well as the future of Excel?!

We'll be learning:

• Why we should always use Option Explicit (and how to setup vba to force us to do so!)
• How to debug programs using breakpoints and/or the Immediate window
  

Getting data from your form to your spreadsheet

Last week we had a play with creating our own customised forms, with text boxes, comboboxes, labels and other options... but one question that many people asked was "How do I get the info from the form and into the spreadsheet?" That's the first thing we'll see this week!

Open up your StudentResults Spreadsheet that we started last week and:

• Go into the vba editor
• Edit your userform (it should be called frmStudentDetails or something similar)
• Double-click on the OK button to add a click-event for your OK button,

Add the following code to your OK button's click function:

• Cells(1,1).Select
• ActiveCell = txtFirstName 'Or whatever your field is called
• ActiveCell.Offset(1,0) = txtLastName
  

Test out your form to see what it does... you'll find that it's not exactly what you had planned. See if you can modify the code so that the info from the form goes into the right cells!

Now we've got one remaining problem... our data is getting from our form to our spreadsheet, but it always going into the same row... how are we going to solve this?

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 a decent tutorial 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!

Note: The advanced filter in excel causes lots of people headaches later on, when they haven't properly understood what it's doing... take the time now to go over and over the examples so that you feel confident using it!

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
• State
  
• 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 for Total and Final Grade work correctly.

Once you have your formulas working, 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! Note the special prefix... other standard prefixes are:

• cbo for Combo boxes (ie. cboState)
• lbl for Labels (ie. lblFirstName)
• frm for your forms (ie. frmStudentDetails)
• cmd or btn for Command Buttons (ie cmdOK or btnOK)

For those who get ahead, there's two extra tasks:

First, create a macro that will simply display your form... the only statement your macro will need is:

• frmStudentDetails.Show

Then add a button to your spreadsheet that will call your new macro and display your form. Test it out to make sure it works!

Second, 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. Try adding the following example code to your OK button:

• Cells(2, 1) = txtFirstName

Try it out!

Assignement 1 (Alice) Due on 1st September

Just a reminder that as agreed, we'll demonstrate our first assignments in Week 7, which will be Friday the 1st of September (three weeks from this Friday).

We'll all have the opportunity to have a go of each others Alice programs before handing them in!

If you haven't yet completed the Alice Activities (up to activity 6), please try to have this done before tomorrow so that I can help with any problems etc.