MS Excel VBA
Work-in-progress. Updated 12 June 2011
So you need to automate certain steps in your Excel spreadsheet, or enhancing it by introducing a validation check, or perhaps provide a guided user input interface. If you need to such a thing with Excel, there’s one way to go about it – implementing your own macro (or VBA code).
Get to know Visual Basic Editor
In order to get started you need to familiarise with the Visual Basic editor (VBE). This is where you type your VBA code and test them.
- Open a new Microsoft Excel spreadsheet.
- Right click on the Sheet1 tab name and select View Code. This will automatically open the VBE and Sheet1 VBA object window.
[pic]
(1) is the project window, (2) properties window and (3) object’s VBA code window.
In the project window, you should see 4 Microsoft Excel objects listed with the first 3 correspond to the sheets in the workbook. The last object is named ThisWorkbook. When you are more advanced with writing VBA code, you will encounter Module objects below ThisWorkbook. In this guide, I will touch on that.
Something to note about ThisWorkbook is that it will allow your code to be accessible to all sheets in you Excel workbook. The corresponding sheet’s VBA object will restrict the code for that sheet only. This means that if you write a code for Sheet1 in Sheet1 VBA object and you want to apply the same function in Sheet2, you would have to copy the code into Sheet2 VBA object.
Before we write a VBA code, you need to understanding the fundamentals. First, VBA code need to be written within a subroutine. Second, for it to be functional and meaningful, the subroutine need to be one of the Excel VBA Events. Here are the run down of most useful Excel VBA Events.
