Application.Run("DBRW"...) RECALC_0_3 in Worksheet_Change

Post Reply
bkkbasher
Posts: 19
Joined: Mon Aug 26, 2013 8:39 am
OLAP Product: PAx & PAW
Version: PA 2.0.8
Excel Version: Excel 2019

Application.Run("DBRW"...) RECALC_0_3 in Worksheet_Change

Post by bkkbasher » Fri Sep 06, 2019 7:56 am

If I call the below Sub BuildModel directly then the Application.Run("DBRW") returns the value perfectly. However, if it gets called from the Worksheet_Change event I get a RECALC_0_3 error.

The Brand is getting passed fine; I've tried hard coding it and its the same result.

I've tried Application.Evaluate(DBRWString) and get the same result.

Strangely, I have a Application.Run("DBSS") on another sheet within a Worksheet_Change and it works perfectly. That doesn't require a return value though.

I've seen older posts in the forum on issues within the Worksheet_Change event but I think many of those have been addressed now.

I've got the latest PAx and Excel 365 (2019).

Anyone have any ideas for something that might kick it into action?

__________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Sheets("Create Model").Range("E18")) Is Nothing Then
Call BuildModel
End If

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub BuildModel()

Dim Brand As String
Dim BrandCode As String

Brand = Sheets("Create Model").Range("E18").Value
BrandCode = Application.Run("DBRW", "CXMD:}ElementAttributes_Model", Brand, "Brand Code")
MsgBox (BrandCode)

End Sub

User avatar
Steve Rowe
Site Admin
Posts: 1941
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Application.Run("DBRW"...) RECALC_0_3 in Worksheet_Change

Post by Steve Rowe » Fri Sep 06, 2019 8:52 am

Perhaps try the DBR formula instead?

bkkbasher
Posts: 19
Joined: Mon Aug 26, 2013 8:39 am
OLAP Product: PAx & PAW
Version: PA 2.0.8
Excel Version: Excel 2019

Re: Application.Run("DBRW"...) RECALC_0_3 in Worksheet_Change

Post by bkkbasher » Fri Sep 06, 2019 9:27 am

Apologies, should have said, i tried DBR and it was the same result. Tried DBRA as well and also the same. However, seem to have stumbled across a workaround.

I noticed if i wrote anything to any cell on the sheet within the VBA prior to doing the Application.Run then it worked. Then found out a basic cell calculate also activates the connection to TM1, even if it is not TM1 related. While it is far from elegant, i have now added a Range("A1").Calculate (which is just an empty cell) within the Worksheet_Change and it works fine.

Not sure if recalculating null or writing null is more lightweight. Neither is ideal, but at least it seems to work now.


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Sheets("Create Model").Range("E18")) Is Nothing Then
Sheets("Create Model").Range("A1").Calculate
Call BuildModel
End If

End Sub


Sub BuildModel()

Dim Brand As String
Dim BrandCode As String

Brand = Sheets("Create Model").Range("E18").Value
BrandCode = Application.Run("DBRW", "CXMD:}ElementAttributes_Model", Brand, "Model Code")
MsgBox (BrandCode)



End Sub

User avatar
Steve Rowe
Site Admin
Posts: 1941
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Application.Run("DBRW"...) RECALC_0_3 in Worksheet_Change

Post by Steve Rowe » Fri Sep 06, 2019 10:20 am

Is it possible that your "confusing" Excel and the TM1 client?

Your worksheet change macro does not include disabling of events and so could be getting triggered multiple times.

bkkbasher
Posts: 19
Joined: Mon Aug 26, 2013 8:39 am
OLAP Product: PAx & PAW
Version: PA 2.0.8
Excel Version: Excel 2019

Re: Application.Run("DBRW"...) RECALC_0_3 in Worksheet_Change

Post by bkkbasher » Fri Sep 06, 2019 11:06 am

I did try disabling events, which I think I saw in an earlier post on the forum on a related issue, but it didn't help either.

I'll continue to play with it and see if I can get a better solution though.

Post Reply