TM1 Forum

Posted: **Wed Feb 14, 2024 2:53 pm**

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.

Posted: **Wed Feb 14, 2024 4:13 pm**

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.

Posted: **Wed Feb 14, 2024 4:18 pm**

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

Posted: **Thu Feb 15, 2024 8:56 am**

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

TM1 Forum

PAX dbrw formula overwrite value with vba

PAX dbrw formula overwrite value with vba

Re: PAX dbrw formula overwrite value with vba

Re: PAX dbrw formula overwrite value with vba

Re: PAX dbrw formula overwrite value with vba