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

The days activities!

After a brief recap of last week, we'll start off today with an exercise that will help us remember some of the more advanced functions of Excel (such as named Ranges, VLookups and Absolute vs. Relative referencing)! You can find the exercise spreadsheet on the S:... Remember to use each other as a learning resource... and ask questions!

We're then going to have a go at importing data into Excel, seeing the difference between delimited text files and fixed width text files, before having a go at our second activity for the day (from our recommended text).

Hopefully we'll still have time to record our first macros and learn a bit about VBA... we'll see!

Making that turtle move!

It's been a week since most of us had our first go at programming... so it's time for a quick refresher (probably while we're waiting for everyone to turn up ;-)

Scroll down to Computer Programming for fun and see if you can draw the square and triangles again.

Last week we started seeing how repitition is pretty powerful for us when we're programming! This week we'll see how we can create our own functions, such as 'square' or 'hexagon' that will draw a square or hexagon.

Starting your first assignment!

As mentioned in class, our first assignment aims to build on our knowledge of MS Access from Cert II - learning more about customising applications in Access with Forms, Macros and menus. To do so, we'll be following the textbook: Advanced Applications using Microsoft Access 2003.

This will get us all ready for the third assignment where we'll be designing our own database application from scratch!

The text has a whole bunch of exercise files that go with it, which you can download from the Pearson Education site, or grab from the S: (under the Cert III folder).

As I mentioned last week, aim to have chapter 2 complete for this Friday so we can talk together about what we've learned. If you read this email today in class, please give a gentle reminder to any other Cert III students around you!

Computer Programming for fun!

Time to learn a bit about computer programming!! Sound daunting? We're going to start learning the basics of computer programming by commanding a stoopid turtle (or triangle!) around the screen!

Open up the Turtle Graphics WebPage and after the intro message displays, type your first three programming commands (in the command window):

• clearscreen
• showturtle
• pendown

Now try the following:

• forward 50
• forward 100
• right 45
• forward 50

Get the idea? Clear the screen again, and see if you can come up with a program for the following:

• A Square
• A Triangle
• A house
• A hexagon
• A star (tricky!)
• Something from your own imagination!
  

Have a look at the code you created for your square... what happens if you change the sequence of your commands? Try it out!

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! Learning the basics of programming is just learning to solve problems with some special tools. I always like to get started with a brain-teaser... just to exercise our minds a bit.

Other things we need to cover before we get started:

• Join email group
• SAGS
• Overview of Assignments
• Required Text
• Deciding on breaks!

Then we'll get started by flexing our Excel skills with a little revision exercised. Once you've finished the worksheet, add comment below on how you feel about your Excel skills... do you feel confident, are there areas you feel you need to brush up on?