Excel and VBA as OPC client

[LuckyPete]

Hi all, been reading and did not find the answer so here is my question.

I am writing a small OPC client in Excel and while I use the VBA code within Excel I have hit a wall and need help.

Task:

I need to display "All the time" the values of some 52 consecutive data registers from a PLC in the range of C2 to C53. My OPC is working fine and I can see the values in it change and being updated OK.

Problem:

I am using a function..."Private Sub MyOPCGroup_DataChange" and when any of the 52 datas change I get there new values BUT these values are not places at there corresponding positions. They are placed in there order of apearrance as new items.

So value 1 corresponding to register 1 is at the top the the cell range when I establish the reading of the OPC Server. And so on for the 52 values.

When a value changes it is place a the top also. How can I get the newer values to be placed in there rightfull place i.e. cells?

'''backup b4 ****up
Option Explicit
Option Base 1
 
Const ServerName = "LGIS.LGEOPC" 'Déclare le serveur une constante inchangeable
Dim WithEvents MyOPCServer As OpcServer 'Déclare MyOPCServer comme variable qui répond à un objet
Dim WithEvents MyOPCGroup As OPCGroup  'Déclare MyOPCGroup comme variable qui répond à un objet
Dim MyOPCGroupColl As OPCGroups
Dim MyOPCItemColl As OPCItems
Dim MyOPCItems As OPCItems
Dim MyOPCItem As OPCItem
 
 
Dim ClientHandles(52) As Long
Dim ServerHandles() As Long
Dim Values(52) As Variant
Dim Errors() As Long
Dim ItemIDs(52) As String
Dim GroupName As String
Dim NodeName As String
 
'Dim i As Integer
 
 
Dim OpcValues(52) As Variant
Dim OldValue As Variant
Sub Automat1()
If Range("C2").Value = 101 Then
    Feuils("sheet1").CommandButton1.Value = False
    Feuils("sheet1").CommandButton2.Value = True
    Else
    End If
End Sub
 
Private Sub CommandButton1_Click()
Sheet1.StartClient
'Range("B2").Value = 2
 
End Sub
 
Sub StartClient()
   
   On Error GoTo errorhandler
  
    ItemIDs(1) = Range("A2").Value
    ItemIDs(2) = Range("A3").Value
    ItemIDs(3) = Range("A4").Value
    ItemIDs(4) = Range("A5").Value
    ItemIDs(5) = Range("A6").Value
    ItemIDs(6) = Range("A7").Value
    ItemIDs(7) = Range("A8").Value
    ItemIDs(8) = Range("A9").Value
    ItemIDs(9) = Range("A10").Value
    ItemIDs(10) = Range("A11").Value
    ItemIDs(11) = Range("A12").Value
    ItemIDs(12) = Range("A13").Value
    ItemIDs(13) = Range("A14").Value
    ItemIDs(14) = Range("A15").Value
    ItemIDs(15) = Range("A16").Value
    ItemIDs(16) = Range("A17").Value
    ItemIDs(17) = Range("A18").Value
    ItemIDs(18) = Range("A19").Value
    ItemIDs(19) = Range("A20").Value
    ItemIDs(20) = Range("A21").Value
    ItemIDs(21) = Range("A22").Value
    ItemIDs(22) = Range("A23").Value
    ItemIDs(23) = Range("A24").Value
    ItemIDs(24) = Range("A25").Value
    ItemIDs(25) = Range("A26").Value
    ItemIDs(26) = Range("A27").Value
    ItemIDs(27) = Range("A28").Value
    ItemIDs(28) = Range("A29").Value
    ItemIDs(29) = Range("A30").Value
    ItemIDs(30) = Range("A31").Value
    ItemIDs(31) = Range("A32").Value
    ItemIDs(32) = Range("A33").Value
    ItemIDs(33) = Range("A34").Value
    ItemIDs(34) = Range("A35").Value
    ItemIDs(35) = Range("A36").Value
    ItemIDs(36) = Range("A37").Value
    ItemIDs(37) = Range("A38").Value
    ItemIDs(38) = Range("A39").Value
    ItemIDs(39) = Range("A40").Value
    ItemIDs(40) = Range("A41").Value
    ItemIDs(41) = Range("A42").Value
    ItemIDs(42) = Range("A43").Value
    ItemIDs(43) = Range("A44").Value
    ItemIDs(44) = Range("A45").Value
    ItemIDs(45) = Range("A46").Value
    ItemIDs(46) = Range("A47").Value
    ItemIDs(47) = Range("A48").Value
    ItemIDs(48) = Range("A49").Value
    ItemIDs(49) = Range("A50").Value
    ItemIDs(50) = Range("A51").Value
    ItemIDs(51) = Range("A52").Value
    ItemIDs(52) = Range("A53").Value
   
  'After declaring the objects and variables
 'required for the OPCClientModule module,
 'the connection process begins with creation
 'of a new OPC Server Object
  Set MyOPCServer = New OpcServer
 '------------------------------
 'Because we want to conncect to a specific
 'OPC Server, the server must be defined.
  MyOPCServer.Connect ServerName, NodeName
 '------------------------------
 'Each OPC Server has a specific interface for
 'its Collection of Groups. To get the interface use:
  Set MyOPCGroupColl = MyOPCServer.OPCGroups
 '------------------------------
  MyOPCGroupColl.DefaultGroupIsActive = True
 '------------------------------
 'Now we add a Group to our Group Collection.
 'You can use any Group name you want.
  GroupName = "MyGroup"
  Set MyOPCGroup = MyOPCGroupColl.Add(GroupName)
  Set MyOPCItemColl = MyOPCGroup.OPCItems
 
  MyOPCItemColl.AddItems 52, ItemIDs, ClientHandles, ServerHandles, Errors
  MyOPCGroup.IsSubscribed = True
    
 
  Exit Sub
errorhandler:
  MsgBox "Error: " & Err.Description, vbCritical, "ERROR"
End Sub
 
Private Sub CommandButton2_Click()
On Error Resume Next
Sheet1.StopClient
Range("B2").Value = 0  'xxxxxxxxxxxxxxxx
End Sub
 
Sub StopClient()
 
  MyOPCGroupColl.RemoveAll
 
  MyOPCServer.Disconnect
  Set MyOPCItemColl = Nothing
  Set MyOPCGroup = Nothing
  Set MyOPCGroupColl = Nothing
  Set MyOPCServer = Nothing
 
 
End Sub
 
Private Sub CommandButton3_Click()
 
'Saving the file on clicking a button with names and dates for archives
 
Dim filename As String
Dim filepath As String
Dim strdate As String
Dim fileID As String
 
strdate = Format(Date, "dd-mm-yyyy")
filename = ("")
filepath = "c:\AAA\"
fileID = Range("Sheet1!C2").Value
 
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.SaveCopyAs filename:=filepath + filename + "Station no." + fileID + " " + strdate + ".xls"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
End Sub
Private Sub cmdSyncRead_Click()
On Error Resume Next
 
 
End Sub
 
'THIS IS WHERE I HAVE PROBLEMS ... I THINK!
 
Private Sub MyOPCGroup_DataChange(ByVal TransactionID As Long, ByVal NumItems As Long, ClientHandles() As Long, ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date)
    
   On Error GoTo errorhandler2
 
Range("C2").Value = ItemValues(1)
Range("C3").Value = ItemValues(2)
Range("C4").Value = ItemValues(3)
Range("C5").Value = ItemValues(4)
Range("C6").Value = ItemValues(5)
Range("C7").Value = ItemValues(6)
Range("C8").Value = ItemValues(7)
Range("C9").Value = ItemValues(8)
Range("C10").Value = ItemValues(9)
Range("C11").Value = ItemValues(10)
Range("C12").Value = ItemValues(11)
Range("C13").Value = ItemValues(12)
Range("C14").Value = ItemValues(13)
Range("C15").Value = ItemValues(14)
Range("C16").Value = ItemValues(15)
Range("C17").Value = ItemValues(16)
Range("C18").Value = ItemValues(17)
Range("C19").Value = ItemValues(18)
Range("C20").Value = ItemValues(19)
Range("C21").Value = ItemValues(20)
Range("C22").Value = ItemValues(21)
Range("C23").Value = ItemValues(22)
Range("C24").Value = ItemValues(23)
Range("C25").Value = ItemValues(24)
Range("C26").Value = ItemValues(25)
Range("C27").Value = ItemValues(26)
Range("C28").Value = ItemValues(27)
Range("C29").Value = ItemValues(28)
Range("C30").Value = ItemValues(29)
Range("C31").Value = ItemValues(30)
Range("C32").Value = ItemValues(31)
Range("C33").Value = ItemValues(32)
Range("C34").Value = ItemValues(33)
Range("C35").Value = ItemValues(34)
Range("C36").Value = ItemValues(35)
Range("C37").Value = ItemValues(36)
Range("C38").Value = ItemValues(37)
Range("C39").Value = ItemValues(38)
Range("C40").Value = ItemValues(39)
Range("C41").Value = ItemValues(40)
Range("C42").Value = ItemValues(41)
Range("C43").Value = ItemValues(42)
Range("C44").Value = ItemValues(43)
Range("C45").Value = ItemValues(44)
Range("C46").Value = ItemValues(45)
Range("C47").Value = ItemValues(46)
Range("C48").Value = ItemValues(47)
Range("C49").Value = ItemValues(48)
Range("C50").Value = ItemValues(49)
Range("C51").Value = ItemValues(50)
Range("C52").Value = ItemValues(51)
Range("C53").Value = ItemValues(52)
'--------------------------------------------------------------------------------
'Verify link quality for data # 1
Range("D2").Value = Qualities(1)
Range("E2").Value = TimeStamps(1)
Range("F2").Value = NumItems
 
errorhandler2:
  MsgBox "Error: " & Err.Description, vbCritical, "ERROR2"
  
End Sub
Private Sub worksheet_change(ByVal Selection As Range)
 
On Error Resume Next
  If Selection <> Range("B2") Then Exit Sub
  If OldValue <> Selection.Value Then
    OldValue = Selection.Value
    Values(1) = Selection.Value
  
       MyOPCGroup.SyncWrite 1, ServerHandles, Values, Errors
 
  End If
 
End Sub

[Ecniv]

OPC PLC. ..no idea..

Data point of view though you could to drop it into an array. Then you'd need the unique id of the item that has changed its value, match the id to the one in the array and update the value.

eg
id - value
1 - 100
2 - 103
3 - 200

change : 2 - 203
1 - 100
2 - 203
3 - 200

Is that the sort of thing you mean?
Can you find any unique ids to use?

[LuckyPete]

Thanks for the reply Ecniv. The Server passes only the changed values to my VBA Client. Here is a message received from the Server programmer but since he is Korean and has some difficulties with the english language I am not able to fix the code like he suggests.

Comparing OPC 1.0, different thing of OPC 2.0 is transferring updated value only without data request by client.

At that time, regiatred ID will be stored in “ClientHandles() “ and Value in “ItemValues()”

Please try to correct as below

Private Sub MyOPCGroup_DataChange(ByVal TransactionID As Long, ByVal NumItems As Long, ClientHandles() As Long, ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date)
 
On Error Resume Next
 
i = 1
 
Do Until i = NumItems+1
 
Cells(3, ClientHandles(i)).value = ItemValues(i)
Cells(4, ClientHandles(i)).Value = Qualities(i )
Cells(5, ClientHandles(i)).Value = TimeStamps(i)
 
Loop
 
End Sub

When I apply this part I get a "i = 1 / Variable not defined" error.

When I define i as Variant I get a "Application defined or Object defined ERROR"

From what I see, the "ClientHandles(i)).Value" should correspond to the exact array number so I only the index # 34 was to change then in the ClientHandles(i)).Value , the value of (i) could be 1 (for there is only one change) but its .Value would be 34... Thats where I sink

[LuckyPete]

The problem I'm having is in this part of the code:

Private Sub MyOPCGroup_DataChange(ByVal TransactionID As Long, ByVal NumItems As Long, ClientHandles() As Long, ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date)
 
On Error Resume Next 
 
Dim i As Integer
 
 
For i = 1 To NumItems
 
 
Cells(3, 2 + ClientHandles(i)) = ItemValues(i)
 
Range("B5").Value = Qualities(i)
Range("B6").Value = TimeStamps(i)
Range("B7").Value = NumItems
Range("B9").Value = ClientHandles(i).Values 
Range("B10").Value = i
 
Next
Exit Sub
 
End Sub

In cell B10 I can see the loop array number increasing
from 1 to 52 when I have to read them all.

When only a few are changing I do not see any values
in ClientHandles(i).Values

[felix538]

Lucky,

what was the outcome of your test?

[Davadvice]

this thread is a few years old and as he has only 3 posts i would imagine that he doesn't visit much.