Need to output to serial port if string in column is duplicated.

[MikeHaras]

Hi All

I am running a program that collects information from data registers in a PLC, the information is written into an excel spread sheet, I need to check if the data written into the cell is unique to all previous data in that column, if the data is unique
i then need to send a single string of data to the plc via serial port to initiate a true or false marker.

Will appreciate any tips, advice or help.

regards

[AlphaFrog]

Here's a good step-by-step guide to VBA serial port control.
Serial Port Communication in Excel (VBA)
Or
Visual Basic for Applications Serial Port Software Example

I imagine you could use the Worksheet_Change event procedure to test if the most recent entry already exists and call a send-serial-data macro when necessary.

[MikeHaras]

Hi AlphaFrog

Thanks for the information, will take a look at the suggested sites and let you know how helpful they are.

[MikeHaras]

Thanks AlphaFrog

So far i have managed to set 2 outputs on and off using the Worksheet_BeforeDoubleClick, that is a step in the right direction, will now work in a method to do the search for duplicates, all the examples i have gone through for the look up involves first sorting the column then comparing 2 cells, the one being below the other due to the sorting. I would however prefer not to sort the data but rather check if the data that has been entered by the eServer program is unique or not. Every time the operator enters a serial number into the PLC the eserver program pulls the serial number into the next field in a particular column, when this cell is populated i need to compare it to all the previous fields and then output the result to the PLC.
Thanks so far for pointing me in the right direction of the serial communication, any other ideas on how i would be able to detect when the next data is written in to the next field and then how to compare that data to all previous data?

So far the worksheet code looks like this:

   Option Explicit

'USE Function Code 0x05 Force ON Coil Device
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim send_string$, NETComm1
 NETComm1 = 7
 
 
If Target.Address = "$A$1" Then
 
 send_string$ = ":01050500FF00F6" 'Y0 ON
 
End If
   
If Target.Address = "$A$2" Then
 
 send_string$ = ":010505000000F5" 'Device number, device address, address value, number of address, checksum. Y0 OFF
 
End If
 
If Target.Address = "$A$4" Then

 send_string$ = ":01050506FF00F0"  'Y6 ON
  
End If
  
If Target.Address = "$A$5" Then

 send_string$ = ":010505060000EF"  'Y6 OFF
  
End If

  'send to plc
  
  send_string$ = Send_To_PLC(Me.NETComm1, send_string$)
  
  
End Sub

Public Function Send_To_PLC(ByVal objNETCOMM As NETComm, ByVal send_string$) As String
 
  Dim Buffer$
  
  
  send_string$ = Chr(&H3A) + send_string$ + Chr(&HD) + Chr(&HA)
  
 
  If objNETCOMM.PortOpen = False Then objNETCOMM.PortOpen = True
  
  objNETCOMM.Output = send_string$
  
   Send_To_PLC = send_string$
   
Do
DoEvents
Buffer$ = Buffer$ & objNETCOMM.InputData
Loop Until InStr(Buffer$, vbCrLf)

objNETCOMM.PortOpen = False
 
End Function

[AlphaFrog]

I don't quite follow the specifics of what you want.

I suggest you could use the .Find method to search for the first match and then use the .FindNext method to search for any additional matches. The data wouldn't have to be sorted and you could search a column from the top down or from the bottom up.
Here's a good guide. http://www.thecodenet.co.uk/articles.php?id=18

[MikeHaras]

Hi AlphaFrog

Once again thanks for your assistance so far.

I will try to explain what i am trying to achieve, bare with me as i am a total novice to vba.

I am running a program that retrieves a serial code from a plc after that serial code has been entered into the plc buy an operator.

the first serial code is entered into column C1 of a excel spread sheet, the following serial code is entered into column C2 of the same sheet, all subsequent serial codes are sequentially entered one after the other in column C.

What I am trying to achieve with the vba code, is to have the code compare the newly entered serial number at the bottom of column C with all previous entered serial numbers in column C, If the newly entered serial number is unique to all the other serial numbers in column C then, the code must send a string via the serial port back to the plc to set a go marker. If the serial number is a duplicate of an existing serial number in column C then the newly entered serial number at the bottom of column C must be deleted and the code must send an alternative string via the serial port to the plc to set a no go marker, the reset of the 2 markers in the plc will be taken care of by the code in the plc.

Your assistance so far is greatly appreciated, and has lead me to some positive results in that i am able to do a automated check in column C when the existing software populates the next cell in column C, however the code (copied and altered from examples) deletes the newly entered serial number as well as its duplicate.

I am able to send the for mentioned go, no go, markers to the plc via double clicking pre defined cells, once the look up is operating successfully i should be able to figure out (with some time) how to incorporate the go, no go, send string function.

I hope its more clear as to what i am trying to achieve.

[AlphaFrog]

Try something like this. It's an event procedure that automatically triggers when a change is made to the worksheet.
Events And Event Procedures In VBA


Put this code in the worksheet's code module and not a standard code module.

• Right-click on the worksheet tab
• Select View Code from the pop-up context menu
• Paste the code below in the VBA edit window

Change the Go_Marker_Macro and No_Go_Marker_Macro names to suit or put in the code that sends your serial port code

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.Column <> 3 Then Exit Sub 'column C
    
    If Application.CountIf(Columns("C"), Target) = 1 Then
        'Unique serial number
        Call Go_Marker_Macro
    Else
        'Duplicated Serial number
        Target.ClearContents
        Call No_Go_Marker_Macro
    End If
    
End Sub

[MikeHaras]

Thanks for the prompt reply, i will immediately give the above code a try and let you know how it turns out.

[MikeHaras]

Hi AlphaFrog

Thanks again for your code, it works like a charm, just the way I needed it to work.

I have been trying to get this working for 2 weeks now.

You are an absolute star and have saved my bacon.

Must say that the introduction to vb has been rather taxing, but has left me with an eagerness to learn more.

And thats a wrap.

Sample code that may assist anyone trying to communicate to DELTA PLC, if there is anyone else trying other functions to read or write to a Delta PLC, I have other communication strings that may be of use.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    
Dim send_string$, NETComm1
 NETComm1 = 7
    
    
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.Column <> 3 Then Exit Sub 'column C
        
        If Application.CountIf(Columns("C"), Target) = 1 Then
        'Unique serial number
        send_string$ = ":01050500FF00F6" 'Y0 ON
    Else
        'Duplicated Serial number
        Target.ClearContents
        send_string$ = ":01050506FF00F0"  'Y6 ON
    End If
    
   'send to plc
    send_string$ = Send_To_PLC(Me.NETComm1, send_string$)
    
End Sub

'USE Function Code 0x05 Force ON Coil Device
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim send_string$, NETComm1
 NETComm1 = 7
 
 
If Target.Address = "$A$1" Then
 
 send_string$ = ":01050500FF00F6" 'Y0 ON
 
End If
   
If Target.Address = "$A$2" Then
 
 send_string$ = ":010505000000F5" 'Device number, device instruction, address value, number of address, checksum. Y0 OFF
 
End If
 
If Target.Address = "$A$4" Then

 send_string$ = ":01050506FF00F0"  'Y6 ON
  
End If
  
If Target.Address = "$A$5" Then

 send_string$ = ":010505060000EF"  'Y6 OFF
  
End If


  'send to plc
  
  send_string$ = Send_To_PLC(Me.NETComm1, send_string$)
  
  
End Sub

Public Function Send_To_PLC(ByVal objNETCOMM As NETComm, ByVal send_string$) As String
 
  Dim Buffer$
  
  
  send_string$ = Chr(&H3A) + send_string$ + Chr(&HD) + Chr(&HA)
  
 
  If objNETCOMM.PortOpen = False Then objNETCOMM.PortOpen = True
  
  objNETCOMM.Output = send_string$
  
   Send_To_PLC = send_string$
   
Do
DoEvents
Buffer$ = Buffer$ & objNETCOMM.InputData
Loop Until InStr(Buffer$, vbCrLf)

objNETCOMM.PortOpen = False
 
End Function

[MikeHaras]

Thanks for correcting the code tags, was having a little difficulty with that.