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?

VB Code:
  1. '''backup b4 ****up

  2. Option Explicit

  3. Option Base 1

  4.  
  5. Const ServerName = "LGIS.LGEOPC" 'Déclare le serveur une constante inchangeable

  6. Dim WithEvents MyOPCServer As OpcServer 'Déclare MyOPCServer comme variable qui répond à un objet

  7. Dim WithEvents MyOPCGroup As OPCGroup  'Déclare MyOPCGroup comme variable qui répond à un objet

  8. Dim MyOPCGroupColl As OPCGroups

  9. Dim MyOPCItemColl As OPCItems

  10. Dim MyOPCItems As OPCItems

  11. Dim MyOPCItem As OPCItem

  12.  

  13.  
  14. Dim ClientHandles(52) As Long

  15. Dim ServerHandles() As Long

  16. Dim Values(52) As Variant

  17. Dim Errors() As Long

  18. Dim ItemIDs(52) As String

  19. Dim GroupName As String

  20. Dim NodeName As String

  21.  
  22. 'Dim i As Integer

  23.  
  24.  
  25. Dim OpcValues(52) As Variant

  26. Dim OldValue As Variant

  27. Sub Automat1()

  28. If Range("C2").Value = 101 Then

  29.     Feuils("sheet1").CommandButton1.Value = False

  30.     Feuils("sheet1").CommandButton2.Value = True

  31.     Else

  32.     End If

  33. End Sub

  34.  
  35. Private Sub CommandButton1_Click()

  36. Sheet1.StartClient

  37. 'Range("B2").Value = 2

  38.  
  39. End Sub

  40.  
  41. Sub StartClient()

  42.    

  43.    On Error GoTo errorhandler

  44.   

  45.     ItemIDs(1) = Range("A2").Value

  46.     ItemIDs(2) = Range("A3").Value

  47.     ItemIDs(3) = Range("A4").Value

  48.     ItemIDs(4) = Range("A5").Value

  49.     ItemIDs(5) = Range("A6").Value

  50.     ItemIDs(6) = Range("A7").Value

  51.     ItemIDs(7) = Range("A8").Value

  52.     ItemIDs(8) = Range("A9").Value

  53.     ItemIDs(9) = Range("A10").Value

  54.     ItemIDs(10) = Range("A11").Value

  55.     ItemIDs(11) = Range("A12").Value

  56.     ItemIDs(12) = Range("A13").Value

  57.     ItemIDs(13) = Range("A14").Value

  58.     ItemIDs(14) = Range("A15").Value

  59.     ItemIDs(15) = Range("A16").Value

  60.     ItemIDs(16) = Range("A17").Value

  61.     ItemIDs(17) = Range("A18").Value

  62.     ItemIDs(18) = Range("A19").Value

  63.     ItemIDs(19) = Range("A20").Value

  64.     ItemIDs(20) = Range("A21").Value

  65.     ItemIDs(21) = Range("A22").Value

  66.     ItemIDs(22) = Range("A23").Value

  67.     ItemIDs(23) = Range("A24").Value

  68.     ItemIDs(24) = Range("A25").Value

  69.     ItemIDs(25) = Range("A26").Value

  70.     ItemIDs(26) = Range("A27").Value

  71.     ItemIDs(27) = Range("A28").Value

  72.     ItemIDs(28) = Range("A29").Value

  73.     ItemIDs(29) = Range("A30").Value

  74.     ItemIDs(30) = Range("A31").Value

  75.     ItemIDs(31) = Range("A32").Value

  76.     ItemIDs(32) = Range("A33").Value

  77.     ItemIDs(33) = Range("A34").Value

  78.     ItemIDs(34) = Range("A35").Value

  79.     ItemIDs(35) = Range("A36").Value

  80.     ItemIDs(36) = Range("A37").Value

  81.     ItemIDs(37) = Range("A38").Value

  82.     ItemIDs(38) = Range("A39").Value

  83.     ItemIDs(39) = Range("A40").Value

  84.     ItemIDs(40) = Range("A41").Value

  85.     ItemIDs(41) = Range("A42").Value

  86.     ItemIDs(42) = Range("A43").Value

  87.     ItemIDs(43) = Range("A44").Value

  88.     ItemIDs(44) = Range("A45").Value

  89.     ItemIDs(45) = Range("A46").Value

  90.     ItemIDs(46) = Range("A47").Value

  91.     ItemIDs(47) = Range("A48").Value

  92.     ItemIDs(48) = Range("A49").Value

  93.     ItemIDs(49) = Range("A50").Value

  94.     ItemIDs(50) = Range("A51").Value

  95.     ItemIDs(51) = Range("A52").Value

  96.     ItemIDs(52) = Range("A53").Value

  97.    

  98.   'After declaring the objects and variables

  99.  'required for the OPCClientModule module,

  100.  'the connection process begins with creation

  101.  'of a new OPC Server Object

  102.   Set MyOPCServer = New OpcServer

  103.  '------------------------------

  104.  'Because we want to conncect to a specific

  105.  'OPC Server, the server must be defined.

  106.   MyOPCServer.Connect ServerName, NodeName

  107.  '------------------------------

  108.  'Each OPC Server has a specific interface for

  109.  'its Collection of Groups. To get the interface use:

  110.   Set MyOPCGroupColl = MyOPCServer.OPCGroups

  111.  '------------------------------

  112.   MyOPCGroupColl.DefaultGroupIsActive = True

  113.  '------------------------------

  114.  'Now we add a Group to our Group Collection.

  115.  'You can use any Group name you want.

  116.   GroupName = "MyGroup"

  117.   Set MyOPCGroup = MyOPCGroupColl.Add(GroupName)

  118.   Set MyOPCItemColl = MyOPCGroup.OPCItems

  119.  
  120.   MyOPCItemColl.AddItems 52, ItemIDs, ClientHandles, ServerHandles, Errors

  121.   MyOPCGroup.IsSubscribed = True

  122.     

  123.  
  124.   Exit Sub

  125. errorhandler:

  126.   MsgBox "Error: " & Err.Description, vbCritical, "ERROR"

  127. End Sub

  128.  
  129. Private Sub CommandButton2_Click()

  130. On Error Resume Next

  131. Sheet1.StopClient

  132. Range("B2").Value = 0  'xxxxxxxxxxxxxxxx

  133. End Sub

  134.  
  135. Sub StopClient()

  136.  
  137.   MyOPCGroupColl.RemoveAll

  138.  
  139.   MyOPCServer.Disconnect

  140.   Set MyOPCItemColl = Nothing

  141.   Set MyOPCGroup = Nothing

  142.   Set MyOPCGroupColl = Nothing

  143.   Set MyOPCServer = Nothing

  144.  
  145.  
  146. End Sub

  147.  
  148. Private Sub CommandButton3_Click()

  149.  
  150. 'Saving the file on clicking a button with names and dates for archives

  151.  
  152. Dim filename As String

  153. Dim filepath As String

  154. Dim strdate As String

  155. Dim fileID As String

  156.  
  157. strdate = Format(Date, "dd-mm-yyyy")

  158. filename = ("")

  159. filepath = "c:\AAA\"

  160. fileID = Range("Sheet1!C2").Value

  161.  
  162. Application.DisplayAlerts = False

  163. Application.ScreenUpdating = False

  164. ActiveWorkbook.SaveCopyAs filename:=filepath + filename + "Station no." + fileID + " " + strdate + ".xls"

  165. Application.DisplayAlerts = True

  166. Application.ScreenUpdating = True

  167.  
  168. End Sub

  169. Private Sub cmdSyncRead_Click()

  170. On Error Resume Next

  171.  
  172.  
  173. End Sub

  174.  
  175. 'THIS IS WHERE I HAVE PROBLEMS ... I THINK!

  176.  
  177. Private Sub MyOPCGroup_DataChange(ByVal TransactionID As Long, ByVal NumItems As Long, ClientHandles() As Long, ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date)

  178.     

  179.    On Error GoTo errorhandler2

  180.  
  181. Range("C2").Value = ItemValues(1)

  182. Range("C3").Value = ItemValues(2)

  183. Range("C4").Value = ItemValues(3)

  184. Range("C5").Value = ItemValues(4)

  185. Range("C6").Value = ItemValues(5)

  186. Range("C7").Value = ItemValues(6)

  187. Range("C8").Value = ItemValues(7)

  188. Range("C9").Value = ItemValues(8)

  189. Range("C10").Value = ItemValues(9)

  190. Range("C11").Value = ItemValues(10)

  191. Range("C12").Value = ItemValues(11)

  192. Range("C13").Value = ItemValues(12)

  193. Range("C14").Value = ItemValues(13)

  194. Range("C15").Value = ItemValues(14)

  195. Range("C16").Value = ItemValues(15)

  196. Range("C17").Value = ItemValues(16)

  197. Range("C18").Value = ItemValues(17)

  198. Range("C19").Value = ItemValues(18)

  199. Range("C20").Value = ItemValues(19)

  200. Range("C21").Value = ItemValues(20)

  201. Range("C22").Value = ItemValues(21)

  202. Range("C23").Value = ItemValues(22)

  203. Range("C24").Value = ItemValues(23)

  204. Range("C25").Value = ItemValues(24)

  205. Range("C26").Value = ItemValues(25)

  206. Range("C27").Value = ItemValues(26)

  207. Range("C28").Value = ItemValues(27)

  208. Range("C29").Value = ItemValues(28)

  209. Range("C30").Value = ItemValues(29)

  210. Range("C31").Value = ItemValues(30)

  211. Range("C32").Value = ItemValues(31)

  212. Range("C33").Value = ItemValues(32)

  213. Range("C34").Value = ItemValues(33)

  214. Range("C35").Value = ItemValues(34)

  215. Range("C36").Value = ItemValues(35)

  216. Range("C37").Value = ItemValues(36)

  217. Range("C38").Value = ItemValues(37)

  218. Range("C39").Value = ItemValues(38)

  219. Range("C40").Value = ItemValues(39)

  220. Range("C41").Value = ItemValues(40)

  221. Range("C42").Value = ItemValues(41)

  222. Range("C43").Value = ItemValues(42)

  223. Range("C44").Value = ItemValues(43)

  224. Range("C45").Value = ItemValues(44)

  225. Range("C46").Value = ItemValues(45)

  226. Range("C47").Value = ItemValues(46)

  227. Range("C48").Value = ItemValues(47)

  228. Range("C49").Value = ItemValues(48)

  229. Range("C50").Value = ItemValues(49)

  230. Range("C51").Value = ItemValues(50)

  231. Range("C52").Value = ItemValues(51)

  232. Range("C53").Value = ItemValues(52)

  233. '--------------------------------------------------------------------------------

  234. 'Verify link quality for data # 1

  235. Range("D2").Value = Qualities(1)

  236. Range("E2").Value = TimeStamps(1)

  237. Range("F2").Value = NumItems

  238.  
  239. errorhandler2:

  240.   MsgBox "Error: " & Err.Description, vbCritical, "ERROR2"

  241.   

  242. End Sub

  243. Private Sub worksheet_change(ByVal Selection As Range)

  244.  
  245. On Error Resume Next

  246.   If Selection <> Range("B2") Then Exit Sub

  247.   If OldValue <> Selection.Value Then

  248.     OldValue = Selection.Value

  249.     Values(1) = Selection.Value

  250.   

  251.        MyOPCGroup.SyncWrite 1, ServerHandles, Values, Errors

  252.  
  253.   End If

  254.  
  255. End Sub