TM1 Tools Issues And Discussions (Was *KEY_ERR Tracer)

[Martin Ryan]

I've posted this before, but wanted to add it to this thread.

It's a piece of VBA code that tells you where a DBRW formula in Excel is picking up data from in a cube. If there is a *KEY_ERR then it will highlight what is wrong with your formula. Useful if you've got complex reports and also can be useful for building DBRW formulae by hand.

If someone makes any enhancements, please post back. I'd like to see an alias function added.

The usual disclaimers apply... no warranties and not responsible for what the code does to your TM1 server, Excel or anything else.

Martin

(Admin Note: Subject line modified since this thread now relates to all of TM1 Tools, not just ther tracer)

[Martin Ryan]

New version that includes a tool that zips through and makes all worksheets values only, removing any TM1 formulae - useful for quickly emailing a file off to a non-TM1 user.

Could be further revised to only get rid of TM1 formula, instead of ALL formula, the methodology is in this forum somewhere if someone wants to dig it out and apply it.

Martin

[Wim Gielis]

Regarding makeAllSheetsValues() and Sub pasteSheetAsValues(), see also: http://www.rondebruin.nl/values.htm (and other pages on his website).

Wim

[Steve Rowe]

Version 0.2a

Added a new sub to clear only TM1 formula from all sheets in the workbook.
If anyone else wants to add useful TM1 type utilities to the tool bar feel free to add to the code, would be nice to have our own little toolbar.
Something that needs doing is to add the script that puts the tool bar somewhere nicer that I saw someone had posted

Usual disclaimers apply,

Detailed Changelog

Code: Select all

Sub ChangeLog()

''''Version 0.1 Martin Ryan - Original version


''''Version 0.2 Steve Rowe - Minor bug fixes and added TM1 Only clear macro - 17/04/09
'Added option explicit and defined a variable (Sheet)
'Added code to solve issue around selection of hidden worksheets.
'Added event enabling and disabling
'Added black list macro
'Added version number


End Sub

--Edit Added the file, missed the fact that the xla was refused and had to zip it

[Steve Rowe]

Small update as I had not changed the tool bar to reference the new macro, so both icons were running the same macro, doh! Thanks to Martin for pointing this out!

[Steve Rowe]

Sorry all further fix in the TM1 only macro that allows for formula with no opening bracket "(" that was causing the macro to fail. (Like = server &"Management Information")

[Martin Ryan]

Latest version now includes ability to trace DBRA, DBSA and VIEW formula. I've also cleaned up the toolbar build, based on Alan's suggestions in the General forum.

For the sake of simplicity I've removed the button for the Values Only macro, and left only the remove TM1 formula, which doesn't remove Excel formula. If you want to give yourself and users the option of both you'll just need to uncomment the button addition in the BuildToolbar sub. I don't think my users could handle the nuance between the two, so have kept it simple by keeping the old one out.

As an added bonus I've also put in some documentation on how it works, including some screenshots.

Any problems, full refunds are available.

Martin

[Steve Rowe]

Next change required should anyone choose to take up the challenge. Method to close the formula trace easily so that you can avoid having to move the mouse to click on the cross to close the dialog.

Maybe something on the mouse move event or something...

Also the aproach at the moment doesn't work for more complex TM1 formula like
=IF(NOT(DBRA("traxx:management information",TRIM(LEFT(A28,100)),$C$19)=$B$14),DBRA("traxx:management information",TRIM(LEFT(A28,100)),$C$19),DBRA("traxx:management information",TRIM(LEFT(A28,100))&DBRA("traxx:Sub-analysis 1",E28,$C$19)&" N",$C$19))

[Wim Gielis]

This is somewhat better code with respect to makeAllSheetsValues and pasteSheetAsValues:

Code: Select all

Sub makeAllSheetsValues()
' This code saves the active workbook with a new name, then
' cycles through the sheets in the worbook and pastes them as values
' removing all formula
    Dim saveAsName As String, thisSheet As String, Sheet As Worksheet, SheetVisible As XlSheetVisibility
    saveAsName = ActiveWorkbook.FullName
    saveAsName = Left(saveAsName, Len(saveAsName) - 4) & "valuesOnly.xls"
    saveAsName = Application.GetSaveAsFilename(saveAsName)

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

    If saveAsName = "False" Or saveAsName = "" Then Exit Sub
    ActiveWorkbook.SaveAs saveAsName
    
    For Each Sheet In ActiveWorkbook.Worksheets
        'collect the visible status of the worksheet
        SheetVisible = Sheet.Visible
        'unhide the sheet
        Sheet.Visible = xlSheetVisible
        'Sheets(Sheet.Name).Select
        pasteSheetAsValues Sheet
        'set the visible status back to what it was before
        Sheet.Visible = SheetVisible
    Next Sheet

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    ActiveWorkbook.Save
    MsgBox "Workbook has been saved as values only copy", vbInformation
End Sub

Sub pasteSheetAsValues(sh As Worksheet)

    With ActiveSheet.UsedRange
        .Value = .Value
    End With

End Sub

Wim

[Wim Gielis]

Regarding the line of code:

For Each sheet In ActiveWorkbook.Sheets

Watch out for Chart sheets. The code will hang in case a Chart sheet is encountered. Use:

For Each sheet In ActiveWorkbook.Worksheets

[Steve Vincent]

i like that. would be even better if it only changed TM1 specific formulas, but its a good place to start

[Steve Vincent]

Regarding the line of code:

For Each sheet In ActiveWorkbook.Sheets

Watch out for Chart sheets. The code will hang in case a Chart sheet is encountered. Use:

For Each sheet In ActiveWorkbook.Worksheets

Can't get it to work, it fails to compile on the line highlighted;

For Each Sheet In ActiveWorkbook.Worksheets
'collect the visible status of the worksheet
SheetVisible = Sheet.Visible
'unhide the sheet
Sheet.Visible = xlSheetVisible
'Sheets(Sheet.Name).Select
pasteSheetAsValues Sheet
'set the visible status back to what it was before
Sheet.Visible = SheetVisible
Next Sheet

[Steve Rowe]

Did you copy the other sub in?

Sub pasteSheetAsValues(sh As Worksheet)

With ActiveSheet.UsedRange
.Value = .Value
End With

End Sub

Might need to change the highlighted line to

Call pasteSheetAsValues (Sheet)

Otherwise nor sure, over to Wim

[Steve Vincent]

Did you copy the other sub in?

Sub pasteSheetAsValues(sh As Worksheet)

With ActiveSheet.UsedRange
.Value = .Value
End With

End Sub

Might need to change the highlighted line to

Call pasteSheetAsValues (Sheet)

Otherwise nor sure, over to Wim

Ah. I had to edit that sub because it refuses to run on its own - i wanted the option of valuing either a single sheet OR a full book. Guess that code only works doing the full book, which is a shame

[dubs]

i cant get this to work, it shows all red rows and nothing in cols 1 & 3 for all rows even though the formula works and recalculates ok.

any ideas?

[Wim Gielis]

Hi all

1/

Code: Select all

Sub pasteSheetAsValues(sh As Worksheet)

With ActiveSheet.UsedRange
.Value = .Value
End With

End Sub

My bad, sh is an argument in this sub. But I forgot to use it in the line

Code: Select all

With ActiveSheet.UsedRange

that should read:

Code: Select all

With sh.UsedRange

2/

Code: Select all

Call pasteSheetAsValues (Sheet)

does exactly the same as:

Code: Select all

pasteSheetAsValues Sheet

. As said above, the code of pasteSheetAsValues should be in the code. The sub does not run on its own since there is an argument that should be passed to it (a variable declared as Worksheet for instance)

3/

Regarding the whole workbook or 1 sheet:

For 1 sheet it would be something along the lines of:

Code: Select all

Set Sheet = ActiveSheet
'collect the visible status of the worksheet
SheetVisible = Sheet.Visible
'unhide the sheet
Sheet.Visible = xlSheetVisible
'Sheets(Sheet.Name).Select
pasteSheetAsValues Sheet
'set the visible status back to what it was before
Sheet.Visible = SheetVisible

Hence, no loop.

Wim

[Wim Gielis]

dubs

Perhaps you can add a few small screenshots to your post (or a new post)? (without sensitive information of course).

Wim

[dubs]

Cheers Wim,

yes may help?

it is is strange because if i do an alt+f9 then it all calculates correctly so the calculations work

[Steve Vincent]

Cheers Wim

For others, the full sub that you can add as a menu item / button etc to value just the current sheet is;

Code: Select all

Sub pasteSingleSheetAsValues()
Dim Sheet As Worksheet, SheetVisible As XlSheetVisibility

Set Sheet = ActiveSheet
'collect the visible status of the worksheet
SheetVisible = Sheet.Visible
'unhide the sheet
Sheet.Visible = xlSheetVisible
'Sheets(Sheet.Name).Select
pasteSheetAsValues Sheet
'set the visible status back to what it was before
Sheet.Visible = SheetVisible

End Sub

[Steve Vincent]

Cheers Wim,

yes may help?

it is is strange because if i do an alt+f9 then it all calculates correctly so the calculations work

dubs, which addin did you try? i just grabbed the latest from Steve and it's fine. The tracer is missing all the TM1 bits of info, so are you logged in (stock question i ask everyone!) and do you have the admin license rights? Excel can sometimes loose the link to TM1 even if the server explorer looks fine so might be worth closing excel and trying again as a start