Using Macros To Get The Most Out — страница 2

Macro, Record new Macro. Every keystroke is recorded. The recorded macro is automatically converted into VBA. Even the revisions are recorded, as evidenced below. Notice here that the “if” command was required. This nullified the prior formula, however the code is still recorded. After the macro is completed the work done on the spreadsheet can be deleted and the macro applied to the raw data. This is done by Tools, Macro, Play Macro. A screen will appear as follows, and the Average Growth macro is selected. Press Run and the commands are applied to the spreadsheet. The results are the appearance of a new data column “J’ with average growth figures expressed as a percentage. Whilst the record macro function is easy to use, it does have limitations. Most recorded macros

are designed to perform a specific task. Most designers are also the users and being under time constraints take shortcuts. This means that the macros can become so specific they will not be useful in even a slightly different situation. In the example performed above, what happens in the next year where there are six years of trading history? The recorded macro is rendered useless. Writing a macro in VBA can provide a tailored outcome no matter what the data range as the instruction can capture the data range until “blank”, rather than be limited to a prerecorded cell range. Attempts to change prerecorded macros can become incredibly frustrating. There are many commands that you need to learn to program VBA, one benefit of the record macro facility is that it can become a

ready reference for the 300 VBA instructions. In all the VBA component can allow for a myriad of functions that recording macros cannot. There are plenty of texts and courses on writing VBA code. Or for free you can surf the net at www.add-ins.com/tricks.htm. or www.excelvba.com/?source=goto www.beyondtechnology.com/tips.shtml . In short, be more professional by being more accurate, timely and providing accurate data. Use spreadsheeting. Use Macro’s. (1) Beaman I. R., Ratnatunga, J. A., Krueger, P. and Mudalige, N. (1998). Financial Modelling, 2nd Edition, Quill Press (2) Lee, J.C., (2000), Business and Financial Statistics Using Minitab 12 and Excel 97, Singapore, River Edge. (3) Kyd, C. W., Kinata, C., (1992),Complete Guide to Microsoft Excel Macros, Microsoft Press (4)

Kinkoph, S., (1999), The complete idiots guide to Microsoft Excel 2000, Indianapolis Ind, Que. (5) Albright, S. C., Winston, W. L., Zappe, C., (1999), Data Analysis ans Decision Making with Microsoft Excel, Pacific Grove CA, Duxbury Press. (6) Berk, K.N., Carey, P., (1996), Data Analysis with Microsoft Excel, Pacific Grove, Microsoft Press. (7) Wempen, F., Payne, D., (1999), The Essential Excel 2000 Book, Rocklin CA, Prima Tech (8) Perry, G., (1999), Excel 2000 Answers!, Berkley, CA, Osbourne. (9) Walkenbach, J., (1999), Excel 2000 Programming for Dummies, Foster City CA, Transworld. (10) http://support.microsoft.com/support/kb/articles/q177/7/60.asp (11) http://www.surfer.ch/opendir/Top/Computers/Software/Spreadsheets/Excel (12)

http://www.oclc.org/oclc/ill_mgmt_stats/reportmc.htm (13) http://support.microsoft.com/support/kb/articles/q177/7/60.asp (14) http://www.add-ins.com/online.htm (15) http://press.oreilly.com/excel.html (16) http://directory.google.com/alpha/Top/Computers/Software/Spreadsheets/Excel (17) http://www.oreilly.com/ (18) http://www.accountingadvisors.com/ (19) http://www.hqda.army.mil/AAAWEB/auditinfo.htm (20) http://www.mctc.commnet.edu/staff/kelly/ac105.html (21) http://www.npo.net/itrc/courses/spreadsheet.html