Professional OPC
Development Tools

logos

Cell update in Excel VBA gives runtime error 50290

More
13 Jan 2015 15:52 - 13 Jan 2015 15:53 #2605 by support
I have not tested for this situation.

Here is an attempt using a different workaround: In it, all updates are stored into a global variable, no cell updates are attempted inside the event handler. Then there is a periodic update task (runs 1 per second) that takes the accumulated values and stores them into the actual cells. Of course when done this way, the moments when the cells update do not precisely corresponds to when the updates have arrived. Also, one can think of improvements to the idea, such as:

- Enable/Disable the timer with more intelligence. Currently I start it when the workbook is activated, and stop it when the workbook is deactivated. There may be better events inside Excel to choose from. The timer also keeps running while the VBA is in design mode. Plus, one can also gate it with the Subscribe/Unsubscribe, so that the timer is not unnecessarily invoked when we are not interested in the updates.

- It may be possible to combine the two approaches - direct cell update from the event handler, with this periodic timer. For example, try to store the value into the cell directly, but if an error is generated, store the value into the dictionary instead, to be picked up later. Interestingly, Excel isn't giving me the original error right now (weird!), so I cannot develop this idea further even if I wanted to.

Here is the example project:


File Attachment:

File Name: UASubscrib...ple3.xls
File Size:63 KB



It consist of one part that is global - on ThisWorkbook level:
' Time of next update.
Private UpdateTime As Variant
 
Public Sub ScheduleUpdate()
    ' Schedule an update 1 second from now.
    UpdateTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime UpdateTime, "Sheet1.UpdateCells"
End Sub
 
Private Sub Workbook_Deactivate()
'Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Clear a previously set update, if any.
    On Error Resume Next
    Application.OnTime UpdateTime, "Sheet1.UpdateCells", , False
End Sub
 
Private Sub Workbook_Activate()
'Private Sub Workbook_Open()
    ' Schedule the first update.
    ScheduleUpdate
End Sub


And then, modified parts of the Sheet1:
' Holds cells to be updated, and the update values.
Dim CellsToUpdate As New Dictionary
Private Sub Client_MonitoredItemChanged(ByVal Sender As Variant, ByVal E As OpcLabs_EasyOpcUA.EasyUAMonitoredItemChangedEventArgs)
    ' Store the value in the intermediate dictionary. The cell name is passed to us in the State property.
    Set CellsToUpdate(E.arguments.State) = E.AttributeData.Value
End Sub
 
Public Sub UpdateCells()
    ' Update values that have changed since the last update. The cell names and values are stored in the intermediate dictionary.
    For Each Cell In CellsToUpdate.Keys
        Range(Cell).Value = CellsToUpdate(Cell)
    Next
 
    ' Clear the intermediate dictionary.
    CellsToUpdate.RemoveAll
 
    ' Schedule next update.
    ThisWorkbook.ScheduleUpdate
End Sub

Best regards
Attachments:
Last edit: 13 Jan 2015 15:53 by support.

Please Log in or Create an account to join the conversation.

More
13 Jan 2015 15:41 #2604 by support
From: C.
Sent: Monday, January 12, 2015 7:16 PM
To: Zbynek Zahradnik
Subject: RE: Questions about VBA

Zbynek,

I believe that is the case.

However [...] the OnTime function [...] is not working as it is expected. [...]

I ran the VBA code and everything would update. Then, I click into an empty cell to start editing it. I had another colleague write to one of the tags that I was subscribed to. Then, when I clicked out of the cell I was editing, I still saw the old value of the tag. I had to unsubscribe and subscribe again to see the new value she wrote.

It’s odd since we are not passing OnTime a LatestTime parameter. I know we cannot exactly know when procedure we pass to OnTime will be run, but I waited a good 20-30 seconds after I stopped editing the cell and still didn’t see the update that occurred while I was in Edit mode. I don’t think we really have control over this, since at this point it is up to Excel, but did you see a similar behavior when you were testing this?

C.

Please Log in or Create an account to join the conversation.

More
12 Jan 2015 17:54 - 12 Jan 2015 17:58 #2597 by admin
One more information to the current (up to 5.32) behavior of OPC Classic - COM event handlers: There is a special logic that stops sending event notifications if the event handler repeatedly returns an error.

It is therefore likely that in this:

On this one, after clicking in a cell to edit something, the other cells cease updating and never start updating again, even when clicking out of the cell that was being edited.


the cells cease updating permanently not directly due to the cell editing, but due to the error being thrown in the event handler. If this is the case, ignoring (suppressing) the error in the event handler, or better, using the approach I have shown with OPC UA (by basically postponing the cell update to the idle time of Excel) should resolve it.

This logic has been introduced long time ago because in case of out-of-process component, when the client process is abruptly terminated, the easyopda.exe process still tries to send notification to it; these notifications then constantly return some RPC_xxxx error, but cause great deal of CPU processing in the EasyOPC process. For this reason we try to detect it, and handle it by not sending further updates.

In the case of in-process - as it is with the current COM support for OPC UA, and the upcoming for OPC Classic - both the user app and the EasyOPC code are in the same process, and they live and die together, thus the problem cannot ever happen: if the application process dies, EasyOPC code goes with it as well.
Last edit: 12 Jan 2015 17:58 by admin.

Please Log in or Create an account to join the conversation.

More
08 Jan 2015 10:52 - 08 Jan 2015 11:10 #2588 by support
I could reproduce this. The runtime error given is 50290. Apparently it is not officially documented by Microsoft, and there can be several reasons (Google for it...). In our scenario, it seems to be caused by the fact that Excel does not allow cell updates from the code while the sheet is in the edit mode.

I should say that our concern was primarily with assuring that we can actually *get* the UA data to all these OLE Automation (COM)-based tools. That is, making sure that our methods can be called and arguments passed to them, and the event handlers are invoked and their parameters can be accessed from the user code. There are big differences between what different tools can consume, and it was hard work to find common means that work everywhere or almost everywhere. The ultimate goal of *what* then happens with the received data is more or less left to the customer - because it brings even more tool-specific complexities, as can be seen in this case.

Nevertheless, when there is a specific issue or an opportunity to give more guidance, we are happy to do so.

This said, here is at least a workaround. Replace the original event handler code:
Private Sub Client_MonitoredItemChanged(ByVal Sender As Variant, ByVal E As OpcLabs_EasyOpcUA.EasyUAMonitoredItemChangedEventArgs)
    ' Update the value in the cell. The cell name is passed to us in the State property.
    Range(E.arguments.State).Value = E.AttributeData.Value
End Sub

by this:
Private Sub Client_MonitoredItemChanged(ByVal Sender As Variant, ByVal E As OpcLabs_EasyOpcUA.EasyUAMonitoredItemChangedEventArgs)
    ' Schedule an update. The cell name is passed to us in the State property.
    ValueString = """" & Replace(Replace(E.AttributeData.Value, """", """"""), "'", "''") & """"
    Application.OnTime Now, "'Sheet1.UpdateCell """ & E.arguments.State & """, " & ValueString & "'"
End Sub
 
Private Sub UpdateCell(Cell As Variant, Value As Variant)
    ' Update the value in the cell.
    Range(Cell).Value = Value
End Sub

It shows the way to go, but it still has following issues:
  1. The cell updates stop momentarily while the sheet is in the edit mode.
  2. The cell value has to be passed to a macro in a string form. Numerical values work fine, but some special incoming strings (such as those containing quotes or double-quotes) can cause problems when not treated ("unescaped") properly. I tried to do so for quotes and double-quotes using the Replace function in the code, however there are still instances (e.g. with the non-Latin characters in the strings) where the string is not correctly recognized.

Resources used:

The resulting Excel file is attached to this post and will be included in future version of the product:

File Attachment:

File Name: UASubscrib...ple2.xls
File Size:48 KB


As to the different behavior of the COM object for "Classic" (COM-based) OPC, it is possible and not too surprising, because it is implemented in a fairly different way. The OLE Automation objects for classic OPC are under rewrite right now, therefore it is likely the behavior will align itself with OPC-UA after this effort is completed.

Best regards
Attachments:
Last edit: 08 Jan 2015 11:10 by support.

Please Log in or Create an account to join the conversation.

More
08 Jan 2015 10:39 #2587 by support
From: C.
Sent: Wednesday, January 07, 2015 4:40 PM
To: Zbynek Zahradnik
Subject: Questions about VBA

[...] I have more questions that I can’t figure out and I was hoping that you can help.

[...] working on using the new UA COM feature to work with UA in Excel.

I’ve been looking at the UASubscribeToMultiple example and I have a question on how this works. I’m not super familiar with VBA and I couldn’t find anywhere online that addresses this, so I’m sorry if this is a dumb question. After the tags are subscribed, I can see values in the appropriate cells updating. If I click in a different cell (say E2) to edit something, all the updates in the other cells stop. If I click out of E2, the updates for the other cells continue. I notice sometimes as well, while I am editing E2, a runtime error will get thrown. My assumption is that this is because Excel doesn’t allow us to edit more than one cell at a time, and the MonitoredItemChanged event handler is trying to update another cell while I am still editing E2.

If I add an On Error handler that ignores the error, everything continues fine. Doing this would lose the information that that event had, so I guess we would need to cache it somewhere to try to update the cell’s value again if possible if we don’t want to lose any data?

My question is – is this an expected feature of Excel? What exactly is going on when I do this? I’m not sure how Excel handles locking cells while they are being updated. Mark is complaining that editing one cell should not affect the others being updated, which makes sense to me from a user point of view. Is there something that I’m unaware of to make this happen? What is the suggested approach for this?

On a side note, we also found that the SubscribeToMultipleItems example for the DA COM object does not quite operate this way. On this one, after clicking in a cell to edit something, the other cells cease updating and never start updating again, even when clicking out of the cell that was being edited.

Thanks for all of your help. I greatly appreciate it.

C.

Please Log in or Create an account to join the conversation.

Moderators: support
Time to create page: 0.188 seconds

      

 Recommend this on Google