First Assignment Due on the 6/05

... that's two weeks after the holidays. Most people should be quite a way along with this already... if you've got special circumstances that you think require a different date, talk to me earlier rather than later!

We'll all get to see each others applications in action on the 6th of May!

Remember too, if you haven't started your second assignment, start it now!! We'll negotiate a due date for this second assignment in class, but remember that you can finisht this one on your own now and get it out of the way... a good idea I would say!

EasyCashLoans!

Who could have known that you were running an EasyCashLoans business on the side while studying at TAFE! After being so successful on E-bay (not thinking of anyone in particular here), 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 ;-) )

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 3652 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! You can always post to your own Blog too and I'll help out too, but I'd rather people get in the practise of finding 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).

Looping with Microsoft

First things first! We've been meaning to do the Microsoft VBA Tutorial for a while now, but have never gotten around to it! So we're going to do it first thing today! This tutorial will build on what we know and open up the possibilities of what we can do with For loops.

I'm not sure how long it will take, but it is divided into three separate lessons, so make sure you take a break between each one!

When you've finished the tutorial, be sure to summarise any new learning on your blog for later reference! Ask me how you can put a link in your blog entry so you can remember where the tutorial was :)

Later-on, we'll take a less-stressful stroll through importing data into Excel from other sources.

An excellent book for class!

Just found an excellent book that covers all of what we are doing in class (thanks to Colin Tyrrell for the tip-off). The book is called Advanced Applications using Microsoft Excel 2003, and has actually been written by the TAFE South Australia to cover the exact modules that we are doing in class!

I've just spoken to Pheobie at Megalon Books (4784 1302), who said that they can order it in at $44.95. Since we've already in week 6, I'm not going to require people to buy it, but it's definately a worthwhile investment as it covers exactly the things that we need.

We'll actually do some activites from the book over the next while, which will give you a chance to see whether you think it's worth the money!

A brief history of everything!

We've been learning a lot of stuff over the past 4 weeks! I'd really like everyone to take a little while to reflect on what you've learned, how confident you are feeling, whether you feel you need to spend some more time on certain aspects before moving on etc. Here's a summary of the past 4 weeks:

Week 1
In our first week, we recapped our Excel skills with an activity about Nepean/Penrith District Soccor club. This gave us a chance to re-familiarise ourselves with formulas in Excel, creating Charts in Excel, formatting numbers etc. etc. If you have finished this since week 1, make sure you let me know as I've only got 5 people as having competed this activity.

Week 2
In week 2 we recorded our first Macro by following the first part of the the tutorial Excel VBA: No Experience necessary -Lesson 1. This tutorial introduces the VBA editor in Excel, recording macros, and then goes on quite quickly to introduce Loops (something we only began looking at last week). It would be worthwhile going back over this now as we've learned enough to get up to example 3 in the tutorial.

We then took a break from VBA and looked at data validation in Excel. This enabled us to help our users enter valid information with Excel's built in Data Validation tools. To get an overview of what we did, check out the introduction to data validation from Microsoft and then go over your own StudentRecords spreadsheet.

Some of us also got to know the basics of computer programming through AJ's Turtle Graphics application. We saw how we can write computer instructions to draw a square on the screen, and then extended that to draw other shapes.

Week 3
We quickly recapped a few things from the previous week (recording another macro, doing some more data validation) and then picked up where we left off with our introduction to computer programming. This time we saw how powerful repitition can be by turning our simple squares into beautiful flowers like the one shown here.

We then got to know the most useful function ever written - the MsgBox function. while also seeing how every VBA procedure that we write needs to start and end with some special bits, like this:
Sub Greeting()
MsgBox "Hi there"
End Sub

On top of all that, we also tried out the InputBox() function... but that only really showed us how frustrating it would be for people to enter information if we used the InputBox() function... there has to be a better way! And there was :) We then delved into creating our own customised forms for our Excel applications! Wish I had a picture of one to put up here! But I don't! The last thing that we did in week 3 was getting the information from our form onto the spreadsheet... but there was a slight problem with our solution, can you remember what it was?

Week 4
As per usual, we started with a bit of revision, creating a small Excel application like a computer hardware inventory for a store. We set up our spreadsheet then designed our own custom forms, we even added an About button that told the world how cool we were for creating such a useful application ;-), but every time we entered new info it always ended up on the same line, cause we were writing code like:
Cells(2,1) = txtFirstName

Hmm... Lynne helped us out here by coming up with the idea of making our program to move down the spreadsheet checking each row until it finds an empty one, and then adding our new information! So that's what we did! Once we'd gotten it working for our HardwareParts application, we then went off and tried to apply the same solution to our StudentRecords application.

The present...
Not bad for 4 weeks huh? If you were away, you might not have covered all this yet, but that's ok, I'm keen for you to play catchup today! And, if you feel that all of this is seeming a little bit foggy, YOU'RE NOT ALONE! It's a natural part of learning - if you keep applying yourself and trying, the fog will clear!

So, time to write up in your own blog where you're at! Try to comment on anything that you need to go back over to finish, or just to revise, as well as the things that you feel confident about.

If you're all up-to-date with your activities (make sure you show me!), then start the excellent Microsoft tutorial that will recap some of what we've done, as well as introduce some new things! It's in three parts, so make sure you take a break between each! And write up a new entry in your blog describing how you fonud the tutorial (i.e. too easy, too hard, what was useful etc.)

Finally, if you finish that, we can start working on the first assignment! Remember, if you're not into Used Cars, feel free to talk to me about an alternative subject for your assignment that will demonstrate the same skills! (Ask me for a marking criteria, as I'll forget to print it out).

Going Looopy trying to get data from a Form!

We've already seen how powerful repitition can be for us programmers... to jog your memory again, open up the AJLogo Turtle Graphics Application in another browser window (remember, right-click then Open in new window). See if you can remember how to create a function called flower! (Hints: you might want to first make a function called square, and then use the repeat command to make your flower function).

We're going to use repitition in a similar way to enter new information from a form into our spreadsheet! Our problem is that everytime we press OK on our form, the information gets entered onto our spreadsheet on exactly the same row! How can we get around this? We'll work out a solution in class together! This'll also give us a chance to find out what the VBA debugger is :)

Once we've got our solution working for our HardwareParts application, we'll close this worksheet and test our solution on our StudentRecords application. Now we're ready to start our first assignment!

To recap some of the ideas we've been learning (and learn a few more!) check out the excellent tutorial provided by Microsoft called Get in the Loop with Excel Macros.

Revision Exercises Week 4 Programming

The first thing we'll be doing this week is creating a small Excel application to store information about computer hardware. Create a new spreadsheet and save the file as ComputerHardware.xls.

We'll first need to decide together on what information our application should store. It's really important that the information entered into our application is correct, as our customer is using it to update the prices on her website each month, so we're going to create our own customised forms to make the data-entry as simple as possible!

Our form will need to have input controls (fields/radio buttons/dropdowns etc) and labels corresponding to each field on our spreadsheet, but this time we're going to use a special naming convention to name our controls and labels.

When the user clicks on our OK button, the information from our form should be entered automatically onto the spreadsheet (well, at least the text boxes for a start!).

Finally, our form also needs to have an 'About' button which, when clicked, will display a message box telling the user that we created the program and a contact email/web address, for example:

This application was created by Jo Harvey. http://customapps.blogspot.com

But there's still a small problem with this application... any ideas as to how we might fix it?