Weekly VBA warmup!

OK... ready for this weeks VBA warmup?? Try creating subs for these two problems below (using the InputBox and MsgBox functions, together with a mystery function that you'll have to find!

1. After Mark Latham's recent outbursts, the Labor party has decided to remove the letter 'l' from the alphabet and by replacing it with a 't'. Create a new Sub called LathamsLegacy that will prompt the user to enter text, and will return a MsgBox displaying the same text, but with every 'l' replaced with a 't'.
2. Before the sale of Telstra, Sol is keen to rename the company back to it's previous name of Telecom. Create a new Sub called Telecom that will modify any text entered by the user by replacing the word "telstra" with "telecom".

Working on Assignment 2

If you finish the previous two exercises (this weeks VBA warmup, and the Pivot Table tutorial), then I'd really like to see people working to finish their 2nd Assignments (or working through the 1st Assignment if you're running behind).

Please use your time wisely!

If you happen to be ahead, I'm planning next week to start with an activity creating our Yahoo Movies database design that we came up with last week, in Access - so feel free to get started. We'll want to create an interface that allows the user to select the movie they wish to see, and this will run a query that then displays all the locations and details of the cinemas where the movie is showing!

This weeks VBA warmup

Lately we've started familiarising ourselves with a few VBA functions such as Date(), Month(), and MonthName() (can you remember what they do? If not, look them up again or look at your examples!). Each week from now on we'll have a quick exercise that will introduce one or two VBA functions.

Note, you don't need to remember these functions! The point of these exercises is to learn to look up functions that you need and figure out how to use them from any source of help you can find! ...

Keep the Visual Basic Scripting Functions page open in a separate window while you try the following in your VBAExamples worksheet:

1. Create a new Sub call Capitals that will ask the user to input some text (using an Input box). When you've got that working, modify your function so that after the user enters the text, it responds with a MsgBox with the exact same text but all in upper-case (capital letters).
2. Create a new Sub call Reversi that will ask the user to enter in some text, and will respond with the text completely reversed! For e.g., if the user types in: Hi there!, your Sub will display: !ereht iH, in a MsgBox.

Of course, please help each other out when you get stuck!

A database for Yahoo Movies

Do you ever find that, when you want to see a specific movie, you have to check all the cinema ads to find where it is playing? This isn't such a problem for the block-busters, but for smaller arty films it can be quite difficult to find out where you can see a film!

Imagine that we're being paid big bucks by Yahoo! Australia to design a database system with a difference. Rather than the normal listings in the paper that are sorted by Cinema first and then by movie, this one will allow us to also create a list showing, for each movie, all the cinemas where the movie is showing!

We'll divide into groups of 3, and it'll be up to your group to decide on what fields you need, and how to design your tables. Part of what we'll discover together is what's known as database normalization!

Alternatives to MS Office?

For the bulk of our course, we're focusing on using the great set of applications known as Microsoft Office. Currently this is the package that most businesses use... but ever so slowly competitor products are making their presence felt out there! When we go for jobs and are asked what applications we're familiar with, it may be helpful to demonstrate an awareness of other Office applications!

To get us warmed up this week, spend 5 or 10 minutes seeing what you can find out about two other Office application suites (other application suites with equivalent functions to Excel and Word etc). Jot down on a piece of paper any pro's or con's that you can note from the products your find. This might include price, or whether it can open Word/Excel files.

We'll get together to discuss the differences... hopefully some time soon we'll be able to try them out too!