Saturday, 21 August 2010

An introduction to Excel VBA (Macros)

The purpose of my next few blog posts will be to go over the basics of the Visual Basic for Applications (VBA) programming language that comes within the Microsoft Office suite.  VBA is a programming language aimed at ‘normal’ people, it’s written in a style similar to normal written English. 
Programming with VBA in Excel means that you can instruct Excel to automatically do things that you would normally do manually — saving you time.
This blog post will concentrate on the Visual basic Editor (VBE). The VBE is the tool you will use to write your VBA macro code and create userforms.  Macro code is the instructions you are giving to Excel to follow, userforms are what you use to allow the user to interact with the macro.
Below are some screen shots of the VBE and notes to explain what each of the sections are for.  You can open the VBE by pressing Alt+F11 (this will switch between the VBE and Excel).  In Excel 97 through 2003 you can also use the command bar menu; Tools-Macro-Visual Basic Editor.  In Excel 2007 you will need the ‘Developer’ ribbon turned on, and press the ‘Visual Basic’ button.

The VBE (Visual Basic Editor)

Visual basic Editor

The Project Explorer

VBE Project ExplorerThe Project Explorer displays all of the workbooks currently open in Excel (including Add-Ins and hidden workbooks).  A tree diagram is used to display the objects under each project (worksheets, userforms, modules and class modules).  Modules contain the macro code.  Class Modules are covered in another blog post which can be found here.

The Code Window

VBE Code Window The code window is where the macro code is written / displayed.  All of the objects in your project will have an associated Code window.  To open them double-click the object in the Project Explorer window to bring up the Code Window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project Explorer window. Unless you’ve added some VBA macro code, the Code window will be empty.

The Properties Window

VBE Object PropertiesThe properties window allows you to amend the properties for all of the objects in your project.  The example above is for a Sheet1, it is more commonly used to edit the properties of userforms and controls (buttons, list boxes, combo boxes etc).  For the worksheet above you can change the visible property, disable cell selection etc.

Friday, 20 August 2010

Excel 2007 Developer Ribbon

To do any sort of VBA coding within Microsoft Excel 2007 it will prove useful to turn on the Developer Ribbon.  You can use Alt+F11 to switch between the VBE and Excel, but having the Developer Ribbon turned on will make life easier.  The Developer Ribbon is like the Visual basic toolbar in Excel 97 through 2003.

To turn on the Developer Ribbon you will need to click the Office button Excel_Office_Button , then select Excel Options, bottom right of the menu.  Within the ‘Popular’ category select the ‘Show Developer tab in the Ribbon’.

Enable Developer Ribbon Once this is checked you will see the following controls now available within Excel.

Developer Ribbon Buttons