Additional Information:
This video shows you how to replace complicated formulas with simpler VBA code. The main technique employed is to assign cells to a meaningful variable name such as "InterestRate", and construct formulas from those names. It is possible to achieve the same result by assigning cells a range name which raises the question of why we would choose to use Visual Basic.
If we were writing a single formula, the only reason would be to benefit from the greater clarity of spreading an equation out over many lines. Although not covered in the video, you can check the value at each stage of the calculation by stepping through each line of the macro using F8 as an alternative to pressing play. You can then hover the cursor over a variable name to get its value.
The argument for using VBA comes if we wish to repeat the calculation for a number of different cells. We introduce the variable RowNum that enables us to re-calculate each row in turn and this puts us only a small step away from automating all the calculations. If we use the Worksheet Change event (covered in Module 3), we can make Excel update the table each and every time the sheet gets updated.
Even so, it is not hard to drag a formula down the entire column of the above table. Therefore the arguments for using macros as a substitute for formulas are that macros allow us to selectively re-calculate certain cells when certain changes are made. Large spreadsheets containing lots of formulas often run slow because Excel will re-calculate all formulas each time any change is made.
The key objective of the module is to familiarize yourself with the Visual Basic Editor. Essentially, it is a text window. This means you can use cut, copy, paste, find and replace data. This can be extremely useful because there are only a limited number of commands, so a lot of lines end up looking very similar to one another.
If you have already seen a few macros, you may be surprised to see that we are making up variable names as required. Most coders would define all their variables at the start of a script; indeed, it is good practice to do so. In Visual Basic, these declarations take the form "Dim VariableName as Variant". If Excel highlights your undefined variable names when you attempt to run a macro, you will need to uncheck "Require Variable Declaration". This can be found in the Visual Basic Editor under Tools->Options->Editor->Code Settings.
