Hi All,
This is my first post here so please be gentle
I'm not looking for pin point solutions to this question, just really ideas as to what the underlying cause might be.
I have inherited a Spreadsheet that has 4 key sheets:
1. 2 combo boxes (2nd updates based on selection made in the first, updates by macro), tables (not named tables), and several graphs that are tied to those tables
2. further filtered data which is populated by macro triggered from clicking on particular cells on sheet 1
3. filtered data based on selections made in sheet 1 combo boxes
4. raw data used to populate sheets 3 and the tables in sheet 1
The majority of macro code used to extract data from the sheets uses SQL statements with adodb connection and recordset so treats sheets like a database table.
Once the user has made a selection in the 1st combobox on sheet 1, the 2nd combo box is populated and then once the 2nd combobox has the desired selection a button is clicked and that kicks off the data interogation.
the firs part of the macro selects data from sheet 4 based on the criteria determined by the combobox selections and then the resulting recordset populates sheet 3. Here is where the problem begins. I have started calling it an irrational bug as I just can't explain how/why it happens, it isn't consistent between PCs and as far as I can tell shouldn't even be possible.
The bug:
Once sheet 3 is populated, macros then run again using SQL statements, which populate the tables on sheet 1 based on the results in sheet 3. On customer laptops (Excel 2010 SP1, but no updates since SP1), here is where the process breaks; despite the old data being cleared from sheet 3, then it being populated with new data, the macros that populate the tables behave as if the old data is still there and the tables and graphs on sheet 1 don't update. If the macros are re-run with no criteria changes, this still makes no difference, however if the file is saved and then the macros are re-run with no criteria changes, the tables and graphs on sheet 1 will now update. This lead me to think that the sheet needed to be calculated, but this made no difference, I wasn't that surprised as there are no formulas on it to calculate so it must be something else that saving the file triggers. Unfortunately the file is stored on a network and I can't rely on the user saving a local copy before opening and running the macros.
I did wonder if the old data is somehow being retained in a recordset, but at no point do the macros put the data from sheet 3 into a recordset so i can't see why this old data could persist anywhere
The bug isn't replicated on a PC running Excel 2010 SP1 but with Office Updates applied following SP1 (not SP2 though). The laptops that don't run it correctly are in a corporate environment and I can't reasonably ask that they update all their pcs. I am considering replacing the macro code that populates the tables on sheet 1 with formulas but some of the code will be difficult to replicate in formulas and will involve numerous SumIfs, CountIfs, Sum_Products and date range stuff which I am not looking forward to...
As I said, I'm not looking for a solution, just ideas of what to look at as I am at a loss. I have trawled through a lot of Excel forums looking for anything similar but have not come across anything even closely resembling this issue...
Many thanks in advance for your time!
Cheers,
Dan
This is my first post here so please be gentle
I'm not looking for pin point solutions to this question, just really ideas as to what the underlying cause might be.
I have inherited a Spreadsheet that has 4 key sheets:
1. 2 combo boxes (2nd updates based on selection made in the first, updates by macro), tables (not named tables), and several graphs that are tied to those tables
2. further filtered data which is populated by macro triggered from clicking on particular cells on sheet 1
3. filtered data based on selections made in sheet 1 combo boxes
4. raw data used to populate sheets 3 and the tables in sheet 1
The majority of macro code used to extract data from the sheets uses SQL statements with adodb connection and recordset so treats sheets like a database table.
Once the user has made a selection in the 1st combobox on sheet 1, the 2nd combo box is populated and then once the 2nd combobox has the desired selection a button is clicked and that kicks off the data interogation.
the firs part of the macro selects data from sheet 4 based on the criteria determined by the combobox selections and then the resulting recordset populates sheet 3. Here is where the problem begins. I have started calling it an irrational bug as I just can't explain how/why it happens, it isn't consistent between PCs and as far as I can tell shouldn't even be possible.
The bug:
Once sheet 3 is populated, macros then run again using SQL statements, which populate the tables on sheet 1 based on the results in sheet 3. On customer laptops (Excel 2010 SP1, but no updates since SP1), here is where the process breaks; despite the old data being cleared from sheet 3, then it being populated with new data, the macros that populate the tables behave as if the old data is still there and the tables and graphs on sheet 1 don't update. If the macros are re-run with no criteria changes, this still makes no difference, however if the file is saved and then the macros are re-run with no criteria changes, the tables and graphs on sheet 1 will now update. This lead me to think that the sheet needed to be calculated, but this made no difference, I wasn't that surprised as there are no formulas on it to calculate so it must be something else that saving the file triggers. Unfortunately the file is stored on a network and I can't rely on the user saving a local copy before opening and running the macros.
I did wonder if the old data is somehow being retained in a recordset, but at no point do the macros put the data from sheet 3 into a recordset so i can't see why this old data could persist anywhere
The bug isn't replicated on a PC running Excel 2010 SP1 but with Office Updates applied following SP1 (not SP2 though). The laptops that don't run it correctly are in a corporate environment and I can't reasonably ask that they update all their pcs. I am considering replacing the macro code that populates the tables on sheet 1 with formulas but some of the code will be difficult to replicate in formulas and will involve numerous SumIfs, CountIfs, Sum_Products and date range stuff which I am not looking forward to...
As I said, I'm not looking for a solution, just ideas of what to look at as I am at a loss. I have trawled through a lot of Excel forums looking for anything similar but have not come across anything even closely resembling this issue...
Many thanks in advance for your time!
Cheers,
Dan
