BASIC has more than 20 years of history as a computer programming language. The name says it, it is “basic” and it is something like speaking with your computer. Later on BASIC evolved into Visual Basic, which you can use to design interfaces with your mouse. Meantime, Microsoft tailored Visual Basic to work with its Office suite and called it “Visual Basic for Applications”, which VBA stands for. In this article, we will touch the basics to give you a small step in getting going with your VBA programming experience. Even just a little knowledge of VBA can give a real boost of efficiency when working in Excel and other Office applications.
Opening the VBA module is the same in all Microsoft Office programs, but I will choose Excel because we will have as much flexibility as we want with the calculations.
Let’s go step by step from opening a blank workbook. Open up Excel. If you did not play with the preferences, you should have Sheet1, Sheet2 and Sheet3. You can start the VBA module by going through Tools -> Macro -> Visual Basic Editor or simply hitting Alt + F11. Now, we have our worksheet open and our code window. As you see, the window is split into three: at the top there are the menu and icon bars, on the left your VBA Project and on the right the code window. On the left pane you see “VBA Project(Book1)” and then “Microsoft Excel Objects.” Our default worksheets are listed as “Sheet1(Sheet1)”, “Sheet2 (Sheet2)” and “Sheet3 (Sheet3).” On the right, at the top you see two dropboxes: (General) and (Declerations). We will get to each one in a minute.
You do not need to stick with the default naming scheme of course. If you rename your file (your Excel workbook) to something else, such as “My_Workbook” and save, you will see that on the left pane VBA Project(Book 1) changes to VBA Project(My_Workbook). The same way, if you rename the worksheets, the changes will be applied to the Sheets under Microsoft Excel Objects. Let’s rename Sheet1 to Calculations, Sheet2 to Detailed_Reports and Sheet3 to Summary_Report. On the left pane in Visual Basic Editor, you will see that Sheet1(Sheet1) has changed to Sheet1(Calculations), Sheet2(Detailed_Reports) and Sheet3(Summary_Report.) However, if you switch back to your workbook and change the order of the worksheets, for example move Summary_Report to the left of the Calculations, the Sheet1, Sheet2 and Sheet3 notation will not change. The notation has an advantage: you can refer to the sheets as you name them. You do not need to say “Sheet1” in your code, go with “Calculations.”
Regarding the two dropboxes, there is such a thing as declaring variables in programming. I will try to keep things as simple as possible here, and open things up where necessary, to avoid losing you in the article. There are two types of variables: global and private. Global variables will be available in all modules in the code, whereas Private variables will only be available in the module it is defined under.
Also, there are the Sub and Functions. These are the two sections of code that perform a specific action. Function returns a value and Sub does not. We will come back to that later.
Read on for more details on Global Declarations, our first excel VBA application and making calculations.
