When using VBA extensivly it is a good idea to know how to use VBA. VBA means Visual Basic for Application and can be used to automate functions that are hard to achieve using simple functions. VBA uses the same syntax as Visual Basic and is quite simple to use.
To write a VBA application you need to open the Visual Basic Editor (VBE) which you do by pressing ALT + F11. Choose Insert => Module in the menu to create a new module.
Enter the following code:
Sub Test() 'Select cell C1 Range("C1").Select 'Write "Foo" in the selected cell Selection.Value = "Foo" End Sub
If you press run (either F5 or press the green triangle icon in the icon tray) you’ll see how the module has written Foo in cell C1. So, you ask, do you have to run modules from inside the VBE? Of course not, that would be dull. If you get back to your excel sheet there are differences to running VBA script depending on which Office pack you are using. I’m using Office 2007 so I will show you how to do it in Office 2007, however, if you are running 2003 or perhaps an even newer version then it shouldn’t be too different.
In Office 2007 press the big office button in the top left corner (where you go if you want to save or open a file) and choose Excel Options in the bottom right of the menu. In the options Popular tab check the “Show Developer tab in the Ribbon“. You might have to change your security settings in the Trust Center to get it to work.
This has now opened the Developer tab and if you go to it you should be able to select your macro by pressing the Macro-icon:
If you press Run the script will execute and you’ll see the effect in cell C3. You have now written your first VBA script that does something!