Spreadsheet #20:  Macro Programming #1 - Identification of Maxima and Minima

[Back to Group 1 Assignments] [Back to Chem3020 home]

"Macros" are series of instructions given to spreadsheet, word processing, and programs that run laboratory instruments.  Macros are often merely a series of key- or mouse-strokes that can be recorded as a "macro."  Then, the macro program can be run over and over whenever the user wants to save effort and/or time.   In almost all modern software, a series of keystrokes can be recorded by turning on the macro recorder.  (Click: Tools > Macros > Macros… or Record New Macro...). After the macro is recorded in a separate window, it can be edited and then saved with this spreadsheet or to a special Excel file to store macros for use in other worksheets. Macros can also be assigned a button on the screen, that when clicked will run the macro, or even assign it to a [control] key, so that by pressing [Ctrl] [key] it runs the program (e.g., [Ctrl] "g"  could create a graph.)  Read the information about recording and running macros in your specific spreadsheet software.

Load the data from the last spreadsheet, #19, include the % moisture calculations, but do not include the "IF" statements.  Resave the sheet under a new name before beginning.  (As a good practice, save your macro programming and the new spreadsheet before running any macros. Macros can often ruin a spreadsheet... so always make a backup file before running the macro!)

Turn on the macro recording function.  Then, block your numerical data, including one extra column just to the right of the % Moisture column.  Sort this entire blocked area in ascending order (% Moisture column) and type "<--Minimum Value" in the cell just to the right of the minimum value.  Resort the same blocked area in descending order, and type "<--Maximum Value" to the right of the maximum value for % Moisture.  Resort the same blocked area in its original testing order.  After completing this task, turn off the macro recording function.

View the macro in the viewing area.  Edit if desired, and save the macro.   Return to the spreadsheet and delete the maximum and minimum labels.  Run your macro to see if it functions correctly.  If not, edit the macro to make it work, resave it, and print a hard copy of the macro and a copy of the output it produces.

To edit a macro or see it, select Tools > Macro > Macros…. (or press [ALT] [F8]). Select your macro that has been previously saved and click on [EDIT]. This should bring up a new window with your macro in it, written in Visual Basic code. Copy and paste it into Word to print it. On some systems, the macro programming option must be installed before you can use it to run or edit macros.

Questions to Answer:

1. Could you run this macro on a new data set with the same number of data points?  Explain how to do this.

2. Could you run this macro on a new data set with more data points than you wrote the macro to handle?  Explain the problems associated with this situation and, if needed, propose a solution.

3. Why sort the data set twice?  Could the same thing be accomplished with only one sort?  Consider the situation described in Questions #2 as part of your answer.

4. Explain the importance of cursor location during the execution of a recorded macro.  What would be a good initial programming step in any macro before moving the cursor to a desired location?