Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems executing Excel VBA code in Microsoft Office 2003
Hi,
I have written a VBA function in a Microsoft Excel 2002 Work Sheet. The function is used to filter rows depending upon the selections made in 2 combo boxes in the work sheet. I Hide\Unhide the rows, depending upon the selections made in the combo boxes. I make row by row iteration to find if the row has to be hidden or unhidden. The worksheet typically has about 5000 rows. The function is written in the Change event of the Combo Boxes. When I open this Work Sheet on the machine which has Excel 2002 installed on it, it takes about 30 seconds to execute this function. However, when I open the same work sheet in Excel 2003, it does not complete even after 30 minutes and the Task Manager shows that the Work sheet is not responding. I have fine tuned the code for this function to a large extend and it is well reflected when opened on a machine with Excel 2002. It takes hardly 30 seconds for filtering 5000 rows. Can anybody help me in knowing some limitations\features of Excel 2003 which are creating problems for me?. Any help would be greatly appreciated. Thanks Umesh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems executing Excel VBA code in Microsoft Office 2003
Umesh,
Instead of iterating through your range, you should simply rely on Excel's native filtering function, along the lines of this, where F12 and F13 are the linked cells for the two combo boxes: Range("A1:A5000").AutoFilter _ Field:=1, _ Criteria1:="=" & Range("F12").Value, _ Operator:=xlOr, _ Criteria2:="=" & Range("F13").Value HTH, Bernie Excel MVP "Umesh" wrote in message ... Hi, I have written a VBA function in a Microsoft Excel 2002 Work Sheet. The function is used to filter rows depending upon the selections made in 2 combo boxes in the work sheet. I Hide\Unhide the rows, depending upon the selections made in the combo boxes. I make row by row iteration to find if the row has to be hidden or unhidden. The worksheet typically has about 5000 rows. The function is written in the Change event of the Combo Boxes. When I open this Work Sheet on the machine which has Excel 2002 installed on it, it takes about 30 seconds to execute this function. However, when I open the same work sheet in Excel 2003, it does not complete even after 30 minutes and the Task Manager shows that the Work sheet is not responding. I have fine tuned the code for this function to a large extend and it is well reflected when opened on a machine with Excel 2002. It takes hardly 30 seconds for filtering 5000 rows. Can anybody help me in knowing some limitations\features of Excel 2003 which are creating problems for me?. Any help would be greatly appreciated. Thanks Umesh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems executing Excel VBA code in Microsoft Office 2003
Umesh,
Instead of using SUM, use SUBTOTAL. Look in help for the correct syntax, but subtotal only operates on visible cells, so will return the correct sum after filtering. You can place the formula after filtering, or simply don't inlcude the formula's row in the filtered range. HTH, Bernie "Umesh Bhanushali" wrote in message ... Hi Bernie Thanks for your reply. Even I had first decided to use the AutoFilter method of Excel. However, there is one problem to this. I have a summation row at the bottom of these 5000 rows which are going to be filtered. The formula in this summation row has to change automatically as I hide\unhide these rows. E.g. If all the 5000 rows are visible it would be SUM of all the 5000 rows. However, if only 3000 rows are visible, it will be SUM of only these 3000 rows. When I use AutoFilter, this formula will not be automatically updated, and hence I have to go row by row to see which rows are visible and add create a new formula each time Filtering is done. I had previously ran across the issue of formula length exceeding 1024 characters. However, before filtering, I now Sort these rows using Excels 'Sort' method, and then I have implemented a logic which will create a formula like SUM(D20:D3000), depending upon the sorted group which is displayed. Hope I am clear enough in my explanations. Can you help me in knowing how can I make my formula react to hidden\unhidden rows automatically as I use AutoFilter. Second thing is, my row by row iteration may be slow. It takes about 30 seconds in Excel 2002, but why does the same piesce of code take 5 minutes in Excel 2003? Any help would be appreciated. Thanks, Umesh *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems executing Excel VBA code in Microsoft Office 2003
Hi Bernie,
Thanks for the suggestion. I am now using the Subtotal function instead of SUM and it seems to work fine. I have also found out why the execution was taking longer in excel 2003. Excel 2003 evaluates the formulas on the sheet in a different manner then in excel 2000. Even if we hide/unhide rows, the formulas on the work sheet are recalculated which was not the case in excel 2000. So I just turned off the formulas while the filtering function was working. i used Application.Calculation = xlCalculationManual and once the filtering was over, I reset it to Application.Calculation = xlCalculationAutomatic. Thanks for all the help. "Bernie Deitrick" wrote in message ... Umesh, Instead of using SUM, use SUBTOTAL. Look in help for the correct syntax, but subtotal only operates on visible cells, so will return the correct sum after filtering. You can place the formula after filtering, or simply don't inlcude the formula's row in the filtered range. HTH, Bernie "Umesh Bhanushali" wrote in message ... Hi Bernie Thanks for your reply. Even I had first decided to use the AutoFilter method of Excel. However, there is one problem to this. I have a summation row at the bottom of these 5000 rows which are going to be filtered. The formula in this summation row has to change automatically as I hide\unhide these rows. E.g. If all the 5000 rows are visible it would be SUM of all the 5000 rows. However, if only 3000 rows are visible, it will be SUM of only these 3000 rows. When I use AutoFilter, this formula will not be automatically updated, and hence I have to go row by row to see which rows are visible and add create a new formula each time Filtering is done. I had previously ran across the issue of formula length exceeding 1024 characters. However, before filtering, I now Sort these rows using Excels 'Sort' method, and then I have implemented a logic which will create a formula like SUM(D20:D3000), depending upon the sorted group which is displayed. Hope I am clear enough in my explanations. Can you help me in knowing how can I make my formula react to hidden\unhidden rows automatically as I use AutoFilter. Second thing is, my row by row iteration may be slow. It takes about 30 seconds in Excel 2002, but why does the same piesce of code take 5 minutes in Excel 2003? Any help would be appreciated. Thanks, Umesh *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tab key problem in Microsoft Office Excel 2003 | Excel Worksheet Functions | |||
Microsoft office excel 2003 has stopped working | Excel Discussion (Misc queries) | |||
Need help with end results Microsoft Office Excel 2003 | Excel Worksheet Functions | |||
Microsoft Office XP excel 2003 | Excel Discussion (Misc queries) | |||
Want to buy just EXCEL 2003 not the entire microsoft office suite | Excel Discussion (Misc queries) |