Getting Started with VBA

As previously mentioned, VBA is a language that is available to use in a number of Microsoft Office applications, including Microsoft Access and Excel. The information that follows here will concentrate mainly on Excel, although a lot of it will also work in other applications. There are aspects of VBA that are specific to Excel, for example, dealing with workbooks and worksheets, however, things such as looping structures and ‘if’ statements, used in decision making, work across all applications where VBA is available to use.

When working with VBA in Excel it is necessary to use the ‘Developer’ ribbon menu, which isn’t visible by default. Enabling this may vary, depending on the version of Excel that is being used, however, in more recent versions it can be enabled by going to ‘File’ and then ‘Options’. From here select ‘Customize Ribbon’, check the box next to ‘Developer’ in the right hand pane of the window and click ‘OK’.

One of the places that VBA code can be run from is via the clicking of a button. Buttons can be used, for example, to format a spreadsheet in a particular way. A button can be added to an Excel worksheet via the ‘Insert’ option on the ‘Developer’ ribbon menu. As a demonstration, a button will be used here to display a message in a dialogue box saying, ‘Hello world!’.

Upon selecting a button from the ‘Insert’ option on the ‘Developer’ ribbon menu, a mouse click on the worksheet in the desired position will bring up a dialogue box to assign a macro. A macro basically allows you to specify a series of steps to be performed when a particular action is taken, in this case, clicking a button. As well as writing VBA to create a macro, a record of steps taken to format a worksheet can be taken, which automatically generates VBA, however, writing the VBA code provides greater flexibility.

A name can be entered for the macro. It is a good idea to give a meaningful name, rather than leave the default suggestion. At the bottom of the dialogue box it is possible to specify where the macro should be available. When a name has been entered and the desired options chosen, click ‘New’. A new window appears where VBA can be added to the macro. The outline code for the macro will automatically be created. Note that the name after the word ‘Sub’ and before the parenthesis corresponds to what was entered in the previous dialogue box. Any VBA that needs to be executed when the button is pressed needs to be between these two lines of code.

Sub Demo_Click()

End Sub

In the example below, a line of code has been added to display a dialogue box that says, ‘Hello world!’ when it is pressed.

Sub Demo_Click()

   MsgBox ("Hello world!")

End Sub

Once this line of code has been added you can go to ‘File’ then ‘Close and Return to Microsoft Excel’. Back in Excel there should now be a button displayed on the worksheet. Note that when saving an Excel spreadsheet that contains macros, the ‘Save as type’ must be ‘Excel Macro-Enabled Workbook’.

When the mouse cursor moves over the button, the cursor should change. If this doesn’t happen, click somewhere else on the worksheet and then move over the button again. Upon clicking the button, a dialogue box should appear with the message ‘Hello world!’ displayed, together with an ‘OK’ button to dismiss the box.

It is possible to edit the text of a button, with a right mouse click on the button, which brings up a menu, where one of the options is ‘Edit Text’. After right clicking on a button, six handles appear around its border allowing it to be resized. Hovering over the button border, other than over these handles, will change the cursor signifying that it is possible to click and drag the button to a different position.

To edit the VBA for the button, the ‘View Code’ option can be selected on the ‘Developer’ ribbon menu. From here, on the left hand side a module will have been created when the button was initially added to the worksheet. This is where the VBA for the button resides.

Code Commenting

When programming in any language it is useful to add comments to the code to describe what is happening and why. This is useful when the code is revisited at a later date to make enhancements, or for debugging purposes. In order to add a comment in VBA, a single quote needs to be added at the beginning of the comment. This is shown with the text in green below.

Sub Demo_Click()

   ' Display a "Hello world" message.
   MsgBox ("Hello world!")
 
End Sub