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]

Can you share your workbook using one drive or google drive or some other site so I can actually see what you're working with?

 

[tlundPSI]

https://drive.google.com/open?id=0B4m9j514agrLdF9wb09UTTFsSjQ

Let me know if that doesnt work i believe have sharing turned on

 

[NoSparks]

Thanks Thomas,

My macro indicates A1 has only a single ascii character(49) in it,
which is the number 1, so there should be no type mismatch using = 1

In post #7 you indicate getting the error upon startup.
If you Reset the macro, or wait a minute and hit F5, do things run as expected after that?

My line of thought here is the macro being triggered before everything else is prepared.

 

[tlundPSI]

Thanks for the Help NoSpark.

Yes the program runs after start up its just when the file is opened that this occurs.

It is not an issue for me to step through this bug but for the end user i would like to do my best to eliminate it. Do you have a recommendation on how to fix this?

 

[NoSparks]

You could try putting a pause in the macro but it will delay things every time.
for a 3 second pause

Private Sub Worksheet_Calculate()

Application.Wait (Now + TimeValue("0:00:03"))

If Range("A1").Value = 1 Then
    Application.EnableEvents = False
    Call Store_Measurments
    Application.EnableEvents = True
End If
End Sub

I honestly don't know if the Wait will screw up other things so make sure you're testing on a copy of the workbook.

Good luck, let me know if that helps.

EDIT: seems this won't work. I just closed and opened the file and there was no pause.... back to the drawing board.

 

[NoSparks]

OK, maybe this time....

Place these subs in the ThisWorkbook module

Private Sub Workbook_Open()
'Runs macro 5 seconds after Excel is opened

Application.OnTime Now + TimeValue("00:00:05"), "Initial_Delay"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Reset for next time workbook opens

Sheets("Sheet1").Range("A2").Value = 0

End Sub

In the standard module you already have, add sub

Sub Initial_Delay()
    Application.EnableEvents = False
    Sheets("Sheet1").Range("A2").Value = 1
    Application.EnableEvents = True
End Sub

Alter the sheet module Worksheet_Calculate sub

Private Sub Worksheet_Calculate()

If Range("A2") <> 1 Then Exit Sub

If Range("A1").Value = 1 Then
    Application.EnableEvents = False
    Call Store_Measurments
    Application.EnableEvents = True
End If
End Sub

I don't have all the links and background stuff running that you do so can't check things out thouroughly.
Try this on a Copy of your workbook.

Let me know how it goes.

 

[tlundPSI]

Nospark: That does the trick thanks a lot!!!

 

[NoSparks]

Hey, that's good, I knew there was a solution, just had to find it.

A comment....
If you intend to keep the "Record Current Data" and "Save & New" buttons on the sheet, both will cause the sheet to recalculate which is going to trigger the Worksheet_Calculate event which in turn will call the Store_Measurments macro. You'll need to disable and re-enable events within the macros to prevent this.