A new version of Windows and Office?

Have you heard about this? See if you can find out what benefits the new Windows or the new Office will bring!

We'll watch two very short videos this morning that'll show us a bit about the new versions of Windows and Office!

Turtle Graphics in Excel?

Surely we can't draw in Excel like we did with our Turtle Graphics?? Well, not exactly... but we can learn how to use a useful Excel object, called the "ActiveCell" ,while trying to draw in our Excel worksheet!

So far we've been using the Cells(row,col) method to interact with our spreadsheet. For example:

Cells(5,3) = txtFirstName

But there are other ways that we can interact with the spreadsheet from VBA. It's often handy to know a different way to do something, because it might make your job easier in a different situation! Instead of the Cells() method, we can also use the Range method as follows:

Sub Test()
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A5").Activate
End Sub

The above code activates Sheet1 and then activates the cell "A5" on sheet 1. One obvious benefit to this Range Method is that it uses the more familiar "A5" notation that we're used to. This is great if we know the exace cell (or named range) that we want to use... but can you think of a situation where the Range method won't work?

One really useful way to interact with the Worksheet is to use the ActiveCell property - it simply refers to whichever cell is currently activated (ie. the cell that currently has the black border around it - usually this is the last cell that you clicked!)

We can set some of the properties of the ActiveCell, such as its colour:

ActiveCell.Interior.ColorIndex = 5

Or, we can move the active cell by offsetting across or down the page:

ActiveCell.Offset(0, 1).Activate

For example, copy and paste the following sub into your vba_functions.xls file:
Sub DrawLine()
ActiveCell.Interior.ColorIndex = 5
ActiveCell.Offset(0, 1).Activate
End Sub

What happens when you run this sub? And when you run it again? And again?

Exercises:

1. Modify the DrawLine sub so that it draws a line down the worksheet, rather than across.
2. Modify the DrawLine sub so that, instead of only drawing one cell each time it is run, it draws a line using 3 cells.
   
3. Create a new Sub called DrawSquare, that uses the ActiveCell property to change the colour of a cell, before moving on to the next cell... eventually drawing a 5x5 square.
4. Create a new Sub called DrawDiamond, that does exactly that!
5. (Tricky) Create a new Sub called DrawHouse and see what you can come up with!
   

Now you're ready to tackle Band Scenario 6!

Fun with VBA functions!

So far we've only been using the built in VBA functions (such as MsgBox, CInt or IsNumeric) as we've needed them. Today we're going to have some fun finding out what else is out there! Check out the list of VB functions at Microsoft's Visual Basic Scripting Functions (right-click and open in a new window).

How many of the functions do you recognise? We'll see how many we can count in class!

Open a new Excel Spreadsheet and save it as "vba_functions.xls". See if you can use this reference to achieve the following:

1. Find a function in the reference that will return the current date.
2. Now find another function that will format your date however you choose (ie. "16/9/05" or "16th September, 2005", depending on your choice). Create a new Sub called DisplayCurrentDate that uses a MsgBox to display your formatted date.
   
3. Find a function that will take a date and return a number from 1 to 12 corresponding to the month part of the date. Create a new Sub called CurrentMonthNum that uses a MsgBox to display the number of the current month.
4. Find a function that will take a number corresponding to a month, and return the name of the month (ie. you give it 9 and it returns "September"). Now use all three functions in a new Sub called CurrentMonthName that uses a MsgBox to display the name of the current month.
5. Now create a new Sub called BirthdayMonth that uses an InputBox to ask the user to type in their birthday. Your sub will then use a MsgBox to respond with the month they were born. For example, if I type: 1/8/75, then the sub will use a MsgBox to say "Oh, you were born in August!".
6. Find a few functions that will allow you to create a new Sub called BirthdayDay. This Sub will ask the user to type in their birthday, and then tell them what day of the week they were born (e.g. "Monday").
   

Probably a good time to take a break, stretch your legs, before trying the next set!

More about Looping

As well as going over the while-loop from last week, if you've got time after catching up it'll be worth seeing what Microsoft's got to say about looping!

Checkout Get in the loop with Excel Macros. You should find that the first two lessons of this tutorial are revision for you, so feel free to skip ahead (after doing the quizes!).

Your own personal Loan Application!

Who could have known that you were running an EasyCashLoans business on the side while studying at TAFE?? Todays 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 (the file is on the P: under 3651 Exercise Files) 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).