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

[bkkbasher]

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

[Steve Rowe]

Perhaps try the DBR formula instead?

[bkkbasher]

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

[Steve Rowe]

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]

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.