VBA TM1RECALC Problem

[Helen]

Hi there

I am making a .xla file as an addin for our users so that they can calculate just a workbook and not all open books within Excel.

I've got the code working but the calculation works intermittently and I'm not sure which is the most reliable command to use?

appication.run "TM1Recalc1" seemed to always work yesterday but today it won't but application.run "TM1Recalc" will work.
I've also tried application.calculate which was working and application.run "TM1Refresh"

Does anyone know which is the most reliable command to run the calculation (and why one command works one day and not another?).

I'm using TM1 in a Citrix environment with windows XP and office 2003 the TM1 version is 9.0.

(I've also had problems with the normal Excel functionality F9 and shift+F9 not always forcing TM1 to calc)

Helen

Please see the code that I am using below:

Code: Select all

Private Sub Calc_This_Book()
    
    answer = MsgBox(prompt:="The current active workbook will calculate.  If you have more than one workbook which needs to calculate please use shift+control+F9.  Would you like to continue?", Buttons:=36)
    
    If answer = vbYes Then
        
        currentbook = ActiveWorkbook.Name
        CurrentSheet = ActiveSheet.Name
        CurrentCellAddress = ActiveCell.Address
        
        Application.ScreenUpdating = False
    
        'disable calculation on all workbooks/sheets
        For Bookcount = 1 To Workbooks.count
            Workbooks(Bookcount).Activate
                
                For sheetcount = 1 To Sheets.count
                    Worksheets(sheetcount).EnableCalculation = False
                Next sheetcount
                   
        Next Bookcount
        
        'enable calculation on all sheets of users workbook
        Workbooks(currentbook).Activate
                
                For sheetcount = 1 To Sheets.count
                    Worksheets(sheetcount).EnableCalculation = True
                Next sheetcount
                
        'Application.Calculate
        Application.Run "TM1RECALC"
        'Application.Run "TM1RECALC1"
        'Application.Run "TM1REFRESH"
              
        
        'enable calculation on all sheets of all workbooks
        For Bookcount = 1 To Workbooks.count
            Workbooks(Bookcount).Activate
                
                For sheetcount = 1 To Sheets.count
                    Worksheets(sheetcount).EnableCalculation = True
                Next sheetcount
                   
        Next Bookcount
        
        
        Workbooks(currentbook).Activate
        Worksheets(CurrentSheet).Activate
        Range(CurrentCellAddress).Activate
        
        MsgBox ("Your workbook has completed it's calculation.")
    End If
    
        Application.ScreenUpdating = True
    
End Sub

[lotsaram]

Hi Helen,

Consider using code tags.

For what you want to do I think your code is unnecessarily complex as there is no need to loop through workbooks and worksheets.

Application.Run "TM1RECALC1" calculates the active worksheet only (same as user hitting SHIFT + F9, this is the macro that runs when Perspectives takes over the ActiveSheet.Calculate event)
Application.Run "TM1RECALC" calculates all open workbooks (same as user hitting F9, this is the macro that runs when Perspectives takes over the Application.Calculate event)
Application.Run "TM1REFRESH" rebuilds an active form on the active worksheet (same as user hitting ALT + F9)

All you should need to do is get a response from the user whether to recalculate only the current sheet or the entire application then branch to Recalc1 or Recalc accordingly. You only need Refresh if you need to rebuild an active form. (For what its worth my advice would be to train users to use manual calculation and use Shift + F9 and F9 an/or just have 3 standard buttons on reports for "Refresh Current Sheet", "Refresh All" and "Rebuild Active Form").

HTH

ADMIN NOTE: edited the 2nd example which originally said Application.Run "TM1RECALC1" but should have read Application.Run "TM1RECALC"

[Helen]

Thankyou for your help I'll use that in my code. From what you've said I need the TM1RECALC rather than TM1RECALC1 because I will always be calculating full book.

My code is not overly complex because it is essential to calculate a complete book full. It will give unreliable results calculating each sheet in turn, this will not work because any sheet that is referenced by the current sheet may not already be calculated before it. All the code does is to disable calculation for all workbooks and then enable calculation only on the current workbook then the whole application can be calculated and only the current workbook will calculate.

The purpose of the code is to calculate an active workbook and no others to prevent unnecessary useage of the system.

Many thanks for your help

Helen

[lotsaram]

What are you actually doing in your application? If you are using TM1 correctly then there should be no need to have worksheets referencing each other as each worksheet can be "independent" in an Excel sense and just reference the cube(s).

Just a thought ...

[Steve Vincent]

All the code does is to disable calculation for all workbooks and then enable calculation only on the current workbook then the whole application can be calculated and only the current workbook will calculate.

You cannot do that because it's not Excel functionality that exists.

Calculate all worksheets in all open workbooks. F9

Note When a portion of a formula is selected, calculate the selected portion. You can then press ENTER or CTRL+SHIFT+ENTER (for array formulas) to replace the selected portion with the calculated value.

Calculate the active worksheet. SHIFT+F9
Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+F9
Rechecks dependent formulas and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. CTRL+ALT+SHIFT+F9

As you cannot calculate all sheets in the active book without calculating all open books, TM1 has no event to latch on to. Your only option is to cycle thru the sheets in the active book via VBA and recalc each sheet in turn. If the report isn't set up in a way that allows the correct numbers to be calculated this way, you really should look at redesigning it so it can. The only other option is to ensure the users do not have other books open, which is usually good practice anyway.

[jydell]

Hi

I had the same issue where I did not want to recalc all open workbooks (as it was slow) The below code cycles through the open workbook only and refreshes each tab individually (same as selecting each tab individually and pressing shift-F9). Note above issues still apply if one tab refers to anothers data the sequence of refreshing will cause you problems.

Code: Select all

Sub refresh_This_book_only()

'  Run the same routine over all tabs
Dim WS As Object
For Each WS In Worksheets

WS.Activate

ActiveSheet.Select
Application.Run "TM1RECALC1"

Next WS
     
' return to and select the first sheet in the workbook
     Sheets(1).Select
     


End Sub

[Helen]

Thanks for the help.

Now my code is using TM1RECALC and not TM1RECALC1 this is working well. The code calculates only the current workbook and no other and it calculates it as a whole (like the normal F9) not a sheet at time like using the Shift+F9.

I've been testing it myself for the last couple of weeks without any problems - with the aim of rolling it out to our users. Hopefully this will help stop users calculating many books that they are not using and locking out other users. If anyone else wants to test it and let me know that would be helpful.

Many thanks
Helen