Hello to all. This is my first post in here and I am totally new in writing VBA macros in Excel.
I have an application which is consisted of a PLC(programmable logic controller) and an excel sheet which will run some macros. From the PLC I read a specific value using an external link in Unicode text formating.
I want this value (is a string) to compare it with other values which are stored in a column. If the result of the string compare function in any of the rows is equal with 0 I want the excel sheet to return another value to the PLC. My problem is that I cannot trigger automatically the Worksheet_Change function.
The sequence of the whole application is the following:
check if the value you are reading is equal with 0 -> If not execute the Compare function -> Check the result of the compare function and reutrn a specific value.
My code is the following:
Sub Compare()
Dim A As Long 'The counter we will be using the process our records
Dim Position As Long 'This variable will contain the position of the character searched for
For A = 2 To 13
Barcode_A = ActiveSheet.Cells(A, 1).Value 'Assigns the value to the first string to compare
Barcode_B = ActiveSheet.Cells(A, 2).Value 'Assigns the value to the first string to compare
ActiveSheet.Cells(A, 4).Value = StrComp(Barcode_A, Barcode_B, vbTextCompare)
If ActiveSheet.Cells(A, 4).Value = 0 Then Range("H3") = ActiveSheet.Cells(A, 3)
Next
End Sub
Public Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("H1").Value) Is Nothing Then Check_Barcode_Entry
End Sub
Sub Check_Barcode_Entry()
If (ActiveSheet.Range("H1").Select <> vbNullString) Then Compare
End Sub
I read the value from the PLC in the cell H1. Barcode A is the entries which will be stored in the Excel.
Please any advice will be appreciated.
I have an application which is consisted of a PLC(programmable logic controller) and an excel sheet which will run some macros. From the PLC I read a specific value using an external link in Unicode text formating.
I want this value (is a string) to compare it with other values which are stored in a column. If the result of the string compare function in any of the rows is equal with 0 I want the excel sheet to return another value to the PLC. My problem is that I cannot trigger automatically the Worksheet_Change function.
The sequence of the whole application is the following:
check if the value you are reading is equal with 0 -> If not execute the Compare function -> Check the result of the compare function and reutrn a specific value.
My code is the following:
Sub Compare()
Dim A As Long 'The counter we will be using the process our records
Dim Position As Long 'This variable will contain the position of the character searched for
For A = 2 To 13
Barcode_A = ActiveSheet.Cells(A, 1).Value 'Assigns the value to the first string to compare
Barcode_B = ActiveSheet.Cells(A, 2).Value 'Assigns the value to the first string to compare
ActiveSheet.Cells(A, 4).Value = StrComp(Barcode_A, Barcode_B, vbTextCompare)
If ActiveSheet.Cells(A, 4).Value = 0 Then Range("H3") = ActiveSheet.Cells(A, 3)
Next
End Sub
Public Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("H1").Value) Is Nothing Then Check_Barcode_Entry
End Sub
Sub Check_Barcode_Entry()
If (ActiveSheet.Range("H1").Select <> vbNullString) Then Compare
End Sub
I read the value from the PLC in the cell H1. Barcode A is the entries which will be stored in the Excel.
Please any advice will be appreciated.