Clear TM1 Formulae from Excel

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Clear TM1 Formulae from Excel

Post 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


If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Useful Code

Post 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

"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Amylim
Posts: 1
Joined: Wed Oct 26, 2011 10:28 am
OLAP Product: tm1
Version: 9.3.1
Excel Version: 2007

Re: Clear TM1 Formulae from Excel

Post 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
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Clear TM1 Formulae from Excel

Post 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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Jayme1
Posts: 14
Joined: Thu Mar 27, 2014 5:30 pm
OLAP Product: turbo intergrator
Version: cognos express
Excel Version: Excel 2009

Re: Clear TM1 Formulae from Excel

Post 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
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Clear TM1 Formulae from Excel

Post 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.
Jayme1
Posts: 14
Joined: Thu Mar 27, 2014 5:30 pm
OLAP Product: turbo intergrator
Version: cognos express
Excel Version: Excel 2009

Re: Clear TM1 Formulae from Excel

Post by Jayme1 »

Thank so much for your help.... much appreciated.
sof4246
Posts: 1
Joined: Mon Nov 28, 2016 5:15 pm
OLAP Product: Not Sure
Version: Cognos TM1 Perspectives
Excel Version: 2007

Re: Clear TM1 Formulae from Excel

Post 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
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Clear TM1 Formulae from Excel

Post 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.
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Clear TM1 Formulae from Excel

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
jcr55
Posts: 54
Joined: Tue May 08, 2012 3:58 pm
OLAP Product: TM1
Version: 9.5.2 FP2
Excel Version: Excel 2007

Re: Clear TM1 Formulae from Excel

Post 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
jboulay
Posts: 10
Joined: Mon Nov 13, 2017 10:04 pm
OLAP Product: TM1
Version: unknown
Excel Version: 2016

Re: Clear TM1 Formulae from Excel

Post 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
TJMurphy
Posts: 74
Joined: Mon May 12, 2008 12:25 pm
OLAP Product: TM1
Version: PA 2.0.6 Local
Excel Version: Excel 2016

Re: Clear TM1 Formulae from Excel

Post 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!
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Clear TM1 Formulae from Excel

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
TJMurphy
Posts: 74
Joined: Mon May 12, 2008 12:25 pm
OLAP Product: TM1
Version: PA 2.0.6 Local
Excel Version: Excel 2016

Re: Clear TM1 Formulae from Excel

Post 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.
TJMurphy
Posts: 74
Joined: Mon May 12, 2008 12:25 pm
OLAP Product: TM1
Version: PA 2.0.6 Local
Excel Version: Excel 2016

Re: Clear TM1 Formulae from Excel

Post 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:
Post Reply