PAX dbrw formula overwrite value with vba

[monkey97]

Hey!

How can i use vba code to override the DBRW formulas? No matter how i try the DBRW formulas remain, they are not overwritten with the value. Overall, I would like to see the snapshot function in vba code form.



Thanks.

[WilliamSmith]

Hi my friend, I hope you are having a nice day.

Try this

Code: Select all

dim cell as range
set cell = ActiveCell 'ActiveCell, for each, etc.

Application.EnableEvents = False
cell.Formula = cell.Value 'or .Value2
Application.EnableEvents = True

It will prevent ExcelDNA batch recalculation (e.g. RECALC_XXXX) from triggering.

[WilliamSmith]

Also, you may find this helpful...

Trigger batch recalculation of TM1 formulas on a range, or worksheet. Useful for force re-calc in automated scripts:

Code: Select all

Sub ReCalcRng(rng As Range)

    calc = Application.Calculation
        
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    rng.Dirty
    
    Application.Calculation = calc

End Sub

Sub ReCalcWS(Optional ws)
    
    If IsMissing(ws) Then
    
        Set ws = ActiveSheet
    
    End If
    
    calc = Application.Calculation
        
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ws.Cells.Dirty
    
    Application.Calculation = calc
    
End Sub

[monkey97]

Thank you so much.
The solution was to turn Application.EnableEvents on and off.