ENV* K105

Assignment #8--Macros
Due 11/08/05

In this assignment you will learn about Macros as described in Chapter 21 of Using Microsoft Office 97 (see handout). A macro is a series of commands that an application carries out automatically. In Office 97 (and later) applications, macros are written in a computer language called Visual Basic for Applications (VBA). This is actually the same as the programming language Visual Basic (VB). Fortunately, you do not need to know any VB to actually record and use macros. Nonetheless, the more you know, the more powerful, flexible and reliable your macros will be.

1. Download assignment8files.zip (click the link; save the file to disk, then double click it to open. Extract the files to a USB drive, your I:\ drive or another folder). Read all of this assignment.
2. Before you can run any of the macros I wrote, you need to decrease the Macro Security level to Medium. Open Excel. Choose Tools, Macro, Security.... Select the Medium radio button and Click OK. Close Excel.
3. Read Chapter 21 (in the handout), working as many examples as you can.
• The spreadsheet in Fig 21.1 is already started for you (fig21.1.xls).
• Don't get too upset about "The Excel Object Model" described on pp. 454-463. If you get something out of it, great; if not, ok.
• Try the experiments on pp. 464-472. That material is fairly heavy going, however, so don't drive yourself up the wall if you can't understand all of it.
4. Create a neat little calculator starting with the Weak Acid pH Calculator you made last week.
• Record a macro:
• Open the file weakstart.xls. This is the calculator you created, spruced-up just a tiny bit.
• Under Tools, choose Macro, Record New Macro...
• Name the new macro "Recalc". Assign it the shortcut key Ctrl Shift R (just type a capital R in the box). Change the description so that you get credit for recording the macro. Click OK. Anything you do in Excel will now be recorded until you hit the stop recording button.
• Carry out the steps you would normally do to run Solver.
• Choose Tools, Protection=>, Unprotect Sheet.
• Choose Tools, Solver.
• Set Solver Parameters:
• Set Target Cell: $F$17; Equal to: Value of: 0
• By Changing Cells: Hion
• Subject to the Constraints: (Click Add) Cell Reference: $F$13 >= Constraint: 1E-14 (Click OK).
• Click Solve
• When the Solver Results box comes up, choose OK.
• Reprotect the sheet by choosing Tools, Protection, Protect Sheet, OK.
• Click the Stop Recording button (with the little "■") on the Stop Recording toolbar.
• CRITICALLY IMPORTANT! Before you run your macro, you must tell VBA to include Solver in its bag of tricks.
• Under Tools, choose Macro, Macros... (Alt F8).
• Highlight Recalc and click the Edit button. The Visual Basic Editor will appear.
• Under Tools, choose References. The References => VBAProject dialog box will appear.
• In the Available References list, make sure that the little box next to SOLVER is checked. Click OK.
• If is not there or if you see a message like "Missing: Solver.xla", you need to show VBA where the solver is.
• Click the Browse button and browse to "C:\Program Files\Microsoft Office\Office10\Library\Solver\"
• Change "Files of type:" to "Microsoft Excel files (*.xls,*.xla)"
• Select "Solver.xla" and click Open
• Under File, choose Close and Return to Microsoft Excel. (You may need to exit and restart Excel before the Solver will work right.)
• Now you can run your new macro. You can run a macro by clicking Run in the Macro dialog box (Tools, Macro, Macros... or Alt F8) or by typing the shortcut key.
• Change the acid concentration to 100 mmolar and the salt to 10 mmolar then run the macro. You should get a pH of 3.76.
• Let's fine tune this.
• After you run the macro a few times, run Solver manually (without the macro). Remember that you will need to unprotect the worksheet first.
• Notice that the subject to constraints box is filling up with the one constraint. The macro adds the same constraint every time it runs. This is not good
• Open the VB Editor with the Recalc macro displayed. Between the line 'ActiveSheet.Unprotect' and the line 'SolverOk SetCell:=....', add the line 'SolverReset'. Indent as necessary. Note: In this assignment, I'm using single-quotes (' ') to indicate a quotation. Don't type them in. Double-quotes (" ") are part of the commands. Type those in.
• While you are at it, look to see if there are some exactly duplicated lines (the macro recorder will sometimes put these in). For example, the line 'SolverOk SetCell:= "$F$17", MaxMinVal:=3, ....' may be repeated after the line 'SolverAdd CellRef:="$F$13"....'. If so, delete the extra lines. Close and return to Excel. The macro should now clean up the Solver box each time it runs. You could have had this in the macro to start with by clicking the ResetAll button while you were recording the macro.
• One more problem:
• Change the K_A to 6.31E-08 and set acid and salt both to 100 mmolar. Run your recalc macro.
• The Solver Results box will say that Solver could not find a feasible solution. Solvers failure has to do with the initial value for Hion.
• Set Hion ([H⁺]) to 1E-14 (unprotect the sheet first!) and run again. This time you get a pH of 7.20.
• You need to add a line to your macro that sets the initial value of Hion to 1E-14. After the line 'ActiveSheet.Unprotect' add the line 'Range ("$F$13") = "1.00E-14"'.
• Your Calculator should now work for a range of acid and salt values.
• Save your workbook as 'ABC_weak1.xls' where ABC are your initials.
5. Add a command button.
• Unprotect the sheet. Choose View, Toolbars, Forms to display the forms toolbar.
• Point at the second item down in the right hand column. The yellow tool tip should appear saying 'Button'. If so click and add a button near the bottom of the blank area on the left side of the grey box on your worksheet.
• The Assign Macro dialog box will appear. Choose Recalc and click OK.
• Highlight the text on the button ('Button 1') and replace it with the text 'Recalc'. Click somewhere else in your worksheet.
• Enter values for acid and salt. Left click the Recalc button. Cool!
• Save your worksheet as 'ABC_weak2.xls'.
6. Add another command button.
• Insert another button above the first one.
• The Assign Macro dialog box will appear. For the 'Macro Name:' type 'NewData', then click the New button.
• Between the line 'Sub NewData' and the line 'End Sub', type as follows. This must be typed exactly, with every comma, quote mark, etc. Be careful, the lines that start 'Data = ...' are fairly long and may have wrapped. Both of the lines end with '...Concentration")'. There are exactly 6 lines of code total.

Sub NewData()

Data = InputBox("Acid Concentration, in mM", "Enter Acid Concentration")
Range("$F$9").Value = Data
Data = InputBox("Salt Concentration, in mM", "Enter Salt Concentration")
Range("$F$10").Value = Data

End Sub

• Exit and Return to Excel.
• Right click on the button and change the text to 'New Data'.
• Click elsewhere on your worksheet. Click the New Data button. You should be able to enter values for acid and salt.
7. Add a combo box.
• Examine the worksheet "listboxeg.xls". A combo box is very much like a list box.
• Unprotect your Calculator worksheet. Copy the information in A1:C6 of 'KaValues.xls' to I5:K10 of your pH Calculator worksheet.
• Using the Forms toolbar, insert a combo box above the two command buttons you created. Right click on it and choose format control.
• On the Control tab, set the Input range to $J$5:$K$10. Set the Cell link to $F$7. Set the Drop down lines to 6.
• When you click the down arrow in the box, a list of weak acids will drop down. Pick one.
• In cell F8, type the following formula: '=VLOOKUP(F7,I5:K10,3)'.
• Play with the combo box. When you choose a weak acid, that acid's K_A should show up in cell F8.
8. Clean up the worksheet.
• Close the Forms toolbar.
• Format cell F8 to locked (Format, Cells, Protection, Locked).
• Format cell F7 to hidden (Format, Cells, Number, Custom, Type: ;;;) and not locked.
• Format I5:K10 to have white text (Format, Cells, Font, Color: choose white).
• Set the length of the combo box to just long enough to display the names (right click to edit).
• Put the box and the buttons where you want them.
• Protect the worksheet.
• Save as 'ABC_weak4.xls'.
9. Use WebCT to send me all your files compressed into a single zip folder. Be sure to include your initials in the name of the zip folder and of each file in the folder.