Don't worry about the "death of Excel" on an RTD looping issue.  You are correct in that RTD is NOT the way to do what I'm trying do.  The tradition API in VBA is the correct methond.

Hello.

Additional information: I have also looked at the unexpected Excel termination. We might be able to fix that. However, I am still not sure that if fixed, you will get the results you expect. Specifically, since the RTD function is based on subscriptions, when any OPC item is first requested, the server may not have the data at that initial moment, And it is OK for the server to send Bad status in the initial notification. The code you currently have (the original one - the one which assigns to .Value) will get that first notification, so it's not guaranteed to be the actual value you are after.

Best regards

Hello,
thank you for more details.

For what you are doing, I tend to think that using the explicit operation calls (such as do an OPC Read when you need the data) would be more appropriate than the use of the RTD function, which is specialized a) to do subsriptions, not Read-s, and b) provide data to formulas dynamically when they change - and not when you need them. In our QuickOPC product, there are APIs just for that, and it works well.

If you want to use the RTD function nevertheless, what you described only reinforced my opinion that you should set the formulas and not the values. Try replacing the statement  that sets the value with this one:

        ActiveSheet.Range("D" & Index).Formula = "=RTD(""opclabs.office.excel.connectivityrtdserver"", """", ""opcuaattribute"", ""opc.tcp://192.168.2.201:4840/UADiscovery"", """ & topicstring1 & """, """", """")"

 

Best regards

 

I understand how this would look weird.  I'm using the RTD function in a way a little outside of its intent.  This is basically a recipe in a PLC.  The intent here is to read it out of the PLC one time and load it in spreadsheet, at which point I'll stop polling the live data.  Then the recipe values are modified in Excel and sent back to the PLC on a button press through some VBA code to move data around the spreadsheet.  I actually have a limited dataset working this way, but in production I would have to have 600 =RTD(....) statements in 600 cells.  It would just be easier if I could populate all 600 cells programatically.  Granted there are other ways so achieve my end goal.  I'm just investigating whether this method might be a semi straightforward way to do it.

In previous older applications I would use some software component to read/write the recipe array directly to the PLC.  If I can't get RTD to work, my next step is to look at going that route with some OPCUA components. My experience has been these components tend to get flakey or complicated when used in VBA (if they even work at all in an Excel VBA module).  Thus this odd workaround using the RTD function.

Suggestions?

Hello.

I think that there are actually 2 issues in your post:

1. Why the Excel closes or crashes with the code you provided.
2. Is the code you provided actually going to do what you want, if it worked?

At the moment I have doubts about #2, and it makes no sense to go deeper into #1 without having #2 clarified first.

The RTD function is special in that its result can change dynamically. That works in a formula in a cell. If you, however, evaluate that function inside the VBA code, you end up with a static value (number, typically). Your code then assigns this value to the cell. So if the code had worked, in the best case, you would end up with cells filled with static OPC values valid at the time you ran the code.

If your intent was to end up with cells that contain dynamic data, your code should set the *formulas* inside the cells (and the formulas should contain the RTD function), and not the *values* of the cells.

Let me know if I got it wrong or if you need further help.

Best regards
 

I'd like to loop through some tags in an array in a PLC and populate an Excel Sheet with that data.  It's basically a 12 x 50 array. I could type "=RTD.....whatever" in all 600 cells, but it would be way easier if I could do it programatically.  I can't seem to crack this one.  Using a code loop, I can loop it once using variables in the RTD strings indexed by the loop count.  I can NOT get it to work if I loop more than once.  I suspect it has something to do with the asynchronous communications of the RTD function.  The following VBA code works with an index of 1 (one loop).  If index is greater than 1, Excel thinks about it a bit, shuts itself down and then reopens in recovery mode.  Any ideas?

Sub RefreshRTD()
    For Index = 1 To 1  'Change this loop upper limit to 2 and Excel crashes'
        
        topicstring1 = "nsu=CODESYSSPV3/3S/IecVarAccess ;ns=4;s=|var|Turck/ARM/WinCE TV.Application.GVL.RandomNum[" & Index & "]"
               
        ActiveSheet.Range("D" & Index).Value = Application.WorksheetFunction.RTD("opclabs.office.excel.connectivityrtdserver", "", "opcuaattribute", _
        "opc.tcp://192.168.2.201:4840/UADiscovery", topicstring1, "", "", "Value;""""")
     
    Next
End Sub