VBA in modular workbooks

Hi Jeffrey,

At this stage, the manipulation of content within module components using VBA should be limited to content within assumptions cell blocks. A common example of this might be running a goal for something like a debt payback period analysis, etc.

The key to doing this without Modano slowing things down is temporarily disabling Excel events capture, and then re-enabling it after running your code. I normally also temporarily set Excel calculation to manual, as follows:

Sub Enter_Assumptions()
    
    'Example of VBA-based assumptions entry into a module component assumptions cell block.
    
    'Declarations:
    Dim xlcalcPrior As XlCalculation
    
    'Removes Modano events capture:
    Application.EnableEvents = False
    
    'Ensures manual calculation:
    With Application
        xlcalcPrior = .Calculation
        .Calculation = xlCalculationManual
    End With
    
    'Allows for errors:
    On Error GoTo RestoreEnableEvents
    
    'Enter code here to do things to assumptions...



        
    'Ensures calculation and events are always restored even if errors:
RestoreEnableEvents:
        
    'Restores prior calculation method:
    Application.Calculation = xlcalcPrior

    'Restores Modano events capture:
    Application.EnableEvents = True

End Sub

I've attached a simple example of this - which enters some random numbers into an assumptions cell block within a revenue module component - so you can see it for yourself.

Re the GoTo statement - I agree that they are generally a no-go zone, particularly in non-VBA applications, but I find them pretty handy in smaller VBA macros instead of explicity handling errors in each section of code. It's a bit quick and dirty, but it works well. In larger procedures, I'd certainly avoid them.

Re removing Excel events capture - Modano captures the sheet change event for processing, as this is how it detects changes made by users when they enter formulas, etc. For assumptions cells, it doesn't do much but it's still a lot slower when running loops and goal seeks, etc., than it is without events being captured. You'll notice the difference if you create a larger procedure and try it with and without removing Excel events capture.