TM1 Forum

Posted: **Mon Oct 11, 2010 8:55 am**

Folks

We have the scenario that when a user types into a DBRW it overwrites the formula and thus does not send data back to TM1.

I assume that the TM1P.XLA works in the following manner.

An event handler is set up on the worksheet_change event. This is my attempt at some pseudo code.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

   'Do nothing if more than one cell is changed or content deleted

    If Target.Address = in range of DBRWs or Target.OldValue begins with DBRW Then


            'Stop any possible runtime errors and halting code

            On Error Resume Next

                'Turn off ALL events 

                Application.EnableEvents = False

                Call .dll to communicate with TM1 using value = Target.Address and dimensions based on the DBRW formula contained within the Target.oldvalue. This would call an update function.
                If (return is bad) 
                    Place *KEY_ERROR into the return value for the DBRW function and Copy the formula back Target.Address = Target.OldValue
                Else 
                    Copy the formula back Target.Address = Target.OldValue and allow the formula to display the updated value.
                Endif 

                'Turn events back on

                Application.EnableEvents = True

            'Allow run time errors again

            On Error GoTo 0

        End If

    End If

      

End Sub

My excel is not good enough to determine whether the Worksheet_Change event gives you the before and after cell value (which you would expect) or whether only the range of changed cells is given and tm1p.xla has to maintain a table of DBRW formulae of its own. In either case I have referred to the
structure as Target.OldValue in the code.

As can be seen there is a great deal being done in the event handler and without access to TM1P.xla this is a black box. (It might be an idea to allow read only access to the code IBM in the future).

Clearly what has happened in the case where the DBRW is being overwritten is that the event handler is disabled not working in some way.

Any ideas as to why this might be the case would be appreciated.

Best Regards

John

Posted: **Mon Oct 11, 2010 9:17 am**

John Hammond wrote:Folks

We have the scenario that when a user types into a DBRW it overwrites the formula and thus does not send data back to TM1.

...

Clearly what has happened in the case where the DBRW is being overwritten is that the event handler is disabled not working in some way.

It doesn't really matter what the code is doing, and there's no such thing as read only access to VBA code; if you can access the module, you can access the module.

One of the most common causes of this is some other code that had an unhandled error in it which resulted in someone selecting the "Debug" option, leaving the VBA code in break mode. Going to the VBE and clicking the Reset button on the Standard toolbar will resolve that, as will restarting the Excel session after shooting whoever wrote code without error handling in it.

An even more common reason for this, unconnected with actual code execution, is users pressing the [Delete] key to "clear" the existing information before entering the new value. It's for this reason that I never create input templates which use live DBRW formulas, but rather have input areas which are sent to the cube via DBSW functions.

Posted: **Mon Oct 11, 2010 9:23 am**

Following my own logic I searched the VBA and found this which is trying to handle the protection problem in 9.5.1

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.ActiveSheet.Unprotect pWorkSheetLocking
Application.Run "TM1RECALC"
ActiveWorkbook.ActiveSheet.Protect pWorkSheetLocking
If Target.Address = Range("rngOrganisation").Address Then
ActiveWorkbook.ActiveSheet.Unprotect pWorkSheetLocking
Application.Run "TM1REFRESH"
ActiveWorkbook.ActiveSheet.Protect pWorkSheetLocking
Application.Calculation = xlCalculationAutomatic
ReprotectForecastCells
ElseIf bPerformCalc(Target) Then
    Application.Calculate
    Application.Calculation = xlCalculationAutomatic
    ReprotectForecastCells
End If
End Sub

Having removed this, the DBRW's started to work in the normal way.

Clearly this is being treated as the handler with a priority above that of the tm1p.xla.

Now I wonder does anyone know how to pass the event onto TM1 before or after the handler does its customised processing.

Regards

John

TM1 Forum

DBRW Formula being overwritten

DBRW Formula being overwritten

Re: DBRW Formula being overwritten

Re: DBRW Formula being overwritten