Page 1 of 1

Clear TM1 Formulae from Excel

Posted: Tue Sep 16, 2008 10:39 am
by Steve Vincent
Some VBA for use in Excel (tested in xL 2003) which will find any TM1 formulae and copy / paste value it. Think we got this via the old forum but can't remember who from, have altered it slightly to deal with some other types of TM1 formula like SUBNM.

Code: Select all

'***********************************************
'macro to remove TM1 formulae
'***********************************************
Sub RemoveTM1Formulae()
'replace TM1 formulas with their current values

Dim ws As Worksheet, AWS As String, ConfirmReplace As Boolean
Dim i As Integer, x As Integer, OK As Boolean

If ActiveWorkbook Is Nothing Then Exit Sub
    ConfirmReplace = False
    AWS = ActiveSheet.Name
    Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    OK = DeleteLinksInWS(ConfirmReplace, ws)
If Not OK Then Exit For
    Next ws
    Set ws = Nothing
    Sheets(AWS).Select
    Application.ScreenUpdating = True

End Sub


Private Function DeleteLinksInWS(ConfirmReplace As Boolean, _
ws As Worksheet) As Boolean
'replace formulas with their values

Dim cl As Range, cFormula As String, i As Integer

DeleteLinksInWS = True

If ws Is Nothing Then Exit Function

If ws.Name = "About" Or ws.Name = "Send to TM1" Or ws.Name = "Assumptions" Or _
    ws.Name = "Summary" Or ws.Name = "A - Not In Use" Then Exit Function

   ws.Activate

    
For Each cl In ws.UsedRange
    cFormula = cl.Formula
    
If Len(cFormula) > 0 Then
    If Left$(cFormula, 5) = "=SUBN" Or Left$(cFormula, 3) = "=DB" Or Left$(cFormula, 5) = "=VIEW" Or _
    Left$(cFormula, 8) = "=IF(SUBN" Or Left$(cFormula, 6) = "=IF(DB" Or Left$(cFormula, 8) = "=IF(VIEW" _
    Or Left$(cFormula, 4) = "=(DB" Then
        If Not ConfirmReplace Then
            cl.Formula = cl.Value
        Else
            Application.ScreenUpdating = True
            cl.Select
            Application.ScreenUpdating = False
            
            If i = vbCancel Then
                DeleteLinksInWS = False
                Exit Function
            End If

            If i = vbYes Then
                On Error Resume Next
            ' in case the worksheet is protected
                cl.Formula = cl.Value
                On Error GoTo 0
            End If
        End If
    End If
End If

Next cl
Set cl = Nothing
Application.StatusBar = False
End Function



Re: Useful Code

Posted: Tue Sep 16, 2008 10:59 am
by Alan Kirk
Steve Vincent wrote:Some VBA for use in Excel (tested in xL 2003) which will find any TM1 formulae and copy / paste value it. Think we got this via the old forum but can't remember who from, have altered it slightly to deal with some other types of TM1 formula like SUBNM.
I'm not sure how, but I missed that post; I wrote my own procedure to do this only a couple of days back. May I suggest one modification which is probably unnecessary for smaller worksheets, but can save a fair bit of time for larger ones?

At present your code loops through the whole used range. My code instead selected the SpecialCells range of the sheet which contains formulas only, ignoring all constants, empty cells and the like. That means that your code won't have to loop through those hangers-on. This is particularly useful when the UsedRange has blown out and extends far beyond the range containing actual data, but that's another story. The following demonstrates the general principle.

Code: Select all

Sub LoopThruFormulas()
    
'Vast oversimplification; this code does not contain error handling
'ALL CODE SHOULD CONTAIN ERROR HANDLING!!!!
'And it does not have a single exit point
'ALL CODE SHOULD HAVE A SINGLE EXIT POINT!!!!
'Who said I'm a control freak,
'WHO, WHO SAID THAT????
'But hey, it makes the point...
  
Dim rng_Formulas As Excel.Range
Dim rng As Excel.Range
    
'If there are no formulas then the following would generate an error
'which we need to ignore.
On Error Resume Next
Set rng_Formulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If rng_Formulas Is Nothing Then Exit Sub

For Each rng In rng_Formulas.Cells

    Debug.Print rng.FormulaR1C1
    
Next
 
Set rng = Nothing
Set rng_Formulas = Nothing

End Sub


Re: Clear TM1 Formulae from Excel

Posted: Wed Oct 26, 2011 11:12 am
by Amylim
Hi,

I am a simple TM1 user( not a clever TM1 adminstrator), and have limited knowledge on VBA.
In my work, I work with a lot of TM1 formulas in excel to run my numerous reports every month.
I constantly need to draw data and value TM1 formula to send to users who lack TM1 access.
I also constantly need to trace formula to see which cube, which dimension.

Previously, I downloaded an old version of TM1 add in tools from one of the forums.
"TM1 Tools add in releases"

But I noted that when I hit the value TM1 formula button on the add-in toolbar, it does not value TM1 formula if it starts with a "-" sign or if it is in the middle of a formula. eg -dbrw(x,y,z...) or (-dbrw(x,y,z,s,d,)+a95- d2)


DESPERATE CALL FOR HELP:
-where is the latest TM1 addin
-will it help clear all tm1 formula including the ones above??
-will it still have the wonderful tracer capability

I have very limited support at my work place. :(


Thanks in advance for any help I can get.

A

Re: Clear TM1 Formulae from Excel

Posted: Wed Oct 26, 2011 10:02 pm
by Alan Kirk
Amylim wrote:DESPERATE CALL FOR HELP:
-where is the latest TM1 addin
Look down a few threads. Or here.
Amylim wrote:-will it help clear all tm1 formula including the ones above??
Alan Kirk method = yes, Steve Rowe method = no, Martin Ryan method = yes.
Amylim wrote:-will it still have the wonderful tracer capability
Always.

Re: Clear TM1 Formulae from Excel

Posted: Fri Jun 24, 2016 11:59 am
by Jayme1
Hello , Does anyone know how to combine Steve Vincent's code with Alan Kirk's speed upgrade?

I am using Steve's code but it is very slow on a large workbook. I normally use Alan Zapper add on, but i would like to add a button on this spreadsheet so the user can hardcore the formula themselves.

Appreciated everyone help and for letting me use their code and addons.

Cheers
Jayme

Re: Clear TM1 Formulae from Excel

Posted: Sat Jul 02, 2016 4:46 am
by gtonkin
Hi Jayme,

This is what I have been using for years-looks to be a blend of Steve and Alan's approach.

Code: Select all

Sub Paste_TM1_As_Values()

Dim cell
Dim strFormula As String
Dim bReplace As Boolean

Application.ScreenUpdating = False
Application.EnableEvents = False

' Select all cells with formulas then replace DBRWs etc.
On Error Resume Next
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
For Each cell In Selection.Cells
    strFormula = UCase(cell.Formula)
    bReplace = False
    
    If InStr(1, strFormula, "DBRA(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DBRW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "SUBNM(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DBR(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "ELPAR(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DBS(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "ELCOMPN(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "SUBSIZ(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "VIEW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DIMNM(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "TM1RPTROW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "TM1RPTVIEW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "TM1USER(") > 0 Then
        bReplace = True
    End If
    
    If bReplace = True Then
        cell.Formula = cell.Value
    End If
Next
                
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
Note that I use IF and ENDIF as I just need to know if a TM1 formula is in the cell-even if nested, I am just going to values anyway so checking to the first match should be quicker than comparing for all TM1 related formulae. You can easily add more criteria yourself to the code too. Hope this helps.

Re: Clear TM1 Formulae from Excel

Posted: Fri Jul 08, 2016 8:33 am
by Jayme1
Thank so much for your help.... much appreciated.

Re: Clear TM1 Formulae from Excel

Posted: Mon Nov 28, 2016 5:31 pm
by sof4246
gtonkin wrote:Hi Jayme,

This is what I have been using for years-looks to be a blend of Steve and Alan's approach.

Code: Select all

Sub Paste_TM1_As_Values()

Dim cell
Dim strFormula As String
Dim bReplace As Boolean

Application.ScreenUpdating = False
Application.EnableEvents = False

' Select all cells with formulas then replace DBRWs etc.
On Error Resume Next
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
For Each cell In Selection.Cells
    strFormula = UCase(cell.Formula)
    bReplace = False
    
    If InStr(1, strFormula, "DBRA(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DBRW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "SUBNM(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DBR(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "ELPAR(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DBS(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "ELCOMPN(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "SUBSIZ(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "VIEW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "DIMNM(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "TM1RPTROW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "TM1RPTVIEW(") > 0 Then
        bReplace = True
    ElseIf InStr(1, strFormula, "TM1USER(") > 0 Then
        bReplace = True
    End If
    
    If bReplace = True Then
        cell.Formula = cell.Value
    End If
Next
                
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
Note that I use IF and ENDIF as I just need to know if a TM1 formula is in the cell-even if nested, I am just going to values anyway so checking to the first match should be quicker than comparing for all TM1 related formulae. You can easily add more criteria yourself to the code too. Hope this helps.
Hi gtonkin,

Your code is very good and useful - thank you very much for sharing. I am also extremely unfamiliar with writing VBA code from scratch. Can you please make any suggestions for turning the TM1 formulae into pasted-values but to retain other parts of the grand fomula?

For example, I have this formula in A5 = DBRW($A$1,$A$2,$A$3) and the value/output = 500. But what if my cell were instead = DBRW($A$1,$A$2,$A$3)/100? Instead of the output just being 5, I would like the remaining cell to show 500/100. This is an overly simplistic example but I would just like to know how to maintain the other parts of the cell (which I understand that you did point out in your post that your VBA code would turn the whole thing into a value).

Appreciate yours or anyone else's help with this.

Thank you very much! Sof

Re: Clear TM1 Formulae from Excel

Posted: Mon Nov 28, 2016 6:23 pm
by gtonkin
HI Sof,

I do not have code to do what you want. It will be quite tricky to accomplish, especially if you have limited VBA knowledge.
What you would probably do is the following:
- Determine cells with formulae
- Create a while loop that looks for the TM1 related functions and exits when none found
- When one is found, you will need to write some code to parse - may be find the function then start at the first open bracket, and save the position, count as you find open brackets, subtract as you find close brackets - when all brackets are closed, you have the start and end, copy this function and arguments then evaluate calling application.run
- replace the string with the result where you had the TM1 function
- loop back as there could be multiple functions

I would recommend a recursive function as nested TM1 formulae require you to effectively work from the inside out.
There are probably many ways to solve this, maybe other forum users already have something they can share or a more simple approach.

Re: Clear TM1 Formulae from Excel

Posted: Mon Nov 28, 2016 9:46 pm
by lotsaram
sof4246 wrote:Can you please make any suggestions for turning the TM1 formulae into pasted-values but to retain other parts of the grand fomula?

For example, I have this formula in A5 = DBRW($A$1,$A$2,$A$3) and the value/output = 500. But what if my cell were instead = DBRW($A$1,$A$2,$A$3)/100? Instead of the output just being 5, I would like the remaining cell to show 500/100. This is an overly simplistic example but I would just like to know how to maintain the other parts of the cell (which I understand that you did point out in your post that your VBA code would turn the whole thing into a value).
Suggestion: do not do it!
Using the SpecialCells method with an array of functions to find within formula is about the most efficient approach to the problem. For all the "hits" where a TM1 formula reference is found the cell formula is replaced with the value, no need to re-query the value from the database. If however you want to isolate the TM1 reference and just replace that with its value then this would force a cell-by-cell re-query and evaluation of every single TM1 function. If you have a lot of cells then that is at least one round trip per cell. Don't forget that a TM1 function might be nested with other functions, both TM1 and regular Excel.

So very bad idea from a performance and efficiency point of view.

Then add that the algorithm to isolate the functions withing functions, evaluate each argument and then call each TM1 function individually via Application.Run would be fiendishly difficult.

Then add that you are a self-acclaimed VBA novice.

Bad idea.

Re: Clear TM1 Formulae from Excel

Posted: Wed Nov 30, 2016 3:52 pm
by jcr55
Just an FYI - We use a software package of TM1 utilities from Revelwood called Performance Toolkit.
One of the features is when Excel Perspectives is open, an additional ribbon containing buttons is available.
One button will 'Zap' all TM1 formulas on the current sheet. Another button will 'Zap' all TM1 formulas in the current workbook.
It turns all TM1 formulas into values and leaves the native Excel formulas alone.
Easy enough for end users to use :D

Re: Clear TM1 Formulae from Excel

Posted: Thu Aug 09, 2018 1:21 pm
by jboulay
Hello All,

I know this an old post but any update on how to get around not having to paste values every excel workbook we send out that contains TM1 formulas? What are the latest options below or other optiond?:

1) Use existing VBA macros? some end users don't like to use macros or click on macro button (You really can't people to click)..unless you make macro run automatically?

2) Any new macros or other solutions on other threads?

3) I read somewhere that with the CAFE version of
TM1, the system automatically retrieves data that contains DBR formulas and there was no need to paste values..is that correct?

4) How about new TM1 versions? do they have a work around for this issue?

Thanks

Re: Clear TM1 Formulae from Excel

Posted: Sun Feb 24, 2019 11:37 am
by TJMurphy
Has anyone experienced certain models that work much slower to "zap" than others? We've got an add-in with home-grown VBA code that removes all the TM1 formulae in a model. In terms of logic and flow it's actually very similar to the previous ones shared here. It works fine on nearly all of our models and is generally very quick. But, there is one model, which is not very large (in terms of size (~500k) or numbers of TM1 linked cells), and it can take nearly two hours to zap. There's been a lot of time invested in the layout and building of this report so recreating it is not an option at the moment, it's a 30 page report that covers a lot of business areas and it's delivered to the audience via PDF (yeah, I know).
Some points that might trigger a thought in someone to point me in the right direction to find a fix.
  • I half think this might be Excel actually trying to redo bits of the graphs when it thinks a dependent number has changed (i.e. my zapping). Is it possible to disable this sort of behaviour? When you are working on the model normally though, there is no evidence of everything just stopping while Excel catches up.
  • My VBA zap code has application.enableevents = false and application.screenupdating = false throughout to try and stop the tm1 event handler code jumping in. It also makes sure auto-calc is off first.
  • The TM1Rebuild variable is either deleted or turned off (can't recall which as I don't have my work PC in front of me)
  • There is no VBA code in the model - it's an .xlsx
  • The first few sheets have no graphs - my zap code displays the sheet names as a progress indicator in the status bar and it's clear the code is going slow *before* it hits a sheet with graphs
  • The report does use a lot of conditional formatting (colour coding in green or red depending on variance numbers
  • I've tried doing a "save-as" to a new model and Ctrl-Alt-Shift-F9 with no success
Help, please!

Re: Clear TM1 Formulae from Excel

Posted: Sun Feb 24, 2019 2:00 pm
by lotsaram
If the report is delivered via PDF then why go to the bother of just hard-coding TM1 references, why not just hardcode everything?
(Pseudo code)
Sheet.UsedRange.Copy
Sheet.UsedRange.PasteSpecial Paste:=xlPasteValues

If only sheets and not cells are being iterated then it's going to be a hell of a lot faster and if the presentation format is PDF then it really doesn't matter that the Excel source is completely static.

Re: Clear TM1 Formulae from Excel

Posted: Mon Feb 25, 2019 10:53 am
by TJMurphy
Thanks, that's an idea I hadn't thought about. Given the relatively small numbers of TM1 formulae I was stuck on optimising zapping them.

I did have one other "alternate" idea - I put the entire report up on TM1 Web and took a snapshot from there. That gave very fast performance and was going to be my backstop.

I'll try and alternate "snapshot" the lot in VBA and see how it goes and report back.

Re: Clear TM1 Formulae from Excel

Posted: Wed Feb 27, 2019 8:34 pm
by TJMurphy
As promised, reporting back.

Using a usedrange.value = usedrange.value approach has given me a fast enough zap to work with this model. It's still not as quick as I'd expect (given the size of the model but that kind of fits in with my idea that Excel was trying to redraw all the graphs in the middle of the update. Anyway, it works now so thanks again for the suggestion - I now have two "freeze" icons on my add-in - freeze and "rapid freeze" - product naming never my strong point :lol: