Running a Macro from FORMULA Change (PLC)

[tlundPSI]

Hey guys new to this but I'm trying.

So im using excel as an OPC client through RSLinks paste special. I am copying data from the plc and moving it down when the PLC tells excel to (ie when a value goes high copy and paste values down a few lines).

So i have a macro to copy and move data down (Not the issue but for reference)

I am pretty happy with the way the macro runs but any suggestions are appreciated.


and some VBA code to run this macro (the issue)

The real issue is with the code to run the macro. The only way i could figure out how to trigger this macro from the plc was too paste special from RSlinks and use this value as a trigger. Because the cell with the imported data is a formula you cannot use a sub change (to my understanding and attempts) and though the old google machine i figured out sub calculate would work. Now this code for sub Worksheet_Calculate() works until you reopen excel where you get
_________________
|run-time error "13":|
|Type mistmatch___|

When debug is enabled it points toward the "If Range("A1").Value = 1 Then" line

Error 13 is the issue that i wish to eliminate and to my knowledge is caused by the if line.

Much Appreciated,

Thomas


Code if needed



Sub Store_Measurments()
'
' Store_Measurments Macro
'


Dim i As Integer
Range("B25").Select
For i = 1 To 23
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Next i
Range("B2:H23").Select
Selection.Copy
Range("b25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

End Sub



Private Sub Worksheet_Calculate()
'On Error Resume Next
If Range("A1").Value = 1 Then
Application.EnableEvents = False
Call Store_Measurments
Application.EnableEvents = True
End If
End Sub

 

[NoSparks]

My guess is it's text going into A1.
1 is a number,
try

If Range("A1").Value = "1" Then

 

[tlundPSI]

I have tried this and it returns the same issue

 

[ITWare2008]

At the debug window, if you write debug.print range("A1").value what comes up?

Vandalo

 

[NoSparks]

Same issue being Run-Time Error "13" Type Mismatch ?

Or not triggering the other macro ?

 

[Lukums]

Hey PSI,

My name is Luke Christie. I've recently made an entire scheduler through PLC to Excel and back.

If you need tips and tricks let me know!

I'm using Mitsubishi PLC's not through RS Links BUT I did run into some crappy things.

Try the below for me.

1. Instead of using a sheet to run your VBA put this in a module not sheet.
2. I misunderstand why you're using Private SUB?
3. I would try select the sheet before you jump into your IF statement especially if you're being tricky. Sheets("Sheetname").Select for example before the IF Statement let me know what happens.

It's going to be something very small but do the above and let me know.

BACKUP First I've not tested this.

Sub Store_Measurments()
'
' Store_Measurments Macro
'


Dim i As Integer
Range("B25").Select
For i = 1 To 23
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Next i
Range("B2:H23").Select
Selection.Copy
Range("b25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Call Worksheet_Calculate

End Sub


Sub Worksheet_Calculate()
'On Error Resume Next
Sheets("SheetName").Select
Range("A1").Select
If Range("A1").Value = 1 Then
Application.EnableEvents = False
Call Store_Measurments
Application.EnableEvents = True
End If
End Sub


'' Let me know how you go.

 

[tlundPSI]

So it does run the macro but upon startup error 13 comes up so this is what im trying to remove.

I typed debug.print range("A1") into my code in the debug window and nothing happened. Im not really sure how the debug.print works to be honest.

Lukums: Should i not be using private sub? With the way i have the code right now if i remove private it does not run macro. Using the code that you provided there are no errors but the macro does not run. I adjusted my worksheet_calculate in the location it is in now to match what you suggested and the error 13 remains.

What i think the problem is, is im calling the value of A1 but it is really a formula if that makes any sense.

CHEERS,

Thomas

 

[NoSparks]

Thomas, I don't think Lukums examined your original post in much depth. You have things as they should be with regards to Private and modules.

When you get the run time error and the "If Range("A1").Value = 1 Then" line is high-lighted, will the procedure advance past that line using the F8 key?

What is the formula in A1?

To see what Excel is seeing in A1, select A1 and run this little macro, it prints to the Immediate window (Ctrl+g), let us know.

Sub CheckOfCharacters()
    Dim i As Integer
    Dim str As String

str = ActiveCell.Value

    For i = 1 To Len(str)
        Debug.Print Mid(str, i, 1) & "  =  " & Asc(Mid(str, i, 1))
    Next

End Sub

 

[tlundPSI]

I placed this code in the sheet but nothing appears to be happening.

The code in cell A1 is =RSLINX|Laker_Energy_OPC!'trigger,L1,C1'

 

[tlundPSI]

Sorry didn't execute correctly it pops up saying type mismatch when i run that macro