TM1 Forum

Posted: **Thu Feb 21, 2013 4:42 pm**

Hi,

My first post here and I'm pretty new to tm1 (6 months). I have a question regarding looping through activeforms with vba and rebuilding them. I'm having trouble getting the activeform to complete the rebuild prior to going to the next sheet resulting in activeforms that have not been rebuilt.

Below is my code which calcs an activeform and determines a unique list of customers, then goes into bulk reporting mode and creates many activeforms (by coping a template) for each of the customers. I've marked out the area of the code where the activeform rebuild should take place, but sometimes the code moves on to the next sheet without completing the activeform rebuild.

Code: Select all

Sub FindCustomers()

Dim myRange         As Range
Dim intCust         As Long
Dim iReply          As Integer
Dim myCell          As Range
Dim Customers       As Range

Dim LastRow         As Long
Dim TotalRow        As Long
Dim TotalCol        As Long
Dim shtName         As String

Application.EnableEvents = False

With Sheet3
    .Activate
    .Application.Run "TM1Refresh"
    Set myRange = Sheet3.[TM1RPTDATARNG3]
    myRange.RemoveDuplicates Columns:=2, Header:=xlNo
    LastRow = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
    TotalRow = .Rows.Count
    TotalCol = .Columns.Count
    .Range(.Cells(LastRow + 1, 1), .Cells(TotalRow, TotalCol)).Delete
    intCust = myRange.Rows.Count
End With

With Sheet2
    .Activate
    .Range("A13", .Range("A13").End(xlDown)).Clear
    Set Customers = .Range("A13").Resize(intCust, 1)
    Customers = myRange.Columns(2).Value
End With


iReply = MsgBox("Would you like to run the report for each of these customers?" _
        & vbCrLf _
        & vbCrLf & "Warning, this could take a significant amount of time depending on how many customers and how many rebates!", vbYesNo, "Bulk Reporting Option")
        
        'begin bulk reporting
        If iReply = vbYes Then
            Sheet1.Range("C13:C16") = Sheet2.Range("B1:B4").Value
 
            For Each myCell In Customers.Cells
                Sheets("Report").Copy After:=Worksheets(Worksheets.Count)
                With ActiveSheet
                    .[C17] = myCell.Value
                    shtName = Run("DBRW", [server] & ":}ElementAttributes_Customer_Plan", myCell.Value, "RelatedCustomer")
                        If shtName = "Total Plan Customers" Then shtName = "All Other Customers"
                        If Len(shtName) > 30 Then shtName = Left(shtName, 30)
                    .Name = shtName
'******************************************************************************************************************************************
'****************** Issue here!  I've tried various calc methods and timers, but to no avail ************************************
'******************************************************************************************************************************************
                    .Application.Run "TM1RebuildCurrentSheet"
                    '.Application.Wait Now + TimeValue("00:00:01")
                    '.Application.Run "TM1Refresh"
                    'Call Application.Run("DoTM1Refresh", True)
                    'Call Application.Run("DoTM1Refresh", False)
                    LastRow = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
                    TotalRow = .Rows.Count
                    TotalCol = .Columns.Count
                    .Range(.Cells(LastRow + 1, 1), .Cells(TotalRow, TotalCol)).Delete
                End With
            Next
        End If


Sheets("Report").[C13].Formula = "=SUBNM(server&"":Company"","""", ""1100"",""Description"")"
Sheets("Report").[C14].Formula = "=SUBNM(Server&"":Business_Segment"",""Default"",""A4"",""Description"")"
Sheets("Report").[C15].Formula = "=SUBNM(Server&"":Version"","""",""Current Forecast"")"
Sheets("Report").[C16].Formula = "=SUBNM(Server&"":Period"","""",""FY""&DBRA(Server&"":VERSION"",C15,""Year 1""))"
Sheets("Report").[C17].Formula = "=SUBNM(Server&"":Customer_Plan"","""",""Some Customer Name"",""Description"")"

Application.EnableEvents = False

End Sub

I've not found a complete list of VBA calc commands and was wondering if my assumptions below are correct:

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"
Application.Run "TM1RECALC"                          'workbook calculate "F9"
Application.Run "TM1RECALC1"                        'worksheet calculate "Shift & F9"
Application.CalculateFullRebuild                        'rebuild workbook "Ctrl & Alt & F9"

And finally does anyone else use this method to work in autocalc mode?

Code: Select all

Sheet1.EnableCalculation = True
Application.Run "TM1Refresh"
Sheet1.EnableCalculation = False

Thanks for the help and I look forward to sharing some of my other vba solutions with the group.
-Kevin

Tm1 Version: 9.5.2
Excel Version: 14 x86

TM1 Forum

Activeform rebuild vba loop not calculating

Activeform rebuild vba loop not calculating