Thoughts on Workbook Optimization Tools

[Greg Truby]

[keywords to help future searchers hit this thread: reduce calculation recalculation optimize tools formulas]

[I've run through all the threads that search turned up and that I thought looked promising; but I didn't come across a thread that invited an open discussion of this topic. If I missed a key thread, please post a link to it. -- Greg]

Here's the deal. Using knowledge gained by reading books and mostly learning from the formula wizards around here I have created a monster. The workbook has all sorts of amazing array formulae, dynamic named ranges/named formulae, dynamic validation (dependent lists), VBA [userforms (ever programmed a treeview control? that's fun); forcing macros enabled; class modules] -- you name it; it's probably in this feller. It is quite a marvel. And it is slow. Not grinding to a halt slow. But it's mission is to allow for analysis of various segments of leadtime to various overseas customers and with each change of customer/segment/order class the recalcs take several seconds. It makes it a royal PITA to really hunt down specific logistics bottlenecks.

My question is not about specifics on how to improve this. There are so many places to look, I literally don't know where to start. My question is on getting help on learning "where to start". So -->

Who sells the best toolkit for quickly identifying the most effective steps one can take to reduce re-calc times?

I have seen FastExcel by Decision Models mentioned many times here. (But they do not list MrExcel.com among their favorite Excel websites which, of course, makes me wonder!) I would like to hear feedback or recommendations from folks who have used workbook analysis tools. My impression is that such tools can really be helpful; but I would most definitely not enjoy going out on a limb to talk my boss into investing in the software only to yield modest gains in speed. Your thoughts please!

Much obliged,

 

[FastExcel]

Greg,

There are some VBA profilers out there: Stephen Bullen has one on the CD in the excellent book 'Professional Excel Development'

I try to avoid using the forums to promote FastExcel myself as I feel this would be something of a breach of the rules/netiquette , (but of course since you asked its a wonderful product <VBG> )

I would be happy to answer any questions on FastExcel anyone has via private email (you can find my address on my website)
http://www.DecisionModels.com

 

[Greg Truby]

To Aladin et al,

Just a quick update. I am slogging my way through this WB today. A couple of items...

Adding values by hand (typing or cut & paste) will trigger the autofill into other cells that have formulae that copy downwards. Adding values to the list via VBA likewise causes autofilling. In my test list col A had hard values. Col's B & C had formulae.

Sub AddSomethingToColA()
    Dim r As Range
    Set r = Range("a1000").End(xlUp).Offset(1)
    r = "k"
End Sub

Sub CopySomethingToColA()
    Dim r As Range
    Set r = Range("a" & Rows.Count).End(xlUp).Offset(1)
    Range("H1:H2").Copy r
End Sub

I have names ranges for columns B & C and they are resizing whether new items are added by hand or by code.

So far, the only "weakness" I'm finding is that the autofill does not trigger change events on the cells being autofilled. So, if I want to keep an eye on column C and do something anytime it gets new data, I'm going to have to use the ListObject's .DataBodyRange property, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [TestColumnC]) Is Nothing Then MsgBox "direct hit"
    If Not Intersect(Target, Target.Parent.ListObjects("List1").DataBodyRange) Is Nothing Then MsgBox "indirect hit"
End Sub

Only returns "indirect hit" for changes to column A which result in formulae getting autofilled down into cells in B & C. If I learn anything else interesting, I'll post back.

 

[Greg Truby]

A couple more discoveries about lists:

1. Whenever your selection takes you from outside a list to inside a list or vice-versa you trigger a calculation (if Calc mode is automatic); which may (or may not) be related to item #2...
2. Whenever you select inside and then back outside, Excel seems to literally be deleting the cell at the bottom of the list in the "insert record" row. For if I apply conditional formatting that evaluates the cell below the cell with the conditional formatting everything works fine until I click outside the list and then back on the bottom-most item in the list. If I call up the CF dialog, the formula that had been referencing the cell underneath now shows a #REF error.

 

[Greg Truby]

Can't really tell if anyone is still paying a lick of attention anymore... but at least for me, trying to control AutoFilter on a list datablock with VBA proved a bit twitchy. Today alone, I managed to hang Excel at least a dozen times. After much trial and error, the following appears to be somewhat cooperative (at least I haven't crashed the app for a while). The main bit is ACTIVATING the sheet first. Instead of turning on Autofilter in the regular manner, I am using the ListObject's .SHOWAUTOFILTER property and selecting a cell inside the list. Again, this resides inside a pretty complex workbook, so it may be that I have other factors that are causing more instability than the average workbook might experience? But just maybe this will save someone else from a day of 'cause my forehead is about a bloody pulp after today...

    ' _____Filter the data set on All Data worksheet_____
    
    lngCalcMode = Application.Calculation
    booEvents = Application.EnableEvents                                        '  _________________
    Application.Calculation = xlCalculationManual                               ' | Calculation OFF |
    Application.EnableEvents = False                                            ' | Events      OFF |
                                                                                '  ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
    Application.ScreenUpdating = False
    wsAllData.Activate
    With wsAllData.ListObjects("List_AllData")
        .HeaderRowRange.Cells(1).Select
        Set rngAllCurr = Union(.HeaderRowRange, .DataBodyRange)
        Set rngAllCurr = rngAllCurr.Resize(, 6)     ' only want to copy 1st 6 columns
        .ShowAutoFilter = True
        For f = 1 To wsAllData.AutoFilter.Filters.Count
            .Range.AutoFilter Field:=f
        Next f
        With .Range
            .EntireRow.Hidden = False
            If IsEmpty(Range("ReportEndDate")) Then
                .AutoFilter Field:=ge_AllDataCols.allDate, Criteria1:=Format(Range("ReportDate"), strDateFormat)
            Else
                .AutoFilter Field:=ge_AllDataCols.allDate, _
                            Criteria1:=">=" & Format(Range("ReportDate"), strDateFormat), _
                            Operator:=xlAnd, _
                            Criteria2:="<=" & Format(Range("ReportEndDate"), strDateFormat)
            End If
            .AutoFilter Field:=ge_AllDataCols.allCustName, Criteria1:=Range("NPC")
            .AutoFilter Field:=ge_AllDataCols.allTimeGroup, Criteria1:=Range("OrderType")
        End With
    End With

    ' _____Copy the filtered data set to Subsets worksheet_____

 

[Greg Truby]

Another thing I just learned about lists...I was trying to figure out why my Custom Views... menu option is disabled... straight from the Excel help files...

Note You cannot create a custom view when a worksheet contains an Excel list. If one or more worksheets contain an Excel list, the Custom Views command is disabled for the entire workbook.

...just peachy...