Macros run too fast??

[dcdewick]

I have a spreadsheet that processes data returned via a third-party query add-in to Excel. The numbers of records returned will vary depending on selection parameters set by the user. The sheets containing the query data are consolidated onto an interim results page. Then, to present the analysis results in the format needed by the user, the interim results sheet contents are copied to another sheet that is subtotalled and formatted. The contents of this sheet then get pasted to a front sheet that the user can either view or print.

I need to determine the the numbers of rows of data returned by the queries so I can copy and paste various ranges of data between sheets. To complicate matters, I can't reference the query data directly as the refresh process deletes rows in the sheets that it returns data into, and this causes #REF! error in any other sheets that made direct reference the returned data. Hence all references to the query data have to be indirect using the INDIRECT function.

I've used several diferent mehods of determining the number of rows of data returned.

As the queries return the data into named ranges for each column of data, I have used the ListNames method to return the details of the ranges to a control sheet once the queries have completed. From this I can generate text strings corresponding to the ranges I need to manipulate.

I have used the COUNTA function to count the number of non-blank cells in a range (the size of which I know will always exceed the number of rows of data retutrned).

I get my macro to select the last row in the sheet containing data, and use a statement of the form

rownum - activecell.row

to get the row number of the last row containing data.

I've even constructed a loop to step down a column of data until a blank cell is reached and counting how many non-blank cells it finds before the blank one.

Each method works fine if I single-shot the macro using Debug and the F8 key. However, if I run the macro from a button on the spreadsheet, or by using F5 from the VB editor, the macro fails as it picks up incorrect ranges, usually the ranges from the previous refresh of data. When I check back on the control sheet that calculates all the ranges, though, they are correct!! It is though Excel is multi-threading and the thread updating the spreadsheet lags behind the execution thread of the macro. By the time I get to check the spreadsheet contents, they're all ok, except for the results.

Has anyone else seen this type of problem, and if so, have they discovered any solutions.

 

[Andrew Poulsom]

What code are you using to refresh the query? Normally I would suggest using the Refreshing property:

With Worksheets(1).QueryTables(1) 
   Do While .Refreshing = True 
   Loop 
End With

but that may not be possible with your third-party add-in.

 

[dcdewick]

Hi Andrew

Thanks for the reply.

I'm using an SPSS product, called Showcase Suite. Query is part of the suite used to pull the data from an IBM iSeries which runs our MRP system. I use this product as it provides access controls down to field level. To refresh the data, the user just enters his selection range in a couple of cells on the front sheet in the speeadsheet, and clicks on a button which runs my macro. The Query addin knows which cells to pick up the query variables from, and all 4 queries in the sheet are refreshed using a single comand in the macro. The macro doesn't resume until the queries are complete. I've tried running the queries one at a time, but still get the same sorts of problems.

I'm using Excel 2003 on a W2K machine, by the way.

I've tried turning screenupdating on and off, adding calculate commands here and there, but it's so frustrating seeing the watched variables pick up the wrong values when the macro runs at full tilt, but working perfectly using F8 in Debug mode!!

What is intersting is that if I step out of the macro and go into one of the query data sheets, select the top left cell of the returned data and then do a CTRL/SHIFT/END I get a larger are selected than I expect, and a larger area than that containing the returned data. However, the extra cells selected all appear to be empty. This prompted me to clear the contents of the previous query from the sheet before refreshing the data, but the problems still remain. I think I might need to find another add-in that will show up any hidden characters that might be lurking in the sheet.

 

[Andrew Poulsom]

I'm not familiar with that product, so I don't know if it has the equivalent of the Refreshing property.

You could try putting a Wait statement at the end of your macro to pause for a few seconds.

 

[dcdewick]

Hi Andrew

I had a session browsing various Excel web sites last night, and came across a couple of things that might help. First is dynamic ranges, that is defining a name, Count1, say, with the formula

=offset('Sheet Name'!$a$1,0,0,counta('Sheet Name'!$a:$a),1)

in the refers to box. I can then get the number of non-blank rows in the range by putting the formula

=ROWS(Count1)

into a cell.

This assumes that there are no cells in column A below the last row of data returned that are non-blank. I'll try creating the names within another macro. This is useful as it allows me to be in control of the names used. Currently, if I modify the queries in any way, the range names they generate might change, which means editing the worksheet to change any explict references. This way I control the naming.

There is also a macro I've found at

http://www.beyondtechnology.com/geeks012.shtml

that points to the real last cell in a sheet that I'm going to try. If it works as described, it might help solve why I see some of the discrepancies when I do a CTRL/SHIFT/END.

Then, just in case it's a problem with the sheet I've been using to develop the analysis, I'm going to re-create the entire spreadsheet from scratch.