Excel spreadsheets are an essential tool for any one. However, many users do not realise that there is a very powerful programming language built in to Excel that will automate procedures, build custom functions and create applications.
This language is called VBA (Visual Basic for Applications) and allows you to write macro programs that will do anything that you could do from the user interface and a whole lot more besides. The program that you write forms part of the spreadsheet but you can turn this into an Add-In so that other colleagues can use it independently of your spreadsheet.
You access the Visual Basic Editor (VBE) window from Excel by pressing ALT-F11. This will take you to a window similar to the one shown below. The Project Explorer pane on the left-hand side shows a tree structure of the objects within the workbooks that are open.
Every sheet within each workbook is considered an object and the workbook itself is also an object. These objects all have a hierarchy of methods, properties and events that sit below them.
To create a simple macro, insert a new module by selecting Insert|Module from the menu in the VBE window. Modules are containers to hold the VBA code.
The main pane of the VBE window will now have a blank module with drop downs at the top showing (General) and (Declarations).
Enter the following VBA code anywhere on the module pane (as if it was a Word document):
MsgBox ‘This is a VBA test’
Notice that when you type in the first line of code, Excel automatically puts in the End Sub line. This is because you are creating a subroutine and it has to have a starting point and an ending point. The line commencing MsgBox creates an action within the code.
Notice that if you type in words such as Sub and MsgBox in lower case, Excel automatically changes the appropriate letters to upper case. This helps you to check that you have used the right command.
Click the mouse anywhere within the VBA that you have entered and run the macro. You can do this by pressing F5, clicking Run|Run Sub/User Form from the VBE menu, or clicking on the green triangle (that resembles a play symbol) on the toolbar.
When the code runs, it will take you to the spreadsheet window and display a message box in the centre of the screen. The message box is the same as many that you see every day in Windows and is a simple way of communicating to the user.
Click the OK button on the message box and your macro will end and you will be taken back to the VBE window. Bear in mind that until you have clicked the OK button and finished the macro user control is still with your code and you will not be able o do anything directly in Excel.
This simple example shows how easy it is to program in Excel. However, simply displaying a message to the user is not really a practical application. Suppose that you wished to display some of the properties of your spreadsheet such as who created it, when it was last saved, and when it was last printed.
The following code is a more complicated macro to do this:
Dim strTemp As String, Prp
For Each Prp In ActiveWorkbook.BuiltinDocumentProperties
On Error Resume Next
If Prp.Name = ‘Author’ Then strTemp = strTemp & ‘Author: ‘ & Prp.Value & ‘ |’
If Prp.Name = ‘Last author’ Then strTemp = strTemp & ‘Last Author: ‘ & Prp.Value & ‘ |’
If Prp.Name = ‘Creation date’ Then strTemp = strTemp & ‘Creation Date: ‘ & Prp.Value & ‘ |’
If Prp.Name = ‘Last save time’ Then strTemp = strTemp & ‘Last Save Date: ‘ & Prp.Value & ‘ |’
If Prp.Name = ‘Last print date’ Then strTemp = strTemp & ‘Last Print Date: ‘ & Prp.Value & ‘ |’
Sheet1.Range(‘a1’).Value = strTemp
The code needs to be entered into the module that you created in the first example.
A tip when entering this code is that you do not necessarily need to show all the properties described here, and you can also use Copy and Paste functions to replicate lines of code.
This subroutine (DisplayProperties) concatenates several properties into a readable string of characters and places it into cell A1 on the first sheet in your workbook.
The first thing that it does is to define two variables using a Dimension statement. The variable strTemp holds the concatenated string and the variable Prp represents the properties within the active workbook.
A variable can be given any name, provided that it is not a reserved word or has a space in it or does not begin with a letter, and it must be under 40 characters in length.
The code then uses a For Each .Next loop to iterate through the BuiltinDocumentProperties collection of the active workbook. The For Each statement defines the start point of the loop and the Next statement further down defines the end point. Notice that a dot or full stop is used to separate the hierarchy of objects. Notice also that the code within the loop has been indented to make it easier to follow.
Using If statements, the strTemp string is concatenated together using text strings and vertical bars to make the resulting string readable. The ampersand symbol represents concatenation between strings. Each If statement tests to see if the property name is one that is required in the program and if it is true then it adds the details on to the string strTemp.
When the iteration has completed the value of strTemp is placed in cell A1 on the first sheet in the current workbook. This will overwrite any value already in this cell.
Click your mouse anywhere on the block of code and run the code as before. Nothing appears to happen, because your code has run very quickly. However, if you return to your spreadsheet window, you will see that cell A1 on the first sheet now has data in it.
You can save your code by saving the workbook as you would normally. However, if you are using Excel 2007 it makes a distinction for Macro-Enabled Workbooks. The first time you save the workbook you must select the Save As Type as Excel Macro-Enabled Workbook
Use these links to buy Excel VBA books on Amazon