Improve Cube Function Speed?

[largeselection]

I'm not sure if this is possible, but does anyone have any recommendations on how to improve calculation speed of cube functions?

I have a workbook which builds a report and thus has a lot of cells which are basically just cubevalue functions. It takes on average 5 minutes if I make a change (like select a different person in the filter I have set up) to refresh all of the information.

Any ideas on how to improve processing time? It seems like they are processing one at a time - during the 5 minutes i'll notice some cells will come back, then some others, then others, then they're all done. Maybe I can have them all refresh at once somehow?

Or would it help to minimize excel while it's refreshing and then re-maximize when it's done via macro?

Any ideas are appreciated.

 

[woned]

Before your calculations, write :

Application.Screenupdating = false

re-enable it at the end of your macro.

If that doesnt fix it, then do :

Application.enableevents = false

Also put it back to true at the end of the macro.

 

[largeselection]

Hi! Thanks for the great suggestion!

It's actually not running a macro at the moment - they are all just cubevalue or other cube functions in the cells. They recalculate when I change a dropdown in the filter box.

How could I put into code that I want to disable screen updating when I change the filter drop down, then re-enable screen updating when it completes calculating?

 

[woned]

application.calculation = xlcalculationmanual

This way no functions will ever re-calculate.

Now after you chose a person in the dropdown list, write

application.screenupdating = false
application.calculation = xlcalculationautomatic
application.calculation = xlcalculationmanual
application.screenupdating = true

This way you can control when you want to re-calculate your sheet (putting it back to automatic for an instant will automatically (duh) recalculate), as well as when you want to "refresh" the screen.

I'm not sure if thats exactly what you're looking for, but it should at least help you understand how to solve your problem.

 

[largeselection]

So I tried putting in some code, but it isn't working. It only works if I click into cell B1. The problem that I'm having is that B1 is the value of a pivot table filter drop down. So I thought I could click on B1 (where the filter is), and select a different person's name and since I made that change and B1 is different then it would fire the macro which basically says to not allow the screen to update until it's done calculating.

I've posted the code below. Any ideas on how I can get it to run when I select a different person in the pivot table filter list?

In the worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address = "$B$1" Then
    
    MsgBox ("Calculating...")
    Application.ScreenUpdating = False
    Do Until Application.CalculationState = xlDone
        DoEvents
    Loop
    Application.ScreenUpdating = True
    
    End If
    
End Sub

 

[largeselection]

Upon some further inspection...it seems that what is slowing me down is the many "running background query" operations that occur. So I can't put in a code to wait until calculation completes, because there are many small calculations - so it will start one background query, finish, calculate, then the macro code would continue. I don't think there's any way to disable "enable background query" so it seems I may be out of luck.