When is a workbook too big?

[JeffGrant]

Hi All,

My workbook is currently over 50MB, made up of:

45 sheets. Some of which are very wide but not so deep, whilst are other are small but convenient because the data starts at A1.
Many modules of VBA, with several thousand lines of code. The primary process VBA Routine has 58 ElseIf's in it.
Many PQ queries
and around 50 tables and 50 pivots tables
I have one table that gets added to everyday. It is only 5 columns wide, however, the number of records added every day can be between 500 during the week and 4000 on Fri/Sat/Sun. At the moment, that table is sitting at 147k records and growing for at least another 70 days, so I expect it to reach well over 200k records.
I have a quad core PC with 40Gb of DDR4 RAM.
The PQ code is used to pull data from the web and massage it, then the VBA code works on the resultant PQ tables.
As much as possible, I can do I have converted computations to arrays.
I don't have any conditional formatting, but I do have many blocks of data colour coded for easy viewing.
I recently removed all of the charts and all that did was reduce the size of the workbook by less than a negligible 1Mb.
I have added extra 20Gb of RAM, which sped the code up quite a bit and the workbook stopped crashing

The problem I have is this.

1. As the workbook get bigger, the computer slows down, that is expected.
2. The frequency of various parts of the VBA code being skipped is increasing. For example, I can run the same data over the code twice and get two different results.
3. Pivot tables are not being refreshed by the VBA code and I have to manually refresh them, and
4. I regularly save the workbook without any errors, but the next day when I open it, I get corrupted worked book messages and have to either use the Excel Open & Repair feature, if I do get it open, I then use the Clean feature from M-Z Tools over the VBA code or I have to revert to a copy that is 2 or 3 days old and lose some data.

As much as I read that Excel should handle millions of rows and many complex formulae, I am wondering if I am reaching the limits of Excel and the sales blurbs about its power are just that, exaggerated sales blurbs.
I don't understand why sequential VBA code is being skipped.
I don't understand why the saved workbooks are getting corrupted so regularly without any error messages?
Would it make much difference is I pulled out the growing table and put it in another workbook?
Can my primary VBA sub with 58 ElseIf's be the culprit? I have tried various other structures using CASE and Do's and those structures have not made any substantial difference.
I have tried pulling out the ElseIf's into their own subs, but that just added many more lines of code for no real benefit. Most of the ElseIf's are simply checking for a Yes/No value in a cell. With the ElseIf's, the criteria either passed or failed, and are one line statements to either terminate the routine and move on, or move to the next criteria test. So Mini-subs, just seems to be a waist of time.

Any and all suggestions are very graciously received as this model has been in the making for over two years now, and I now seem to be running into a volume/size related problem.

As always, thanks for all of your contributions.

Jeff.

 

[Shortmeister1]

I hate to say it Jeff, but you are right and I think you've answered your own question. Excel has definite limitations. At the end of the day, Excel just isn't designed for heavy duty databasing. If you are at the edge of its capability, VBA will struggle in the way you've described.

A few thoughts though:

1. If you set screenupdating to FALSE whilst your code is running, it does sometimes work minor performance miracles. You can program the status bar to give regular progress messages too - although once again this will have a performance hit. Don't forget to set it to TRUE at the end of the sub, including error routines.
2. Maybe worth putting some "pauses" in your code so that it can catch up?
3. You can also help yourself out by making sure that formatting and formulae etc. is only used where necessary - i.e. not entire rows or columns, but unfortunately, it sounds like you've maxed out.
4. You could also try saving it as a binary XLSB, but whilst it will drastically reduce the over size of your workbook I'm not convinced it will help your memory issue.

So what am I really saying? You should really be working in a server-side database e.g. SQL server and making use of industrial level tools such as Power BI, SSRS or Tableau or even Excel for summarised reports. I think your problem is going to get worse not better as the data increases in size.

Sorry!!

 

[johnnyL]

I would also add that if you can supply a link to a copy of your workbook, more assistance will be able to be given as to where the bottlenecks are.

 

[Michael M]

You could also have a look at reducing file bloat, if there is any.
If you do a CTRL + End on any all sheets, does the cursor go waaay down the page beyond your work area ??
If so try running this code on a COPY of your workbook, just to be safe !!
The safe / reopen and see if the file saze has changed.

Sub MM1()
    Dim x As Long, LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    For x = 1 To Sheets.Count
        With Sheets(x)
            LastRow = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
            LastCol = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[JeffGrant]

Than you all for your rapid response, I am tied up at a trade show this week, so will review your suggestions at the weekend.

Thanks again

 

[RoryA]

50MB is quite large, but most of what you describe sounds more like issues with the code, not the size of the workbook. For instance, VBA should not skip lines just because you have a large workbook. Based on what you have said is in the workbook, it sounds more like the code is running before the data it's looking at has actually refreshed.

 

[JeffGrant]

Hi Rory, I have been think about what you have said and believe that once again you have hit the nail on the head. As i thought about it, the code that I know it "skips" is always related to a pivot table. Therefore, if the code is referring to an outdated pivot table, then that would give the appearance of the code being skipped.

I will now look to implement some form of check to make sure that the pivot table is current before the code progresses.

I am thinking along the lines of taking a snap shop of the date/time when the pivot table was last updated and then using something like the Now() function so that:

If Now() - Date/Time snapshot > 10minutes, then
Wait 30 seconds
Else

 

[JeffGrant]

Hi Rory, I have been thinking about what you have said and believe that once again you have hit the nail on the head. As I thought about it, the code that I know "skips" is always related to a pivot table. Therefore, if the code is referring to an outdated pivot table, then that would give the appearance of the code being skipped.

I will now look to implement some form of check to make sure that the pivot table is current before the code progresses. Whereas before I have assumed that the pivot table was current.

I am thinking along the lines of taking a snapshot of the date/time when the pivot table was last updated and then using something like the Now() function so that:

If Now() - Date/Time snapshot > 10minutes, then
Wait 15 seconds
Else
' Do nothing
End

I think that should cover it.

--- --- ---

Hi MichaelM, I believe you are also correct in the there is a likelihood of workbook bloating. I have struct that issue before but it slipped my mind. Thanks for the de-bloating code, I will be implementing that very quickly.

Because of the size of this workbook, I am thinking something like Every tenth time the work book is opened, run the de-bloating code as part of of a workbook-open maintenance process to automate it.

--- --- ---
Hi JonnyL,

I appreciate life can always be a lot simpler if the code was made available for members of this forum to review, however, it is not always possible to provide open access to confidential data.

--- --- ---

Hi MartinShort,

Since I read your reply a few days ago, I have really been thinking about your suggestion. As much as I love the idea of going to more powerful tools such as sql engines, the issue I have is that for the majority of the data that is being manipulated in "blocks" and an sql type solution would be great because i use PQ a lot for that and am comfortable with, but not astute with, sql type structures.

But after the raw data is washed, the model gets to a point where an integral part is to copy data from a live webpage, then paste into the model, then VBA takes over to run through the 50 odd elseif's, which essentially ends up with one line yes/no checks of the VBA massaging of the smaller blocks.

This is the part that I fall over. Going from block treatment to one cell - yes / no criteria.

VBA is very simple to check the Yes/No cell value, then generate userforms to publish the result. I don't understand how a single cell evaluation can be handled using something like sql.

Is this where something like PowerBuilder fits in?

--- --- ---
Again, my sincere thanks and gratitude for all of your support.

 

[johnnyL]

Can you post the section of code that pertains to all of the If/ElseIfs ?

 

[Shortmeister1]

Hi MartinShort,

Since I read your reply a few days ago, I have really been thinking about your suggestion. As much as I love the idea of going to more powerful tools such as sql engines, the issue I have is that for the majority of the data that is being manipulated in "blocks" and an sql type solution would be great because i use PQ a lot for that and am comfortable with, but not astute with, sql type structures.

But after the raw data is washed, the model gets to a point where an integral part is to copy data from a live webpage, then paste into the model, then VBA takes over to run through the 50 odd elseif's, which essentially ends up with one line yes/no checks of the VBA massaging of the smaller blocks.

This is the part that I fall over. Going from block treatment to one cell - yes / no criteria.

VBA is very simple to check the Yes/No cell value, then generate userforms to publish the result. I don't understand how a single cell evaluation can be handled using something like sql.

Is this where something like PowerBuilder fits in?

--- --- ---
Again, my sincere thanks and gratitude for all of your support.

Hi Jeff

I'm afraid that I know absolutely zilch about Power Builder.

A couple of additional suggestions though.

1. If you are able, try using SELECT...CASE. It's usually better optimised as it exits as soon as it finds a match.
2. The other thing is that you might find this will work better if you did it in Python and just imported the results into Excel. (Afraid this isn't the place to go any deeper into this, but there is plenty on-line.

One further thing, you can set Pivot Tables and PQ to refresh every time Excel is opened. I usually set this in each case as I often forget to update my tables.