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.

'***********************************************
'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

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.

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

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 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.

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.

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).

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.

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.

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