Updating Functions within a Sub

[aerodoc]

I have sub that changes inputs, which affect functions. At the beginning of the sub, a form starts that says "calculating". At the end of the sub, I the form unloads.

The problem is that I want to form to unload only after after function calculations are completed. I have many functions and it takes several seconds for them update. The sub is not aware that this still needs to occur though (i.e. the functions and the sub don't talk to each other).

So how do I adjust the sub and make sure the form does not unload until all of the functions have finished updating?

[mdmackillop]

Set a flag to be tested before the form unloads

If Flag = True then Unload "Userform"
You could include this in a Do .. Loop or timed loop.

[aerodoc]

Just getting around to further looking into this. Do you have a more extensive example? I have never used a Flag and not really sure how to go about it. Thanks.

[Sebastian H]

I think what mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) meant was simply a Boolean variable. A more descriptive name than "Flag" would have been "AllComputationsDone". In other words, you need to let functions and the sub talk to each other. A single Boolean variable would work nicely if you know which function gets evaluated last:

Public ComputationDone As Boolean

Sub Main()
ComputationDone = False
' some code here to kick of your computations
' wait till computations are done:
Do
' nothing
Loop Until ComputationDone
If ComputationDone Then Unload "Userform"
End Sub

Function LastFunction()
' my computations
ComputationDone = True
End Function

However, that would use a lot of useless computation power in the Do ... Loop; maybe even to the extent that your functions don't get their fair share of computation time. Come to think of it, I don't even know if VBA does any multitasking. That's why mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) mentioned a timed loop; I think what he meant is described here (http://www.vbaexpress.com/forum/showthread.php?t=8183).

Another question is if you even know which function will be computed last. If that isn't the case, then you could use a Boolean array, with an entry for each function. But then again, some functions may be called several times. I'm not sure how to solve that problem.

[aerodoc]

Hmmm...the problem is that I have 1 function repeated hundreds of times. I am not sure if there is any way to determine which of those would be computed last, especially if they were to span multiple sheets.

A timed loop may be a partial answer, but would be too slow or too fast, depending on the computer. I am not sure if that is good either.

[Sebastian H]

Actually, who says that VBA doesn't wait for the calculation? Try the following:

' Set Calculation to manual, but not before you remember current setting:
Dim CurrentCalcSetting As Integer
CurrentCalcSetting = Application.Calculation
Application.Calculation = xlCalculationManual
' Now, do all the changes you need to do in the spreadsheet:
' ...
' Calculate and reset calc setting:
Calculate
Application.Calculation = CurrentCalcSetting
' Done - now we can close shop.
Unload "Userform"

[Paul_Hossler]

Sebastian --



' Calculate and reset calc setting:
Calculate
Application.Calculation = CurrentCalcSetting
' Done - now we can close shop.
Unload "Userform"


I think that if the user gets tired of waiting for the calculating to stop, and hits Esc, the macro will contine, closing the UserForm, but leaving the formulas only partially updated.

I've seen ways to turn off and turn on all user inputs, but I (personal opinion) don't like to do that

Paul

[Paul_Hossler]

One thing to try (at least in 2007/2010 that 'seems' to work for my brief tests:



Sub drv()
Dim iOriginalCalculation As XlCalculation
Dim iOriginalCalculationKey As XlCalculationInterruptKey
Dim iOriginalScreenUpdating As Boolean
'save original state
With Application
iOriginalCalculation = .Calculation
iOriginalCalculationKey = .CalculationInterruptKey
iOriginalScreenUpdating = .ScreenUpdating
'set
.Calculation = xlCalculationManual
.CalculationInterruptKey = xlNoKey
.ScreenUpdating = False
End With
'do stuff
ActiveSheet.UsedRange.Clear
ActiveSheet.Cells(1, 1).Resize(10000, 100).Formula = "=1+2+3+4+5+6"
'start calculating
Load UserForm1
Call UserForm1.Show(vbModeless)
While Application.CalculationState <> xlDone
Application.Calculate
DoEvents
Wend
'done calculating
UserForm1.Hide
Unload UserForm1
'reset original state
With Application
.Calculation = iOriginalCalculation
.CalculationInterruptKey = iOriginalCalculationKey
.ScreenUpdating = iOriginalScreenUpdating
End With
End Sub


Paul

[Sebastian H]

I think that if the user gets tired of waiting for the calculating to stop, [bad things may happen] Agreed. this is a valid concern, and in my first solution, I had been thinking of printing out something on the status line. But I skipped that since there wasn't anything to report, and it seemed like overkill to introduce a counter just for that purpose. Moreover, the status line is not that obvious, so it wouldn't be a safe way to rule out that possibility.

In the second case, though, where we already have a dialog open, I think it should be fine to just display a message like "Computing fields, this will take several seconds. Please don't hit Esc." That should be pretty obvious, and if someone isn't patient for a few seconds, than he or she will run into a lot worse problems anyway. I'm all for usability, but this is not a shrink-wrapped application for the big public; you can't hold an individual macro to that high standard.