TM1 Forum

Posted: **Thu Jun 04, 2009 2:19 am**

Has anyone tried using TM1 VBA Macro Function in Excel???

Since for those unfamiliar with TM1 may not notice the other Dimension...so instead of using a drop down list connected to TM1's subset...I thought it would be much better to use a Command Button to call the E_PICK function and link it to the cell where the active form is connected to. I was wonder if there is a fucntion of something that would call the "REBUILD CURRENT SHEET" icon available in Excel rather than clicking it.

Posted: **Thu Jun 04, 2009 1:16 pm**

Hi:

Have you tried the TM1REFRESH function from the TM1 add-in. I think this was originally implemented for the now-dead Dynamic Slices, which were replaced by Active Forms. If you call something like the following after allowing your user to do an E_PICK:

Code: Select all

Public Sub AfterEPICK()
' Seems to rebuild the current sheet
    Application.Run "TM1REFRESH"    
End Sub

That should do it. I'm not sure, though, if that TM1REFRESH function will be around forever - I don't remember seeing it in the offical documentation, but it at least exists in TM1 9.4 MR1.

If you were not using E_PICK in this then Action buttons have an option to rebuild the current sheet, too, which also works on TM1 Web.

Regards,
Mike

Posted: **Wed Sep 04, 2013 11:09 am**

Hi Mike

Just to confirm Application.Run "TM1REFRESH" still works to rebuild an Active Form in 10.1.1 FP1.

Thanks for the useful post - as you say, this is an undocumented feature.

Regards

Paul Simon

Posted: **Thu Sep 26, 2013 8:12 pm**

I'm not sure if this is comprehensive but here is a list of all the VBA TM1 Calculation options that I use

Code: Select all

Application.Run "TM1RebuildCurrentSheet"  'activeform sheet rebuild "Alt & F9"
Application.Run "TM1Refresh"                      'activeform sheet rebuild "Alt & F9"
Application.Run("DoTM1Refresh", True)        'activeform sheet rebuild "Alt & F9" with screen updating = true
Application.Run("DoTM1Refresh", False)       'activeform sheet rebuild "Alt & F9" with screen updating = false

Application.Calculate      'workbook calculate "F9"  using this negates the benefits of WAN TM1 functions
Application.Run "TM1RECALC"    'workbook calculate "F9"
Application.Run "TM1RECALC1"   'worksheet calculate "Shift & F9"
Application.CalculateFullRebuild  'rebuild workbook "Ctrl & Alt & F9"

Also one should note VBA TM1 calculations are a two pass calculation. Also screen updating is toggled off/on between the calc passes which might screw up your VBA code.

This is the code process of TM1RECALC, TM1RECALC1 and their corresponding "hijacked" hotkeys

Code: Select all

1. Screen Updating OFF
2. 1st Recalc pass
3. Screen Updating ON
4. 2nd Recalc pass

This is the code process of the activeform buttons from the Excel Ribbon.

Code: Select all

1. Screen Updating OFF
2. Runs error testing on the activeform functions
3. 1st Recalc pass
4. 2nd Recalc pass
5. Runs a bad example of an "Excel Diet" which deletes rows and columns outside of the activeform
6. Screen Updating ON
.

Two takeaways from the code process are depending on your usage of the screenupdating property in your overall vba code, you may need to use application.screenupdating = false after your run the TM1 calc code.

The second take away is how poorly, in my opinion, the TM1 code handles bloated activeforms. If one has an activeform with 10k rows and then rebuilds the activeform and it now only has 2 rows, the activeform is now bloated. If one were to upload this file to TM1 Web, it would be very slow.

Here is my beginners solution for an Activeform Excel diet. Use at your own risk as it was written for the activesheet with a single activeform only.

Code: Select all

Sub Usedrange_Diet()

Dim TotalRow As Long
Dim TotalCol As Long
Dim x As Long
Dim y As Long
Dim z As Long
Dim uRange As String
Dim LastRow As Long
Dim LastCol As Long
Dim LastCell As String
Dim NeededCells As Long
Dim OpportunityCells As Long
Dim iReply As VbMsgBoxResult

On Error GoTo errhandle

'to determine xls vs xlsx
TotalRow = ActiveSheet.Rows.Count
TotalCol = ActiveSheet.Columns.Count

'used range
x = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1
y = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
z = x * y
uRange = Range(Cells(1, 1), Cells(y, x)).Address

'needed range
LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Column
LastCell = Cells(LastRow + 1, LastCol).Address 'lastcell needed + 1 row for activeform
NeededCells = (LastRow + 1) * LastCol


OpportunityCells = (z - NeededCells)
    
    If OpportunityCells < 0 Then  'resize to remove negative opportunity
       OpportunityCells = 0
       iReply = MsgBox("No diet is needed on the active sheet." & vbCrLf _
                & "Would you like to view range used statistics?", vbYesNo + vbQuestion)
                    If iReply = vbYes Then
                        MsgBox ("Used Range:   " & uRange & vbCrLf _
                        & "Total Used Cells:   " _
                        & z & vbCrLf & vbCrLf _
                        & "Needed Range:   $A$1:" & LastCell & vbCrLf _
                        & "Total Needed Cells:   " & NeededCells & vbCrLf & vbCrLf _
                        & "Total Opportunity:   " & OpportunityCells & " cells" & vbCrLf _
                        & "Please note one additional row is preserved for activeforms"), vbInformation + vbOKOnly, "Needed Range"
                    End If
    Else

        MsgBox ("Used Range:   " & uRange & vbCrLf _
                & "Total Used Cells:   " _
                & z & vbCrLf & vbCrLf _
                & "Needed Range:   $A$1:" & LastCell & vbCrLf _
                & "Total Needed Cells:   " & NeededCells & vbCrLf & vbCrLf _
                & "Total Opportunity:   " & OpportunityCells & " cells"), vbInformation + vbOKOnly, "Needed Range"
                
        
        iReply = MsgBox("Give this worksheet a diet and delete " & vbCrLf _
                & "Columns:   " & Range(Cells(1, LastCol + 1), Cells(TotalRow, TotalCol)).Address & vbCrLf _
                & "Rows:   " & Range(Cells(LastRow + 2, 1), Cells(TotalRow, TotalCol)).Address & vbCrLf _
                & "Total Cells saved during XML to TM1 Web conversion:   " & OpportunityCells & vbCrLf & vbCrLf _
                & "Proceed?", vbYesNo + vbQuestion, "Excel Diet")
                    If iReply = vbYes Then
                        Range(Cells(1, LastCol + 1), Cells(TotalRow, TotalCol)).Delete
                        Range(Cells(LastRow + 2, 1), Cells(TotalRow, TotalCol)).Delete '+2 to for activeforms
                    End If
    
    End If
Exit Sub

errhandle:
    MsgBox ("Either the active sheet is blank or some other error")
    MsgBox Err.Description
    Exit Sub

End Sub

TM1 Forum

Active Form with the use of VBA Macro

Active Form with the use of VBA Macro

Re: Active Form with the use of VBA Macro

Re: Active Form with the use of VBA Macro

Re: Active Form with the use of VBA Macro