[NOTE: Turns out that didn't work, but explains the situation I'm going through]
Hey everyone,
This community has brought me so much in terms of answers and feedback through the last several years, I thought I would share a quick story, as it might help other 'newbies' just like me, who might be dealing with a similar issue in the future.
(NOT SO) QUICK BACKSTORY
So I've been working on V2 of a tool to acquire and analyse Stock Data from the web. Our REFRESH VBAs are pretty much working the same, and begin by clearing a few designated columns (in our case BN:CE) so that we won't use old data thinking it's been replaced by the updated one, using two IFs statement (in our case "X" in Column N and BL). That part of the code looks like this:
However, eversince I've started working on V2, the 'Clear Data to be Replaced' part as been an issue. Our REFRESH table is 4451 rows long, and while it was running fine at the very beginning, it became problematic right after I starter working on a new tab. I noticed it started to take more and more time to perform that ClearContents, to the point where it would take over 40 minutes to clear the whole table. And no matter what I tried (granted, I'm not the best coder around), I just couldn't seem to improve those performances. The new tab I was working on at the time is pretty heavy in terms of conditional formatting, objects being used as buttons...and for layout purposes, I had to build it with 286 columns, 0.5 wide, and merge everything up (I know...merged cells are the devil).
So I started deleting some objects. Didn't improve anything. Removed all the conditional formatting, again, no impact. I un-merged all the cells, still nothing. That tab used buttons that are in fact objects linked to a cell (as a way to apply conditional formatting to obejct). And, while working on the layout of the page, I had to create, then delete, then recreate them many (MANY) times. So I thought: "All right, maybe there's some kind of cache in Excel that stored all those images that I'm not using anymore, and this is causing it to be slower than it should be". But couldn't find anything on the internet about that, other than a suggestion to turn off the recently open pages, which didn't help.
So I started everything from scratch...about two-weeks and a half of work in the bin. Thinking I'd create those linked image at the very end only, and make sure not to create more than I would actually use. It took me 4 days to remake everything. Ran some REFRESH at various point...no issue. Until I started to work on that pesky tab again. Then, the same issue presented itself. So I said to myself: "Alright, instead of clearing it line-by-line for now, I'll just wipe-out the entire BN:CE columns in one fell swoop for our FULL REFRESH (that's what it was doing anyway), and I'll cross that river once I'll start working on targetted refresh", thinking it might be better if I'm only asking it to clear a few hundred lines instead of the whole 4451 rows.
...and while it was indeed (a lot) quicker, it still took around 30-40 secs just to clear the columns (for about 80-100 lines that met the two IFs criterias). But for a FULL REFRESH, I was back at 30-40 mins. Which is an eternity, especially when dealing on the Stock Market.
So I did more testing. Hid some tabs that we didn't use as much. Removed the linked object buttons, and replaced them with 'invisible' ones placed over a cell that has conditional formatting. Placed the REFRESH button on a different (and empty) tab. Tried removing all the conditional formatting again. Un-merged all the cells. Nothing worked. And I thought "Alright, well, this tab is doomed...and I've just lost 4 days for nothing".
THE SOLUTION
I thought it was ridiculous to even try, to be honest. But that's what solved the problem.
Like I said before, our REFRESH table is 4451 rows long...spreading over 109 columns wide. And while we do need all of those columns...they don't need to be visible. In fact, that whole tab is hidden, and just running in the background. We only access it for maintenance purposes. So I started hidding the columns, only keeping a few unhidden:
B:M - Stock Infos (Symbol, Name, Sector, Index, etc...)
N - First IF criteria
BL - Second IF criteria
BN:CE - Stock Data (Data collected from the internet by the first VBA)
CS:DF - Stock Data (Data collected from the internet by the second VBA)
So 57 columns hidden. Not deleted, just hidden.
Clicked on FULL REFRESH (the one that took 30-40 to ClearContent). The whole table cleared, according to the two IFs criterias...in 5 seconds.
So the issue that prevented the VBA from running smoothly and quick wasn't the absurd number of buttons, the sheer volume of conditional formatting or even the merged cells o the new tab (although all those things probably contributed)...but the fact that columns, in a very large table on an hidden tab...were visible. It didn't matter that the VBA itself wasn't even looking at those columns throughout the whole procedure, that simple fact that they were visibly 'in the way' made it tough to handle apparently.
Why am I sharing this ?
Well, like I said, this community has given me so damn much help/input these past few years...
If sharing this story helps anyone saving 4-days worth of work, well...glad I could contribute to giving you a weekend free !
I'm off to enjoy mine now.
[Mods: Feel free to move this to a more appropriate place on the forum of deleted if this isn't viewed as 'Excel General' related topic]
Bookmarks