PDA

View Full Version : Infinite VBA Loop



CASRevis
03-12-2018, 07:29 AM
Okay, so I'm a PLC programmer and not well versed in VBA code. What I'm trying to do is update my tables with arrays of data from the PLC and save the sheet as a pdf with the name of the order number. What I'm having trouble with is getting the code to start. We don't want a vba button or anything, I want it to start when the save bit changes from a 0 to 1, save the file, and do the same the next time the bit cycles. I'm unsure on how I get this to constantly look at that bit without needing a button, ideally I would just open the xlsm and have it save as it goes. Any help is appreciated.


Private Sub SaveAs()
Dim path As String
path = "C:\Users\zachary.revis\Documents\Order Reports\"
Dim Filename As String
Filename = Range("B1").Text
Dim SaveBit As Integer
SaveBit = Range("G4").Value
Application.DisplayAlerts = False

If SaveBit > 0 Then

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
path & Filename & ".pdf", Quality:=xlQualityStandard _

End If

End Sub


21805

SamT
03-12-2018, 09:13 AM
I want it to start when the save bit changes from a 0 to 1
In ThisWorkbook Code page

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Me.Saved = True Then 'call the Save as PDF sub
End Sub
However, the Saved property of the Workbook (Me) is set False on every change to any sheet, (Chart, Worksheet, Xl4MacroSheet, XL5Dialogsheet). Some changes can be triggered by other-than-User actions. You might want to consider adding a menu item to the Ribbon.

This is the code I use to save a copy of MyPersonal.xls whenever I change it. ThisWorkbook code page:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then Me.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Me.SaveCopyAs ("E:\COMPUTING\Programming\_VBA\MyPersonal\" & CDbl(Now) & "- Personal.xls")
End Sub

This is my Version Control sub. It also goes in the book's ThisWorkbook code page. It requires using the Tools>>Macros menu
Sub Save_Current_Version()
'Requires Name like StringDotTimeStampDotxls.
Me.SaveAs Me.Path & "\" & Split(Me.Name, ".")(0) & "." & CDbl(Now) & ".xls"
End Sub

I hope you get some ideas.

CASRevis
08-24-2018, 05:41 AM
Okay, so I'm not sure if I've explained this right, and I have to stress that I am an absolute beginner in VBA so the more you can spell it out for me the better.
When my order completes my PLC Program pushes a 1 into a data register which is being looked at by the "SaveBit" range G4 for exactly one second.

When that happens it's telling the Order Report that the order completed, (Or was aborted) and it's time to save to a .pdf, this program works just repeating itself every second, but it inevitably crashes
sometime throughout the day and they keep losing their order information.
I'm trying to figure out a way for this to constantly keep scanning to see if G4 changes to a 1, execute the Save, wait for a few seconds, and then start scanning again. I haven't found a way to loop this without
it heavily bogging down or just immediately crashing. Here's what I have it's somewhat different from before.
ThisWorkbook

Private Sub Workbook_Open()
Timer2
End Sub

Module1

Private Sub PDF_Export()Dim path As String
path = "C:\Users\Revis\Documents\Order Reports\"
Dim Filename As String
Filename = Range("B1").Text
Dim SaveBit As Integer
SaveBit = Range("G4").Value

If SaveBit > 0 Then

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=path & Filename & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Timer2
End If

Timer
End Sub

Sub Timer()
Application.OnTime Now() + TimeValue("00:00:01"), "PDF_Export"
End Sub

Sub Timer2()
Application.OnTime Now() + TimeValue("00:00:45"), "PDF_Export"
End Sub

p45cal
08-27-2018, 01:27 AM
You can use event handlers to detect changes to the sheet (or elsewhere).
How is the data in cell G4 being updated?
Depending on this we can use either a Worksheet_Change event or a Worksheet_Calculate event to monitor/respond to the status of cell G4.
We shouldn't need a timer, we should be able to detect a change to G4.
What changes G4 back to 0?

Does the PLC put a 1 in cell G4 for 1 second and change it back to 0?
How quickly will it change? Does it change faster than the saving process?
Is it the same sheet that needs saving under a different name or is a new sheet generated each time?

CASRevis
08-29-2018, 05:43 AM
G4 is looking at a register in the PLC, when the order completes, it pushes a 1 into the register for exactly 1 second then changes back to a 0.


You can use event handlers to detect changes to the sheet (or elsewhere).
How is the data in cell G4 being updated?
Depending on this we can use either a Worksheet_Change event or a Worksheet_Calculate event to monitor/respond to the status of cell G4.
We shouldn't need a timer, we should be able to detect a change to G4.
What changes G4 back to 0?

Does the PLC put a 1 in cell G4 for 1 second and change it back to 0?
How quickly will it change? Does it change faster than the saving process?
Is it the same sheet that needs saving under a different name or is a new sheet generated each time?

p45cal
08-29-2018, 06:29 AM
I'm not at all clear on how cell G4 is getting its value 'pushed' into it. Is there a formula in cell G4?
An experiment: In the sheet concerned's code-module (right-click on the sheet's tab and choose View Code) place the following 2 event handlers:
Private Sub Worksheet_Calculate()
Debug.Print "Calc event: G4 value = ]" & Range("G4").Value & "[ " & Now()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "Change event: " & Target.Address(0, 0) & ", G4 value = ]" & Range("G4").Value & "[ " & Now()
End Sub

Let the sheet run live for a while (let G4 change a few times).
Then in the Immediate pane of the VBE (Ctrl+g if you can't see it) you should have a few lines like this:
Change event: G4, G4 value = ]1[ 29/08/2018 14:19:02
Calc event: G4 value = ]1[ 29/08/2018 14:19:34
Change event: G1, G4 value = ]1[ 29/08/2018 14:19:34
Calc event: G4 value = ]8[ 29/08/2018 14:19:41
Change event: G4, G4 value = ]8[ 29/08/2018 14:19:41
Can you copy and paste them here?

Working blind like this is very difficult and I'm mighty reluctant to do it, so can you provide a workbook (not a picture of one), desensitised if there's sensitive data within it? It will answer a lot of questions and save us guessing (wrongly) aspects of your setup.

CASRevis
08-29-2018, 07:52 AM
Okay so G4 does have a formula like the rest of the cells that are being pushed data from the PLC
G4's formula is =RSLINX|Feed_Mill!'Confirm_Send2,L1,C1'
That cell will just update with whatever the "Confirm_Send2" register has, a 1 or a 0.
22792
22793
The file itself doesn't contain any sensitive data.
All of the cells containing any data will be #REF without being linked to the OPC topic through RSLinx


I'm not at all clear on how cell G4 is getting its value 'pushed' into it. Is there a formula in cell G4?
An experiment: In the sheet concerned's code-module (right-click on the sheet's tab and choose View Code) place the following 2 event handlers:
Private Sub Worksheet_Calculate()
Debug.Print "Calc event: G4 value = ]" & Range("G4").Value & "[ " & Now()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "Change event: " & Target.Address(0, 0) & ", G4 value = ]" & Range("G4").Value & "[ " & Now()
End Sub

Let the sheet run live for a while (let G4 change a few times).
Then in the Immediate pane of the VBE (Ctrl+g if you can't see it) you should have a few lines like this:
Change event: G4, G4 value = ]1[ 29/08/2018 14:19:02
Calc event: G4 value = ]1[ 29/08/2018 14:19:34
Change event: G1, G4 value = ]1[ 29/08/2018 14:19:34
Calc event: G4 value = ]8[ 29/08/2018 14:19:41
Change event: G4, G4 value = ]8[ 29/08/2018 14:19:41
Can you copy and paste them here?

Working blind like this is very difficult and I'm mighty reluctant to do it, so can you provide a workbook (not a picture of one), desensitised if there's sensitive data within it? It will answer a lot of questions and save us guessing (wrongly) aspects of your setup.

p45cal
08-29-2018, 08:28 AM
OK. I'd still like you to do the 'experiment' and paste results here.

And, important, disable your workbook_open event handler which starts Timer2.

It looks like these are DDE links so I wouldn't expect many Worksheet_Change events to occur, but I'd be interested if there are any Worksheet_Calculate events taking place.
If not, great (conversely!) because we can get them to occur on just the change of G4.

The attached could/should be used to carry out the experiment. It has the code added and existing code tweaked for the test.

CASRevis
08-29-2018, 12:34 PM
I appreciate it, I'll give it a shot here and post the results

CASRevis
08-30-2018, 07:12 AM
So the order completed and told it to save at about 10:09:43

Calc event: G4 value = ]0[ 8/30/2018 10:05:33 AMCalc event: G4 value = ]0[ 8/30/2018 10:06:31 AM
Calc event: G4 value = ]0[ 8/30/2018 10:06:54 AM
Calc event: G4 value = ]0[ 8/30/2018 10:06:59 AM
Calc event: G4 value = ]0[ 8/30/2018 10:07:00 AM
Calc event: G4 value = ]0[ 8/30/2018 10:07:01 AM
Calc event: G4 value = ]0[ 8/30/2018 10:07:02 AM
Calc event: G4 value = ]0[ 8/30/2018 10:07:03 AM
Calc event: G4 value = ]0[ 8/30/2018 10:07:03 AM
Calc event: G4 value = ]0[ 8/30/2018 10:07:04 AM
Calc event: G4 value = ]0[ 8/30/2018 10:07:04 AM
Calc event: G4 value = ]0[ 8/30/2018 10:09:32 AM
Calc event: G4 value = ]0[ 8/30/2018 10:09:37 AM
Calc event: G4 value = ]1[ 8/30/2018 10:09:43 AM
Calc event: G4 value = ]0[ 8/30/2018 10:09:44 AM
Calc event: G4 value = ]0[ 8/30/2018 10:09:47 AM
Calc event: G4 value = ]0[ 8/30/2018 10:09:47 AM

p45cal
08-30-2018, 08:30 AM
As I thought, not a worksheet_change event in sight.

Attached is another file which I'd like you to use briefly, as before, and paste the results here.

In it, I've scrubbed the worksheet_change event handler, changed slightly the worksheet_calculate event handler to stop errors when the cell contains #REF.
I've added a formula in cell R1 which refers to cell G4. I'm expecting this 'calculation' to trigger the calculate event when G4 changes. If this works cell R1 font can be set to white, or we can move that cell outside the print area that you've set, so that it doesn't appear on any printout or the pdf file.

I've also added another sheet named Monitor (if it works this sheet can be hidden) which only has a link to G4 of Sheet1. It has its own event handler in its code module (prints data to the Immediate pane in a similar way) which I'm hoping will ONLY trigger when G4 on Sheet1 changes. If this works this might be a robust method of triggering the pdf saving process, but this is only in the concept phase still!

Otherwise, I'm still looking at Sheet1's event handlers where I'll be asking whether G4 = 1 and if so start the pdf saving process.
Potential things to tackle/handle may be:

Different orders being completed within a second
Changes to the order sheet between G4 being set to 1 and the printing process starting.
More calculate events being generated within a second, so will need to avoid the same pdf being saved twice. We'll need a software latch (in vba) to ensure that there's been an intervening return to zero in cell G4 between saves.


edit post posting:
I was looking for what other calculations there were on the order sheet and I noted them in columns S, T & U, along with several in row 50. It probably these that are triggering the calculate event when data cimes in throught the RSLINX DDE links. No matter. I noted that some formula seem a tad long:
The likes of
=H9+H10+H11+H12+H13+H14+H15+H16+H17+H18+H19+H20+H21+H22+H23+H24+H25+H26+H27 +H28+H29+H30+H31+H32+H33+H34+H35+H37+H36+H38+H39+H40+H41+H42+H43+H44+H45+H4 6+H47+H48+H49
can be shortened to:
=SUM(H9:H49)

I also note that there may be a missing formula in cell Q40.

CASRevis
08-30-2018, 08:53 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:50:00 AMReport Sheet Calc event: G4 text = ]0[ 8/30/2018 11:50:02 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:50:03 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:50:03 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:50:04 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:50:04 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:52:17 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:52:22 AM
Monitor Sheet Calc event: A1 text = ]1[ 8/30/2018 11:52:25 AM
Report Sheet Calc event: G4 text = ]1[ 8/30/2018 11:52:25 AM
Monitor Sheet Calc event: A1 text = ]0[ 8/30/2018 11:52:26 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:52:26 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:52:29 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:52:30 AM
Report Sheet Calc event: G4 text = ]0[ 8/30/2018 11:52:30 AM

p45cal
08-30-2018, 03:45 PM
This possible solution does not require an additional Monitor sheet.
The following code should go into the order sheet's code-module to replace any other Worksheet_Calculate() event handler that might be there.
No need for any Worksheet_Change() event handler at all.
Remove all Timer code and calls to any timer code; no workbook_open event handler calling anything.

Private Sub Worksheet_Calculate()
'there HAS to be a formula on this sheet somewhere that refers to G4 in order for this event handler to be triggered on a change of G4.
Static LastSaveBit
x = Range("G4").Value 'grab the savebit value asap.
If IsEmpty(LastSaveBit) Then LastSaveBit = 0 'initialise LastSaveBit on first run.

If x = 1 And LastSaveBit = 0 Then
LastSaveBit = 1
'Debug.Print "saving " & Now
PDF_Export
' Else
' Debug.Print "NOT saving " & Now
End If
LastSaveBit = x
End Sub
The intention with this code is not to allow a new save to take place without there having been an intervening G4 value of 0.
I've left some commented-out code which are remnants of my testing; you may want to re-enable it for your own testing, otherwise you can delete all comments.
This code requires there to be a formula on the same sheet which refers to cell G4, in order for a change in G4 to trigger the calculate event.
FYI a Static variable is one that retains its value between calls to the sub that contains it.

Your PDF_Export sub should NOT be Private, but start with Sub… and it should be without timer calls, nor any checks on G4 - they've already been done by the time this routine is called:
Sub PDF_Export()
Dim Filename As String, path As String
path = "C:\Users\zachary.revis\Documents\Order Reports\"
With ActiveSheet
Filename = .Range("B1").Text 'I'm assuming B1 is on the order sheet which is the active sheet.
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=path & Filename & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End With
End Sub
or similar.
This last code should really have its range references qualified, eg.:
With Sheets("Order")
or:
With Sheets("Sheet1")
rather than:
With ActiveSheet

And if cell B1 where the filename is held is on a different sheet then use the likes of:
Filename = Sheets("PutAnotherSheet'sNameHere").Range("B1").Text

CASRevis
09-04-2018, 05:28 AM
Okay, so I finally had a chance to test this and it does work, but I have to keep pressing the run macro button.
I bumped up the time that the savebit is active just for testing, and pushed the run macro button a few times and it worked well.


NOT saving 9/4/2018 8:14:27 AMsaving 9/4/2018 8:17:06 AM
NOT saving 9/4/2018 8:17:18 AM

p45cal
09-04-2018, 07:06 AM
You shouldn't have to keep running the macro manually. In fact you should never have to run the macro manually at all.
1. Have you ensured that there is "a formula on the same sheet which refers to cell G4, in order for a change in G4 to trigger the calculate event"? That formula can be as simple as = G4 anywhere on the same sheet; it doesn't even need to be in the area of the sheet which is printed/saved.
2. Are Events disabled? You can check manually at any time with this command in the Immediate pane of the VBE:
?Application.EnableEvents
and press Enter. It should come back True. If it doesn't then (a) enable the events with the command:
Application.EnableEvents = True
and press Enter.
And (b) this last will come to nought if there's any other code in the file which disables events and doesn't re-enable them. Look for any line of code elsewhere in the file (or any other open Excel workbook) that looks a bit like:
Application.EnableEvents = False
without a corresponding:
Application.EnableEvents = True
shortly after.

CASRevis
09-04-2018, 07:22 AM
:wotIt worked! No freezes and nothing blew up! I appreciated your help p45, I'm going to run this for the week and see how she goes!

CASRevis
01-09-2019, 08:21 AM
Alright, so the Order Report Excel file worked fantastic. Now I'm back to a file import I stopped working on about a year ago. I'm looking to combine the Import and Report xlsm into one excel file to start, and convert the Import file to the event handling. When I try to run both Import and Report the Import file crashes. I tried my hand at doing the event handling vs. the timing loop I had before with no luck.

So this one is a little trickier than the Order Report. Here we're looking for two csv files, and importing them. The PLCDataSend Module sends the imported values to the PLC, when the plc see's that it's found all of the information, it triggers a bit under A13 to delete the two CSV files. Then looks again for the files.

I appreciate any help you can give me

23537

https://youtu.be/BwMrff5awIM