Ten Practical Tips for Using Excel VBA Macros in Financial Models

http://www.financialmodelingguide.com/analytical-tools/excel-vba-macro/

At the most basic level, the Excel macro function is used to automate repetitive and commonly executed Excel computations or tasks by recording keystrokes and functions. Many financial analysts have also found that taking the effort to study the effective use of Excel macros and Visual Basic for Applications (VBA) helps them to fully utilize the powerful features of Excel for logic and computation techniques in financial modeling, financial analysis and financial mathematics.

We have compiled a list of 10 basic, practical pointers that financial analysts should be aware of to be successful in using Excel VBA macros to their advantage:

* Excel VBA macros should not exceed 10 to 15 lines of code. If you find that your Excel VBA macro exceeds that limit, break your code up into different blocks (i.e. different Excel VBA macros), and link them together by calling them from each sequential block of code. By following this discipline, your logic and computation code will be kept clean, and make your Excel VBA macro audit or debugging process less painful.
* Always take a staged approach when writing an Excel VBA macro. First program an Excel VBA macro that addresses your basic computation task, test it and get it working, then add incremental features one at a time to extend its functionality. Rinse and repeat where required.
* Use the single quote often to insert comments directly into your Excel VBA macro code to document your programming logic and formulas used. As with building financial models, keeping proper and complete documentation of your Excel VBA macro will not only help you keep track of the intricacies of your Excel VBA macro, but also help members of your financial modeling team or other users quickly identify and understand your work.
* Always hit the reset key after you have fixed a mistake in your Excel VBA macro and before you re-run the macro, otherwise the mistake in your Excel VBA macro will repeat itself.
* If your Excel VBA macro reads a number input as text (i.e. Type mismatch errors), insert the following line into your macro: “x = x + 1 -1″ – This reminds Excel that the input is a number and not text.
* Note that the common Undo command used frequently in Excel, does not work when programming Excel VBA macros in the Visual Basic Editor.
* Do not hard code a cell address when referencing data or formulas in your financial model to your Excel VBA macro. Instead, define a cell name for the cell address, and insert the name into the Excel VBA macro. This way, if the spreadsheet structure of your financial model changes, for instance when extra rows or columns are added, the cell reference you have defined will dynamically adjust in your Excel VBA macro.
* If you accidentally run an Excel VBA macro that loops into itself and jams your computer, hit Ctrl + Break on your keyboard to stop the Excel VBA macro. If hitting Ctrl + Break does not help, be patient, Excel will normally stop the macro from running after some time if it does not produce a result or loops endlessly.
* Use only alphanumeric characters in naming Excel VBA macros. Be sure to never use spaces, dots, slashes, and other punctuation characters in your Excel VBA macro names. Excel also has several built-in macros of its own, and it will be worthwhile to put a number at the end of your macro name in the naming process to avoid potential conflicts, for example: “loop1”, or “compute2”, etc.
* When assigning an Excel VBA macro that you have programmed to your spreadsheet, be aware that (besides the common form button) Excel VBA macros can also be assigned to any shape or image that you embed into your spreadsheet. By using suitable graphics other than the dull, built-in Excel form button, you can enhance the visual attractiveness and usability of your financial model.