Text Home

Computer Programs

General Supplements

Instructor Supplements

Introductory Chemical Engineering Thermodynamics

J.R. Elliott
C. T. Lira

Prentice-Hall International Series in the Physical and Chemical Engineering Sciences ISBN (0-13-011386-7)

I. General suggestion for using Macros

If you are a user careful with the source of the files that run macros, you can set Excel to Medium Security for running macros. The medium security setting will prompt you before loading a spreadsheet with macros to give a choice about whether the macros should be executable. To access the security settings, to to Tools>Macro>Security.

Figure 1. Security dialog box.

The medium setting will give a careful user adequate security if you only run macros from trusted sources.

II. Common Error Messages

Delete Method of Range class failed

This error occurs if a macro tries to delete from a spreadsheet that is is 'protected'. Unprotect the sheet and start the macro again.

Internal Error/Memory Exhausted

You may encounter an error in some service packs installations when running Solver. For example, if you have correctly set the Solver reference pointer but did not complete all the parts of step 6 from that procedure, you will find the following misleading error upon execution of the macro:

If this occurs, double check the pointer reference, and then carry out the parts of step 6. After restarting Excel and reloading the workbook, the macro should run.

III. Hints for Using Solver in Macros

Advanced applications can use the solver tool with a macro, however the location of Solver.xla is needed for the Solver tool to run successfully within a macro. In a standard installation of Excel, the location of Solver.xla is not known when Excel is started. However, when a file is opened that uses Solver in a macro, the location of Solver.xla is read from the stored file. The location will be that from the user's computer who saved the file; the location in the stored file will probably be incorrect for the computer on which the file is to be run because the location depends on the version of Excel, as well as the installation.

Checking the reference to Solver.xla

When a file is opened which uses Solver.xla within a macro, the solver location read from the file is stored in memory, even if incorrect. You can test to see if the location is correct for your computer by using Tools>Macro>Visual Basic Editor... The visual basic editor will open. On the Visual Basic Editor, use Tools>References. A dialog box of references will open. Below is an example dialog box with an incorrect pointer to Solver.xla.

Figure 2. Illustration of incorrect Solver.xla reference pointer.

Note the last checked entry indicates that Solver.xla has not been found. Note that the lower box indicates that the system tried to find Solver.xla in a subdirectory of "D:\Apps\...". If a subsequent Excel file is opened that has a reference pointer to the correct location of Solver.xla, that correct location is loaded into memory. Solver will run properly for the remainder of the Excel work session. However, to avoid future confusion is best to save workbooks with the correct location of Solver.xla for your computer. See the section below Pointing to the Correct Solver.xla location.

Error from running a macro with an incorrect reference pointer

When a macro is executed that has an incorrect pointer to Solver.xla, the macro fails with the following message:

Figure 3. Compile error from running a macro with an incorrect reference pointer.

To handle the error:

1. Click OK on the Microsoft Visual Basic window 'Compile error: "Can't find project or library".'
2. The macro is suspended, but not yet stopped. Stop the macro by either: (a) From VBA Menu bar shown in the parent window above the dialog box in Figure 3, use Run>Reset; or (b) on the button bar as shown in Figure 3, click the blue square (reset) in the tool bar.
3. Pull up the reference dialog box Visual Basic window using Tools>References. The dialog box shown in Figure 2 will be displayed.
4. Proceed with the steps for 'Pointing to the Correct Solver.xla location' immediately below.

Pointing to the Correct Solver.xla location

Your computer should display the dialog box as shown in Figure 2.

1. If it is not already selected, select the line for MISSING:SOLVER.XLA and assure that it is checked.

2. Then click the button in the "References VBAProject" dialog box for Browse...

3. In the Add Reference dialog box, click "Files of Type:"., select Microsoft Excel files

Browse to find the file Solver.xla in a location similar to ' C:\Program Files\Microsoft Office\Office10\Library\Solver '. Within the file location, "C:\Program Files\Microsoft Office\Office10", the drive and subfolders depend on where the office applications are installed. Recent installations use 'Office10', or 'Office11', etc. for installations. (You may wish to do a file search to locate Solver.xla your computer and then revisit this step if you have trouble browsing to find Solver.xla). Click on Solver.xla as shown above. Click Open.

4. In the References Dialog box, the reference to solver.xla appears as shown below.

Click OK to close.

5. Return to the Spreadsheet using Visual Basic Menu bar: File>Close.

6. To avoid bugs that have arisen in recent Microsoft patches (as of 12/8/04), is it best to: (a) save the file with the correct pointer to Solver.xla; (b) completely close all Excel windows; (c) restart Excel and reload the workbook. The macro pointer reference should now be fixed. You are now ready to run the macro on an unprotected worksheet.


updated 4/20/05 ,Copyright 2000-5, Carl T. Lira, J. Richard Elliott. All rights reserved.