Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inconsistent Behavior on Web vs. Local Disk
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save a file to disk without re-formating the disk | Excel Discussion (Misc queries) | |||
Excel clip board inconsistent behavior | Excel Discussion (Misc queries) | |||
VBA to save a web page to local disk | Excel Discussion (Misc queries) | |||
Inconsistent Macro Behavior | Excel Discussion (Misc queries) | |||
Download files from 3.5 disk in excel but system said disk need fo | Excel Discussion (Misc queries) |