Thursday, 22 October 2009

Viewing macro code

You'll need to be able to print the macro code - the actual VBA code. This proves you've actually done the macro and not just drawn a button and pretended to do it!!

This is easy: go ALT + F11

This will open the macro thingy. You may need to click on the individual sheets and/or modules down the left side of the screen to see all the code. You can then copy and paste into a word doc (or screenshot if you need to). You can simply close this window down to go back to your spreadsheet any time you want.

Recording a macro

OK, macros simply automate a routine process. Pretty much.

Start by setting up the macro to record:

Tools > Macro > Record new macro

Now give the macro a suitable name - something like clear_sheet (no spaces!!)

Hit OK - everything you do now is recorded as part of the macro. This will simply repeat everything you do each time the macro is run by hitting the button.

For example, you might want to delete all the cells in the sheet. Simply click in the first one, press delete, click in the second one...

Make sure you click at the end in the cell you want to leave the cursor in.

Finally hit the Stop Recording button (or go Tools > Macro > Stop recording)

Now we need to create a button to run the macro. You'll need to be able to see the Forms toolbar first of all - go View > Toolbars > Forms to do this.

Draw a button (the grey button shaped icon may be a good guess here...)

You'll then be able to choose the macro to assign to the button - so I'll add clear_sheet to it.

To finish off you can format the text on the button (Button 1 isn't the most helpful name in the world...).

Then whenever you hit the button you should get the macro being repeated. This makes stuff a lot easier for the user - they can do stuff a tthe press of a button rather than having to think about it. This is (probably) a good way to meet some client needs.

Macros, macros, macros!

First thing you need to do is change your security settings in Excel.

To do this on your home machine:

Tools > Options > Security Tab

Hit the macro Security button

Set this to Medium.


Wednesday, 7 October 2009


Given the people didn't seem to know what it was the other day: try this BBC article, especially given the spate of hotmail and gmail phishing problems.