Clear TM1 Formulae from Excel

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Steve Vincent
Site Admin
Posts: 1048
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 » Tue Sep 16, 2008 10:39 am

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: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

User avatar
Alan Kirk
Site Admin
Posts: 5725
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Useful Code

Post by Alan Kirk » Tue Sep 16, 2008 10:59 am

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 » Wed Oct 26, 2011 11:12 am

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: 5725
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Clear TM1 Formulae from Excel

Post by Alan Kirk » Wed Oct 26, 2011 10:02 pm

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 » Fri Jun 24, 2016 11:59 am

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: 521
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Clear TM1 Formulae from Excel

Post by gtonkin » Sat Jul 02, 2016 4:46 am

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 » Fri Jul 08, 2016 8:33 am

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 » Mon Nov 28, 2016 5:31 pm

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: 521
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Clear TM1 Formulae from Excel

Post by gtonkin » Mon Nov 28, 2016 6:23 pm

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: 3006
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Clear TM1 Formulae from Excel

Post by lotsaram » Mon Nov 28, 2016 9:46 pm

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: 44
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 » Wed Nov 30, 2016 3:52 pm

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

Post Reply