D
D. A. Gray
Overview
I have a pair of Microsoft Excel worksheets that I
developed and posted on a client's Web site, so that his
Realtor customers can use them to prepare closing cost
estimate worksheets for their customers, who are buyers
and sellers of houses. Both worksheets use custom VBA
macros tied to command buttons to copy the appropriate
estimates into the fifth of five columns. The first four
columns contains estimates, usually ranges, for different
financing methods. The Realtor then adds the items that
cannot be known in advance, and the worksheet calculates
totals for estimated closing costs and, for the buyer,
estimated cash needed at closing.
Problem Statement
If you run the worksheet from the network and neglect to
enter the selling price, the application dies with a run-
time error 91, Object variable or With block not set.
Worse yet, the error goes untrapped, even though, I
believe, all the custom VBA procedures have error traps.
However, if you save the worksheet to a local drive and
run it from there, it works as expected, giving no run-
time errors.
Summary of Results
The table below summarizes the test results.
Scenario # Cert Browser File Location Result
1 Unapproved IE 6.0 SP 1 Web Server
Error 91.
2 Approved IE 6.0 SP 1 Web Server
Success.
3 Approved IE 6.0 SP 1 Local Drive
Success.
4 Approved IE 6.0 SP 1 Local Drive
Success.
5 Unapproved Netscape 7.02 Web Server
Success.
6 Approved Netscape 7.02 Web Server
Success.
7 Approved Netscape 7.02 Local Drive
Success.
8 Approved Netscape 7.02 Local Drive
Success.
9 Approved IE 6.0 SP 1 Web Server
Error 91.
Program Features
The Seller's worksheet uses a pair of lookup tables to
calculate the title insurance premium according to the
schedule published by the State of Texas, which I imported
into an Excel worksheet which became part of the Seller's
workbook.
There are four financing methods: Cash, Conventional, FHA,
and VA, each of which has associated closing costs. The
main worksheet, which is the only one that a customer
usually sees, contains a column for each financing method
and a fifth column to hold the estimates for the chosen
method. Behind this sheet is another sheet which holds the
cost estimates that will be entered into the fifth column.
This allows the front page to show ranges, while we use
arbitrary cost numbers, usually the high end of the range,
for the fifth column.
There is a command button for each financing method. When
a button is pressed, a number that corresponds to the
chosen method is passed into a VBA function that copies
the correct range from the cost worksheet into the fifth
column of the front sheet.
If the Realtor fails to enter the selling price before she
presses one of the four command buttons, a form opens to
prompt her for the price. When she enters the price and
clicks the OK button, the price is entered into the
worksheet and the copying proceeds.
The VBA macros make extensive use of Range objects and
tables that are marked as Ranges to do their work and I
wrote a custom function to copy a Range without using the
Windows clipboard.
The macros are digitally signed using a Thawte code
signing certificate.
Development Environment
The Excel documents were developed and tested using
Microsoft Excel 10.0 (a. k. a 2002, XP), SP 2 on a Windows
XP SP1 desktop computer.
Scenarios
I devised several test scenarios that should cover the
whole universe of possible user scenarios. They definitely
illustrate the paradoxical result that I have observed and
that has been reported, though somewhat inaccurately, I
believe, by at least one Realtor.
Scenario 1 - User Paul, Choose Financing Method without
Sales Price
I created a new local user, Paul, on my Windows XP machine
and assigned Paul to the Users group, the most restrictive
security context, with just enough permissions to run
programs.
First, I opened Microsoft Excel, to initialize the
required Registry entries in the HKCU hive for my new user.
.. Next, I navigated to the Web site that hosts the
page, http://www.stoflc.com/, using my default Web
browser, which is Internet Explorer 6, SP 1, patched up to
date per Windows Update.
.. Next, I clicked on the buyer.xls spreadsheet link,
which is listed in a table about halfway down the page.
.. I chose to Open the file, whereupon it opened in a
new browser window, as expected.
.. Since the sheet contains a signed VBA project, the
next thing that happened was that Excel displayed the
prompt about signed macros. I checked the box to always
trust macros signed by this publisher (which is my
company) and clicked the Enable Macros button.
.. The worksheet opened, and I clicked on the Cash
button, causing the prompt for selling price, a Microsoft
Office Form, to open.
.. Immediately, the following error message was
displayed: Run-time error '91' Object variable or With
block variable not set. However, the input form for the
selling price was visible behind the dialog box generated
by the run-time error.
The only available choice was to end the program, which
terminated the macro and displayed the blank worksheet.
Scenario 2 - User Paul, Choose Financing Method with Sales
Price
Scenario 2 resumes where Scenario 1 left off. I'm still
signed on as Paul, connected to the Web site, with the
Excel spreadsheet open in its own window. I didn't even
close after I ran the last scenario.
.. Enter a selling price of $150,000, a loan amount
of $100,000, a term of 15 years, and an APR of 5.25%.
.. Click the Conventional button, just to be
consistent with the fact that I specified a loan.
.. Calculation proceeds as expected, yielding a
finished worksheet, ready for presentation to the buyer.
At the end of Scenario 2, I saved the worksheet as
buyer_scenario_2.xls and closed the window. Since I have
ZoneAlarm set to prompt me whenever it wants to access the
Internet, I was prompted when I asked to save the
worksheet, leaving no doubt that it was indeed Microsoft
Excel that was handling the worksheet. It seems odd that
it needed to read from the Web site, though, unless Excel
only partially loaded the worksheet into memory when I
opened it.
Scenario 3 - Same as Scenario 1, but Workbook Saved to
Local Disk
Scenario 3 takes up where Scenario 2 leaves off.
.. The second browser window is closed, so I choose
the same link from the main page, saving the sheet to a
local disk this time, as buyer_scenario_3a.xls.
.. Once the file is saved, I choose Open, and the
file opens locally in a regular Microsoft Excel window.
.. As in Scenario 1, I chose Cash without first
entering a selling price. The worksheet did its thing
without complaint, producing the desired result.
I saved this worksheet as buyer_scenario_3b.xls and closed
Microsoft Excel.
Scenario 4 - Same as Scenario 2, but Workbook Saved to
Local Disk
Scenario 4 takes up where Scenario 3 leaves off.
.. The second browser window and Excel are both
closed, so I choose the same link from the main page,
saving the sheet to a local disk this time, as
buyer_scenario_4a.xls.
.. Once the file is saved, I choose Open, and the
file opens locally in a regular Microsoft Excel window.
.. As in Scenario 2, I entered the sales price and
loan particulars, and chose Conventional financing.
.. As expected, the worksheet calculated without any
trouble.
I saved the finished worksheet as buyer_scenario_4b.xls.
Scenario 5 - User LuAnn, Scenario 1 with Netscape 7
I created a new local user, LuAnn, on my Windows XP
machine and assigned LuAnn to the Users group, the most
restrictive security context, with just enough permissions
to run programs.
.. Open the Web site in Netscape 7.
.. Choose the Buyer file, buyer.xls, and open it from
the Web.
.. The prompt from Netscape offers to Open using
Excel or Save this file to disk. I chose to Open Using
Excel.
.. The next step leaves little doubt that the full
fledged Microsoft Excel is about to open the workbook.
.. The security prompt is presented as expected, and
I again choose to trust all macros from this publisher.
.. I choose Cash without entering a selling price,
and the worksheet prompts me for one.
.. Unlike Scenario 1, the calculation proceeds
without any fuss.
.. I saved the finished worksheet as
buyer_scenario_5.xls.
I see little point in repeating the remaining scenarios
for Netscape 7, though I shall run through them, making
note only of any unexpected findings.
Summary of Scenarios 6 through 8 with Netscape 7
OK, here goes.
.. Scenario 6 is Scenario 2 with Netscape 7; the
finished file is saved as buyer_scenario_6.xls.
.. Scenario 7 is Scenario 3 with Netscape 7; the
original file is saved as buyer_scenario_7a.xls and the
finished file is saved as buyer_scenario_7b.xls.
.. Scenario 8 is Scenario 4 with Netscape 7; the
original file is saved as buyer_scenario_8a.xls and the
finished file is saved as buyer_scenario_8b.xls.
Scenario 9 - User Paul, Repeat Scenario 1
Since Paul's report claimed that his customer got an error
only the first time she ran the worksheet from the Web, I
am repeating Scenario 1 exactly, logged in as local user
Paul, who has already agreed to trust our signed code.
.. Open the Web site from the shortcut that I created
in the directory where I am accumulating test results.
Since Internet Explorer is the default Web browser, the
page opens in IE.
.. When I click on the link for buyer.xls, I am
offered the choice of opening the file from the Web or
saving it to a local directory. I choose to open it.
.. As I watch the download progress, the status bar
clearly states that Internet Explorer is downloading the
file.
.. It is equally clear from the icon and window
caption that Windows considers the open worksheet to
belong not to Excel but to Internet Explorer. The same is
true of the task list.
As seen in my own ad hoc tests some time ago, this
produced the same result; the error recurs exactly as
expected, the error is thrown as soon as the form
displays, just as it was in Scenario 1. I repeated the
test several times to satisfy myself that the error is
reproducible and is unrelated to whether or not the
digital signature is accepted in the same session.
Action
The document is available at http://www.stoflc.com/,
specifically, http://www.stoflc.com/buyer.xls, for anyone
who wants to repeat the tests. I'm considering opening an
incident but I thought I'd use this problem as my excuse
to test the news group.
David A. Gray
P6 Consulting
Irving, TX, USA
http://www.p6c.com/
You are more important than any technology we may employ.
I have a pair of Microsoft Excel worksheets that I
developed and posted on a client's Web site, so that his
Realtor customers can use them to prepare closing cost
estimate worksheets for their customers, who are buyers
and sellers of houses. Both worksheets use custom VBA
macros tied to command buttons to copy the appropriate
estimates into the fifth of five columns. The first four
columns contains estimates, usually ranges, for different
financing methods. The Realtor then adds the items that
cannot be known in advance, and the worksheet calculates
totals for estimated closing costs and, for the buyer,
estimated cash needed at closing.
Problem Statement
If you run the worksheet from the network and neglect to
enter the selling price, the application dies with a run-
time error 91, Object variable or With block not set.
Worse yet, the error goes untrapped, even though, I
believe, all the custom VBA procedures have error traps.
However, if you save the worksheet to a local drive and
run it from there, it works as expected, giving no run-
time errors.
Summary of Results
The table below summarizes the test results.
Scenario # Cert Browser File Location Result
1 Unapproved IE 6.0 SP 1 Web Server
Error 91.
2 Approved IE 6.0 SP 1 Web Server
Success.
3 Approved IE 6.0 SP 1 Local Drive
Success.
4 Approved IE 6.0 SP 1 Local Drive
Success.
5 Unapproved Netscape 7.02 Web Server
Success.
6 Approved Netscape 7.02 Web Server
Success.
7 Approved Netscape 7.02 Local Drive
Success.
8 Approved Netscape 7.02 Local Drive
Success.
9 Approved IE 6.0 SP 1 Web Server
Error 91.
Program Features
The Seller's worksheet uses a pair of lookup tables to
calculate the title insurance premium according to the
schedule published by the State of Texas, which I imported
into an Excel worksheet which became part of the Seller's
workbook.
There are four financing methods: Cash, Conventional, FHA,
and VA, each of which has associated closing costs. The
main worksheet, which is the only one that a customer
usually sees, contains a column for each financing method
and a fifth column to hold the estimates for the chosen
method. Behind this sheet is another sheet which holds the
cost estimates that will be entered into the fifth column.
This allows the front page to show ranges, while we use
arbitrary cost numbers, usually the high end of the range,
for the fifth column.
There is a command button for each financing method. When
a button is pressed, a number that corresponds to the
chosen method is passed into a VBA function that copies
the correct range from the cost worksheet into the fifth
column of the front sheet.
If the Realtor fails to enter the selling price before she
presses one of the four command buttons, a form opens to
prompt her for the price. When she enters the price and
clicks the OK button, the price is entered into the
worksheet and the copying proceeds.
The VBA macros make extensive use of Range objects and
tables that are marked as Ranges to do their work and I
wrote a custom function to copy a Range without using the
Windows clipboard.
The macros are digitally signed using a Thawte code
signing certificate.
Development Environment
The Excel documents were developed and tested using
Microsoft Excel 10.0 (a. k. a 2002, XP), SP 2 on a Windows
XP SP1 desktop computer.
Scenarios
I devised several test scenarios that should cover the
whole universe of possible user scenarios. They definitely
illustrate the paradoxical result that I have observed and
that has been reported, though somewhat inaccurately, I
believe, by at least one Realtor.
Scenario 1 - User Paul, Choose Financing Method without
Sales Price
I created a new local user, Paul, on my Windows XP machine
and assigned Paul to the Users group, the most restrictive
security context, with just enough permissions to run
programs.
First, I opened Microsoft Excel, to initialize the
required Registry entries in the HKCU hive for my new user.
.. Next, I navigated to the Web site that hosts the
page, http://www.stoflc.com/, using my default Web
browser, which is Internet Explorer 6, SP 1, patched up to
date per Windows Update.
.. Next, I clicked on the buyer.xls spreadsheet link,
which is listed in a table about halfway down the page.
.. I chose to Open the file, whereupon it opened in a
new browser window, as expected.
.. Since the sheet contains a signed VBA project, the
next thing that happened was that Excel displayed the
prompt about signed macros. I checked the box to always
trust macros signed by this publisher (which is my
company) and clicked the Enable Macros button.
.. The worksheet opened, and I clicked on the Cash
button, causing the prompt for selling price, a Microsoft
Office Form, to open.
.. Immediately, the following error message was
displayed: Run-time error '91' Object variable or With
block variable not set. However, the input form for the
selling price was visible behind the dialog box generated
by the run-time error.
The only available choice was to end the program, which
terminated the macro and displayed the blank worksheet.
Scenario 2 - User Paul, Choose Financing Method with Sales
Price
Scenario 2 resumes where Scenario 1 left off. I'm still
signed on as Paul, connected to the Web site, with the
Excel spreadsheet open in its own window. I didn't even
close after I ran the last scenario.
.. Enter a selling price of $150,000, a loan amount
of $100,000, a term of 15 years, and an APR of 5.25%.
.. Click the Conventional button, just to be
consistent with the fact that I specified a loan.
.. Calculation proceeds as expected, yielding a
finished worksheet, ready for presentation to the buyer.
At the end of Scenario 2, I saved the worksheet as
buyer_scenario_2.xls and closed the window. Since I have
ZoneAlarm set to prompt me whenever it wants to access the
Internet, I was prompted when I asked to save the
worksheet, leaving no doubt that it was indeed Microsoft
Excel that was handling the worksheet. It seems odd that
it needed to read from the Web site, though, unless Excel
only partially loaded the worksheet into memory when I
opened it.
Scenario 3 - Same as Scenario 1, but Workbook Saved to
Local Disk
Scenario 3 takes up where Scenario 2 leaves off.
.. The second browser window is closed, so I choose
the same link from the main page, saving the sheet to a
local disk this time, as buyer_scenario_3a.xls.
.. Once the file is saved, I choose Open, and the
file opens locally in a regular Microsoft Excel window.
.. As in Scenario 1, I chose Cash without first
entering a selling price. The worksheet did its thing
without complaint, producing the desired result.
I saved this worksheet as buyer_scenario_3b.xls and closed
Microsoft Excel.
Scenario 4 - Same as Scenario 2, but Workbook Saved to
Local Disk
Scenario 4 takes up where Scenario 3 leaves off.
.. The second browser window and Excel are both
closed, so I choose the same link from the main page,
saving the sheet to a local disk this time, as
buyer_scenario_4a.xls.
.. Once the file is saved, I choose Open, and the
file opens locally in a regular Microsoft Excel window.
.. As in Scenario 2, I entered the sales price and
loan particulars, and chose Conventional financing.
.. As expected, the worksheet calculated without any
trouble.
I saved the finished worksheet as buyer_scenario_4b.xls.
Scenario 5 - User LuAnn, Scenario 1 with Netscape 7
I created a new local user, LuAnn, on my Windows XP
machine and assigned LuAnn to the Users group, the most
restrictive security context, with just enough permissions
to run programs.
.. Open the Web site in Netscape 7.
.. Choose the Buyer file, buyer.xls, and open it from
the Web.
.. The prompt from Netscape offers to Open using
Excel or Save this file to disk. I chose to Open Using
Excel.
.. The next step leaves little doubt that the full
fledged Microsoft Excel is about to open the workbook.
.. The security prompt is presented as expected, and
I again choose to trust all macros from this publisher.
.. I choose Cash without entering a selling price,
and the worksheet prompts me for one.
.. Unlike Scenario 1, the calculation proceeds
without any fuss.
.. I saved the finished worksheet as
buyer_scenario_5.xls.
I see little point in repeating the remaining scenarios
for Netscape 7, though I shall run through them, making
note only of any unexpected findings.
Summary of Scenarios 6 through 8 with Netscape 7
OK, here goes.
.. Scenario 6 is Scenario 2 with Netscape 7; the
finished file is saved as buyer_scenario_6.xls.
.. Scenario 7 is Scenario 3 with Netscape 7; the
original file is saved as buyer_scenario_7a.xls and the
finished file is saved as buyer_scenario_7b.xls.
.. Scenario 8 is Scenario 4 with Netscape 7; the
original file is saved as buyer_scenario_8a.xls and the
finished file is saved as buyer_scenario_8b.xls.
Scenario 9 - User Paul, Repeat Scenario 1
Since Paul's report claimed that his customer got an error
only the first time she ran the worksheet from the Web, I
am repeating Scenario 1 exactly, logged in as local user
Paul, who has already agreed to trust our signed code.
.. Open the Web site from the shortcut that I created
in the directory where I am accumulating test results.
Since Internet Explorer is the default Web browser, the
page opens in IE.
.. When I click on the link for buyer.xls, I am
offered the choice of opening the file from the Web or
saving it to a local directory. I choose to open it.
.. As I watch the download progress, the status bar
clearly states that Internet Explorer is downloading the
file.
.. It is equally clear from the icon and window
caption that Windows considers the open worksheet to
belong not to Excel but to Internet Explorer. The same is
true of the task list.
As seen in my own ad hoc tests some time ago, this
produced the same result; the error recurs exactly as
expected, the error is thrown as soon as the form
displays, just as it was in Scenario 1. I repeated the
test several times to satisfy myself that the error is
reproducible and is unrelated to whether or not the
digital signature is accepted in the same session.
Action
The document is available at http://www.stoflc.com/,
specifically, http://www.stoflc.com/buyer.xls, for anyone
who wants to repeat the tests. I'm considering opening an
incident but I thought I'd use this problem as my excuse
to test the news group.
David A. Gray
P6 Consulting
Irving, TX, USA
http://www.p6c.com/
You are more important than any technology we may employ.