INAIR 2015 “Visual Basic Programming in Microsoft Excel”.doc

INAIR 2007 “Visual Basic Programming in Microsoft Excel” Introduction NRC Faculty Lists.xls This is an example of an Excel workbook that needs to have its nearly 50 sheets parceled out into separate files, since you need to email these to individual departments for review. You would like to automate the process of coping the sheets, saving them to new workbooks, and assigning the new workbook name. Following instructor discussion, you will: Record a macro (a VBA program) to produce one of the needed new workbooks. Open your program in the VBA editor and explore the VBA editor environment. Run and test your program. Edit your code, if needed. Remove unneeded code. Add comments to your code. Enhance your program to produce all the needed new workbooks. Rename subroutine and module. Add a button/autoshape to the first sheet to run your program. CS Evaluations.xls This is a report generator, enabling this Excel workbook to generate an entire series of evaluation reports at the touch of a button. It uses an optical mark reader data file that contains the evaluation data from a series of community services courses. This program cannot simply be recorded and was hand written. Following instructor discussion, you will: Open the VBA editor and practice accessing the VBA help files (F1 key). Explore the caps feature for properly formatted code and auto-selection lists provided by the VBA editor. Add a button/autoshape to run the program. Your file differs a little from the instructor’s. See if you can edit your code so that the program works correctly (and don’t just move the data!). Relative Range.xls This bit of code is designed to do any operation you desire on any selected range of cells on any Excel workbook you choose. It can be part of a VBA library you can always have handy while you are working in Excel. Following instructor discussion, you will: Add a line of code to convert the financial data to thousands of dollars. Add code so that empty cells are ignored. A