Professional OPC
Development Tools

logos

Online Forums

Technical support is provided through Support Forums below. Anybody can view them; you need to Register/Login to our site (see links in upper right corner) in order to Post questions. You do not have to be a licensed user of our product.

Please read Rules for forum posts before reporting your issue or asking a question. OPC Labs team is actively monitoring the forums, and replies as soon as possible. Various technical information can also be found in our Knowledge Base. For your convenience, we have also assembled a Frequently Asked Questions page.

Do not use the Contact page for technical issues.

Use VBA in Excel to access OPC UA data

More
06 Mar 2018 16:08 #6108 by support
Ad 1. This stretches my VBA knowledge again. Here is what I can say from our side: The EasyUAClient COM object stays subscribed until you explicitly unsubscribe, OR until it is deleted which happens when its reference count decrements to zero.

I believe that when you declare a VBA variable holding the EasyUAClient in the way in our/your code samples (and do not create additional references to it by assigning it to other variables), the reference count will go back to zero when you assign a different object or Nothing to the variable, or when the module is unloaded - which would be when the workbook is closed. Closing the workbook should therefore unsubscribe as well.

When the workbook opens again, EasyUAClient will not re-subscribe by itself (it's a new object that behaves like the first one). But your "workbook open" routine will run again and *that* will re-subscribe.

Ad 2. This question should be addressed to the OPC server vendor primarily. What I write further below are just guesses, though based on typical behavior.

*Usually*, the activation/lifetime of the OPC UA server process is not tied to the client connections/disconnections. It runs as a service (or similar concept) in the device or on a PC, and capable of communicating with the target device independently of whether an OPC client is. Also, most (but not all) devices, even if sending out data on change bases, allow the read of current value be triggered at any time, and therefore the OPC UA server should be ale to obtain the current even when it has just started and no data change has come in yet.

Regards

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

More
06 Mar 2018 15:03 #6107 by Monks
Thanks for all the thorough information. Your responses have been very timely and helpful.

In regards to placing the Multiple arguments code in the Workbook Open subroutine:

1) When I close the Excel workbook, will it automatically unsubscribe and re-subscribe again when the workbook opens? Or will it stay subscribed, whether I'm running the code with the file open, or I have the file closed? I ask this because Excel needs to be periodically closed and reopened when running it 24/7. Would I need to unsubscribe before closing the file and then re-subscribe when it opens?

2) How long does OPC UA server hold the latest information? Currently our welder isn't running. If we try to test this code now, would we still be able to read information from the last values recorded, when the welder ran last week? Or would we need the welder to be sending data, real time, before we would see any information fon the server?

Thanks

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

More
06 Mar 2018 09:18 #6106 by support
Here is more to question 4 (taken from the not-yet-released documentation - it will be hyperlinked there):

All callbacks (including event notifications) originating from EasyUAClient object are (by default) sent in a queued manner by a dedicated internal thread, improving resiliency against errors in custom code, and decreasing the possibility of deadlocks. When there is a sudden peak in the rate of incoming events, they can accummulate temporarily in the queue, allowing the processing to proceed smoothly.

This callback queuing can be turned on or off by the QueueCallbacks property. Turning off the callback queuing is only advisable in very special circumstances. When the callback queuing is turned off, the callback and event handling is subject to much stricter rules with regard to performance, but also with regard to what the handlers are allowed to do. Most importantly, the handlers must not call any methods on the originating component, because doing so can cause deadlocks. This rule also applies to calls that may be inflicted by the SynchronizationContext on the component. For example, the common synchronization contexts based on Windows messages (for Windows Forms and WPF) can call other "posted" methods before the action that are instructed to do, causing unpredictable behavior and a possible deadlock too.

For the reasons described above, do not turn the callback queuing off unless you know very well what you are doing.

The capacity of the callback queue is given by the CallbackQueueCapacity property. The memory is not preallocated for the full maximum size, however, and therefore under normal circumstances, there is no penalty for using quite high queue capacities. The callback queue capacity should be large enough to be able to hold all incoming callbacks in case of a sudden "burst", before they are all processed (consumed) by your code.

For errors coming from subscriptions, QuickOPC uses the same "channel" as for success notifications - i.e. the DataChangeNotification Event or EventNotification Event, and it treats each monitored item separately. This means that in case of an error that affects the whole connection to a particular server (or more servers), callbacks (and/or events) will be generated for every monitored item affected. This is by design. If you are subscribed to larger number of monitored items, this itself can create a "burst" that is way above the normal rate of events. Your code, and the size of the callback queue, need to be prepared for this scenario as well.

If the amount of notifications that needs to be held in the queue exceeds its capacity, a callback queue overflow occurs, and these notifications are lost. In addition, a LogEntry Event is generated on the EasyUAClient, with corresponding message. If a notification is removed from a queue while it has overflown, the overflow status is cleared, and a different LogEntry Event is generated on the EasyUAClient (overflow clearing). The event contains overflow count, which is effectively the number of notifications that have been lost due to the queue overflow.

The idle time before the internal thread executing the queued callbacks is stopped is controlled by the CallbackQueueIdleTimeToSleep property. The thread is stopped as an optimization measure, to reduce the amount of system resources consumed. When new callbacks come, the thread is automatically started.

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

More
06 Mar 2018 09:04 #6105 by support
Hello,

ad 1) Yes.

ad 2) I do not have enough VBA expertise to answer that. In general, I think it does not matter as long as VBA does not complain (will have to do with visibility of the scope for the use of the "WithEvents" clause).

ad 3) You can store any kind of value in the State property, and then use it in the event handler. In our example, we used strings which were cell names. And we have assigned literal strings to the State, therefore they were in quotes (such as "A2"). In your code, one of the values had the quotes around it ("FIXNUM"), so it seemed to me that the others were also meant to be quoted strings. If that was not your intent, they can be variable names. BUT that would not make them into variable REFERENCES: The current value of the variable at the time of assignment to State will be used. To my knowledge, you cannot have pass variable references around in VBA, so if your intent was then to use the State in the event handler to simply transfer the incoming value into a variable previously set to State, it would not work.

ad 4). Yes, in general. We will have a new chapter on details of this in the documentation for the upcoming version. I will try to locate that and make available here, in a subsequent post. It's a bit difficult as I am out of office this week.

Best regards

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

More
05 Mar 2018 15:03 #6102 by Monks
OK, Let's see if we can finalize how this code will run.

1) Can I place my QUICKOPC subroutine below in the (workbook open) subroutine so it subscribes to multiple arguments when the workbook opens?
2) Would the event handler subroutine be placed in a module or class module?
3) Usually Numeric variables do not require quotation marks in VBA. Does the "MonitoredItemArguments2.State =" statement still require quotes even if we are referencing variables instead of spreadsheet cells?
4) I assume, once initialized, the Event handler (Private Sub Client_DataChangeNotification(ByVal Sender As Variant, ByVal E As OpcLabs_EasyOpcUA.EasyUADataChangeNotificationEventArgs)) will run my SPC code every time the variables change? The SPC code takes ~1.5 to 2 seconds to run. The welder data updates every 9 seconds, so we should be OK, correct?

Thanks

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

More
02 Mar 2018 07:12 #6096 by support
For the envisioned QuickOPC usage, that is for VBA in Excel, QuickOPC parts need to be installed on the computer where the Excel runs, only.

I have looked at your code and I do not see any obvious problem with it (except, may be, for missing quotes around some of the State values, such as CSTATUS).

And, of course, the event handler for data changes is missing. There, as you wrote, it is really up to you what you do with the incoming data. We have demoed updating spreadsheet cells, but your code can simply store the data into variables, make computations with them etc. - anything VBA can do.

You only need to call the SubscribeXXXX once. It indicates an intent to remain subscribed until explicitly unsubscribed (by a call to UnsubscribeXXXX). See also: opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...%20UA%20Monitored%20Items.html

Even if the connection to the server is lost, it will attempt to reconnect - so you do not need to code in the recovery logic yourself. See also: opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...0OPC%20failure%20recovery.html

Note that as opposed to making a Read call with multiple nodes at once, with subscribes, all data changes come in individually, and therefore the logic to process the values needs to be different. You will only receive the notifications for values that have changed, and they will come one by one, sequentially.

Best regards

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

More
01 Mar 2018 21:21 #6093 by Monks
Do I need to install Quick OPC on the remote computer that is tracking the data or on the controller computer that houses the server? Or both?
I thought I might need the Subscribe VBA code Quick OPC provides, which is why I was asking the earlier ("fill in the blanks") code questions. Instead of updating 4 cells in Excel, I was wondering if I could update variables in the code itself? My proposed code base on your earlier feedback is as follows: Would this work? Would running this subroutine once keep updating the variables or would the subroutine need re-run several times?

Private Sub QUICKOPC()

Dim CSTATUS As Single
Dim ACTCOLLPSE As Single
Dim FIXNUM As Single
Dim DNE As Single

' Create the EasyOPC-UA component
Set Client = New EasyUAClient

Dim MonitoringParameters As New UAMonitoringParameters
MonitoringParameters.SamplingInterval = 1000

' Filter numerical data changes using an absolute deadband of 5.0
Dim DataChangeFilter As New UADataChangeFilter
DataChangeFilter.DeadbandType = UADeadbandType_Absolute
DataChangeFilter.DeadbandValue = 5#
Set MonitoringParameters.DataChangeFilter = DataChangeFilter

' Define OPC node IDs
' For "states", we use names of cells where the values should be updated

Dim MonitoredItemArguments1: Set MonitoredItemArguments1 = CreateObject("OpcLabs.EasyOpc.UA.OperationModel.EasyUAMonitoredItemArguments")
MonitoredItemArguments1.EndpointDescriptor.UrlString = " opc.tcp://192.168.10.19:4841"
MonitoredItemArguments1.NodeDescriptor.NodeId.ExpandedText = "ns=6;s=::AsGlobalPV:gPLC.Status.CollapseStatus"
Set MonitoredItemArguments1.MonitoringParameters = MonitoringParameters
MonitoredItemArguments1.State = CSTATUS

Dim MonitoredItemArguments2: Set MonitoredItemArguments2 = CreateObject("OpcLabs.EasyOpc.UA.OperationModel.EasyUAMonitoredItemArguments")
MonitoredItemArguments2.EndpointDescriptor.UrlString = " opc.tcp://192.168.10.19:4841"
MonitoredItemArguments2.NodeDescriptor.NodeId.ExpandedText = "ns=6;s=::AsGlobalPV:gPLC.Status.ActualCollapse"
Set MonitoredItemArguments2.MonitoringParameters = MonitoringParameters
MonitoredItemArguments2.State = ACTCOLLPSE

Dim MonitoredItemArguments3: Set MonitoredItemArguments3 = CreateObject("OpcLabs.EasyOpc.UA.OperationModel.EasyUAMonitoredItemArguments")
MonitoredItemArguments3.EndpointDescriptor.UrlString = " opc.tcp://192.168.10.19:4841"
MonitoredItemArguments3.NodeDescriptor.NodeId.ExpandedText = "ns=6;s=::AsGlobalPV:gPLC.JoinSta.FixtureNumber"
Set MonitoredItemArguments3.MonitoringParameters = MonitoringParameters
MonitoredItemArguments3.State = "FIXNUM"

Dim MonitoredItemArguments4: Set MonitoredItemArguments3 = CreateObject("OpcLabs.EasyOpc.UA.OperationModel.EasyUAMonitoredItemArguments")
MonitoredItemArguments4.EndpointDescriptor.UrlString = " opc.tcp://192.168.10.19:4841"
MonitoredItemArguments4.NodeDescriptor.NodeId.ExpandedText = "ns=6;s=::AsGlobalPV:gPLC.JoinSta.Done"
Set MonitoredItemArguments4.MonitoringParameters = MonitoringParameters
MonitoredItemArguments4.State = DNE

Dim arguments(3)
Set arguments(0) = MonitoredItemArguments1
Set arguments(1) = MonitoredItemArguments2
Set arguments(2) = MonitoredItemArguments3

' Subscribe to OPC monitored items
Call Client.SubscribeMultipleMonitoredItems(arguments)
End Sub

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

More
01 Mar 2018 19:37 #6092 by support
Thanks for clarification.

If you use QuickOPC for getting the OPC data into VBA/Excel, the computer where the Excel runs needs to have QuickOPC installed according to opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...ml#Application_Deployment.html . This involves copying the assemblies, registering them to COM, and installing the license. It can be done manually, integrated into your installer, or simply by running the installer that we provide.

If secure OPC UA communication will be used, tasks related to UA app certificates will be needed. That is a larger subject, for the purpose of QuickOPC partially covered here: opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...me.html#OPC-UA%20Security.html . If the network itself is secure, you may be OK using unsecured UA communication in which case this part would not apply.

For getting values continuously, it is possible to use periodically repeated Read-s, but it generally discouraged (for performance reasons). Instead, it is recommended to use Subscriptions. Examples for that, including VBA/Excel, also come with the product (list of the examples: opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%2...A%20Examples%20in%20Excel.html ). The code that subscribes to several nodes, in the basic example, is below. The event handler simply updates a cell with a new value, but it do anything it wants with the incoming data.
' Declare an EasyOPC-UA component
Public WithEvents Client As EasyUAClient
 
Private Sub SubscribeCommandButton_Click()
    ' Create the EasyOPC-UA component
    Set Client = New EasyUAClient
 
    Dim MonitoringParameters As New UAMonitoringParameters
    MonitoringParameters.SamplingInterval = 1000
 
    ' Define OPC node IDs
    ' For "states", we use names of cells where the values should be updated
 
    Dim MonitoredItemArguments1 As New EasyUAMonitoredItemArguments
    MonitoredItemArguments1.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    MonitoredItemArguments1.NodeDescriptor.NodeId.ExpandedText = "nsu=http://test.org/UA/Data/;i=10845"
    Set MonitoredItemArguments1.MonitoringParameters = MonitoringParameters
    MonitoredItemArguments1.SetState ("D2")
 
    Dim MonitoredItemArguments2 As New EasyUAMonitoredItemArguments
    MonitoredItemArguments2.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    MonitoredItemArguments2.NodeDescriptor.NodeId.ExpandedText = "nsu=http://test.org/UA/Data/;i=10853"
    Set MonitoredItemArguments2.MonitoringParameters = MonitoringParameters
    MonitoredItemArguments2.SetState ("D3")
 
    Dim MonitoredItemArguments3 As New EasyUAMonitoredItemArguments
    MonitoredItemArguments3.EndpointDescriptor.UrlString = "http://opcua.demo-this.com:51211/UA/SampleServer"
    MonitoredItemArguments3.NodeDescriptor.NodeId.ExpandedText = "nsu=http://test.org/UA/Data/;i=10855"
    Set MonitoredItemArguments3.MonitoringParameters = MonitoringParameters
    MonitoredItemArguments3.SetState ("D4")
 
    Dim arguments(2)
    Set arguments(0) = MonitoredItemArguments1
    Set arguments(1) = MonitoredItemArguments2
    Set arguments(2) = MonitoredItemArguments3
 
    ' Subscribe to OPC monitored items
    Call Client.SubscribeMultipleMonitoredItems(arguments)
End Sub
 
Private Sub UnsubscribeCommandButton_Click()
    ' Unsubscribe from all OPC monitored items
    Call Client.UnsubscribeAllMonitoredItems
End Sub
 
Private Sub Client_DataChangeNotification(ByVal Sender As Variant, ByVal E As OpcLabs_EasyOpcUA.EasyUADataChangeNotificationEventArgs)
    ' Update the value in the cell. The cell name is passed to us in the State property.
    If E.Exception Is Nothing Then valueString = E.attributeData.Value Else valueString = E.ErrorMessageBrief
    Range(E.arguments.State).Value = valueString
End Sub
 
 

Best regards

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

More
01 Mar 2018 19:07 #6091 by Monks
I should have better clarified what we are trying to do. I'm trying to figure out what code I need from QuickOPC that will accomplish real time data collection every 9 seconds from a remote computer station. We currently have custom VBA Code that collects real time data from RS232 and tracks with SPC graphs in Excel. Therefore, I'd like to read the collapse data from the OPC UA Server, save the data collected in a VBA code variable, use this variable information to update the relevant graph for each nest ID, then wait for new information to be sent.
How do I continuously get the OPC collapse data into a code variable?
What/if anything, needs to be configured in the computer that will be used to track the data from the welder server?
This is all new to me, which is why I'm confusing you, sorry. I always work with RS232 communication which is very easy for collecting information real time. If OPC UA server data can't interface as easily we may not be able to adapt this medium to our system. I hope we can.
Thanks
Chris

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

More
01 Mar 2018 13:58 #6090 by support
I am somewhat confused, on several accounts.

1. You appear to have switched to the EasyDAClient component. That is for OPC Data Access - different from OPC UA we have discussed so far. Why?

2. With OPC-DA, the second argument ("opc.tcp://192.168.10.19:4841") will have to look differently, it will have to be the ProgID of the OPC Server, and I do not know that. In addition, the first argument, which is now an empty string, denotes the computer - right now it means the computer where the client is. For remote computer, you will have to configure DCOM.

3. The third argument will be the Item ID. Without further info, I cannot tell whether "CollapseStatus" is right or wrong.

4. You mention "to monitor..." the items, but that's not what Read does. You should use Subscribe if you want to continuously monitor the values.

5. You wrote "multiple argument code example, I listed previously?" but I think we had not discussed or listed that yet.

Yes, making multiple operations at once is always strongly recommended over individual calls in OPC (be it OPC "Classic" or OPC UA). This has to do with round-trip delays associated with each call to the server or the client: One call, with more data, is a way faster than multiple calls with small chunks of data.

Regards

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

Moderators: support
Time to create page: 0.072 seconds