OPC Client-EXCEL/VBA sample

It accesses the OPC server via the automation wrapper of OPC.

Declare OPC Objects (OPC Server / OPC Group / OPC Item)

For hooking events of OPC Server, add to "WithEvents" in dimension statement or event handler named "OBJECTNAME_EVENTNAME".
Dim WithEvents Myserver As OPCServer Dim WithEvents Mygroup As OPCGroup Dim WithEvents Mygroups As OPCGroups Dim Myitem As OPCItem Dim Myitems As OPCItems

Connect and Browse

It is able to get device name registered in OPC Server by using OPC Server object. You can make the Group under device and OPC Server object is enabled to access this lower layer. Set OPCMyserver = New OPCServer OPCMyserver.Connect ServerName, ""

Create OPC Group Object and Add Tags

It is necessary to create the group object in the OPC server to acquire real data from the outside.The data update cycle can be set to the group object, and usually register the device accessed by the same synchronization to the group. Because the threads of each group are created in the OPC server, please note CPU load increase by subdivision too much.
Set OPCMygroups = OPCMyserver.OPCGroups Set OPCMygroup = OPCMygroups.Add("Group1") OPCMygroup.UpdateRate = 1000 Set OPCMyitems = OPCMygroup.OPCItems For i = 1 To ItemCount ClientHandles(1) = i OPCItemIDs(1) = Devicename + "." + Devicecode + Format$(Val(Deviceno) + i - 1) OPCMyitems.AddItems 1, OPCItemIDs, ClientHandles, ItemServerHandles, Errors ''', RequestedDataTypes, AccessPaths Next i

Advise and Callback

Implements event handler of OPC Group object for advice mode. The OPC Server read data for each cycle and if changing the value, callback to client application. The quality flag is normally '0xC0', when occurred error, it will be turn to other value.
OPCMygroup.IsActive = True OPCMygroup.IsSubscribed = True Private Sub OPCMygroup_DataChange(ByVal TransactionID As Long, ByVal NumItems As Long, ClientHandles() As Long, ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date) Dim i As Integer For i = 1 To NumItems Cells(ClientHandles(i), 2) = ItemValues(i) Cells(ClientHandles(i), 3) = TimeStamps(i) Cells(ClientHandles(i), 4) = Qualities(i) Next i End Sub

Sync Read/Write and ASync Read/Write

The OPC has two ways of data access, Synchronize and Asynchronous. In Synchronize, the client application must be wait for completing server work. But the client application can confirm server transaction is normally completed because the OPC Server returns the result on completing the communication work. This is a normally style of procedure.
In Asynchronous mode, the OPC Server turns the handle to client as soon as receiving the request and proceed the transaction on background. After completing this work, calls handler of client application. Asynchronous mode cut down wait time for communication and be able to run client application.
Caution: Asynchronous access is enabled under advice mode.
'Cache Read Dim anItem As OPCItem For Each anItem In OPCMygroup.OPCItems anItem.Read OPCCache 'OPCDevice Cells(anItem.ClientHandle, 2) = anItem.Value Cells(anItem.ClientHandle, 3) = anItem.TimeStamp Cells(anItem.ClientHandle, 4) = anItem.Quality Set anItem = Nothing Next anItem 'Syncronous Write For Each anItem In OPCMygroup.OPCItems anItem.Write Cells(anItem.ClientHandle, 2) Set anItem = Nothing Next anItem

Home Back Top

---