Excel & vba

matzkon86

New member
Joined
Feb 18, 2013
Messages
4
Reaction score
0
Points
0
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.
 
matzkon86, hi

Can you post a spreadsheet example of data that you have now and are actually working with?

Thanks
 
Spreadsheet demonstration

Hello NoS and thanks for your reply.
I will try to demonstrate it here...
Barcode A
Barcode B
StrComp Result
Process Number
Input from PLC
Return process number to PLC

In the real application the input of the PLC will be placed in the Barcode B column. I just wanted to make some tests now..
As I have written in my first post the input value from the PLC is in Unicode text from.

Thanks
 
Sorry for the appearance of my demonstration but is clear the I am new in this site...!!!!
 
matzkon86,

As a retired electrician that had some doings with PLCs, and having interest in Excel and VBA, I'd really like to assist with this.
Personally I'm partial to doing everything in the ladder logic because it's inevitable that somebody will close or disconnect Excel and the PLC will be stuck in its current state.

That said, I sort of know what you are trying to do but it would sure be nice to have some actual data to work with. How about mocking up a spreadsheet with actual numbers, texts and such so we all know what you're dealing with.

Your A and D columns, are they constants?
When and how does column B get populated?
Having declared "Position as long" in your original post would seem that the position of the difference is of importance, but maybe the populating of column B looks after that.

Just want to help if I can
NoS
 
Hello NoS... and thank you for your help....I will try to explain everything...
In column A we will have a lot of different bar codes(hundreds or maybe thousands).
Column B will be populated automatically. I think two different ways. The first way is to add in every row the DDE link from the PLC. The second way(and probably the one I will use) I will have one cell with the DDE link from the PLC and depending on how many bar codes we will have in column A (variant A in my code) I will populate for the same number Bar code B column by copying the value of the DDE link.
In the PLC there two different states of the bar code value. The first is a scanned Bar code which can have numbers, letters or both(therefore has been specified as string). The second is the Null Sting.
The way I want my code to operate is the following: "if bar_code from the PLC( value of the DDE link- H1 cell in my code) has changed check if it is NOT equal with the Null String. If It is not equal then populate column B (Bar code B column) with it, execute the StrComp for every line between Bar Code A and Bar Code B column and save the result of it in column D. Then check Column D. When you will find the first '0' (result of StrComp when the two strings are equal) return to the PLC(cell H3) the number of the process which is stored in column C in that line.

I do not have a problem with how to send or return data to and from the PLC. My problem is how I will make the compare routine to be triggered automatically from a specific event. In this occasion i have selected the Worksheet_Change event for cell H1 which stores the value of the DDE link. Maybe I haven't selected the proper event.
Another way i thought is to use a timer and to check every 1s for example the bar code entry.

I also prefer to have all the data in the PLC but in this occasion the Bar Code database will change every 1 week approx. As you can understand it will be really difficult to store all these data in the PLC. Therefore we use an industrial PC with Excel installed on it..
 
Well... in that case just remove the .Value from your original Worksheet_Change and your Check_Barcode_Entry will run.

Perhaps something like this will look after the null strings.

Code:
Public Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("H1")) Is Nothing Then
    If IsEmpty(Range("H1")) Then Exit Sub
    If IsNull(Range("H1")) Then Exit Sub
    Call Check_Barcode_Entry
End If
End Sub
 
Back
Top