Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hoping that some one here can help me with a data logging spreadshee
that I am trying to set up. Right now I am gathering data from one o our machines that has a Programmable Logic Controller with a networ card in it. I am getting my data, but am having a hard time with m logging and clear log. What happens is, when I press the button "Star Log" my data should be saved in 15 min increments. When I am finishe logging, I want to clear the log and stop it until I am ready to begi agian. I have my macros set up and am currently logging, problem i when I press my "Clear Log" button everything goes crazy. It may lo every few seconds or every minute. Also if someone presses the "Star Log" button more that once this occurs. Hopefully some one will b willing to look at my macro and set me straight. "Start Logging" macro- Sub LoggingFunction() Dim nextCell As Integer If ThisWorkbook.Sheets(3).Cells(1, 1) = "" Then nextCell = 5 Else nextCell = ThisWorkbook.Sheets(3).Cells(1, 1) End If ThisWorkbook.Sheets(2).Cells(nextCell, 1) = Now() ThisWorkbook.Sheets(2).Cells(nextCell, 2) ThisWorkbook.Sheets(1).Cells(3, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 3) ThisWorkbook.Sheets(1).Cells(4, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 4) ThisWorkbook.Sheets(1).Cells(5, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 5) ThisWorkbook.Sheets(1).Cells(6, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 6) ThisWorkbook.Sheets(1).Cells(7, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 7) ThisWorkbook.Sheets(1).Cells(8, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 8) ThisWorkbook.Sheets(1).Cells(9, 2) nextCell = nextCell + 1 ThisWorkbook.Sheets(3).Cells(1, 1) = nextCell Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction" Exit Sub End Sub "Clear Log" macro- Sub ClearLog() Dim i As Integer i = 5 For i = 5 To ThisWorkbook.Sheets(3).Cells(1, 1) ThisWorkbook.Sheets(2).Cells(i, 1) = "" ThisWorkbook.Sheets(2).Cells(i, 2) = "" ThisWorkbook.Sheets(2).Cells(i, 3) = "" ThisWorkbook.Sheets(2).Cells(i, 4) = "" ThisWorkbook.Sheets(2).Cells(i, 5) = "" ThisWorkbook.Sheets(2).Cells(i, 6) = "" ThisWorkbook.Sheets(2).Cells(i, 7) = "" ThisWorkbook.Sheets(2).Cells(i, 8) = "" Next i ThisWorkbook.Sheets(3).Cells(1, 1) = "" Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction" Schedule:=False End Sub Thanks for any help. Dal -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Take a look at Chip Pearson's notes on OnTime at:
http://www.cpearson.com/excel/ontime.htm Pay particular attention to the "Stopping A Timer Process" section. In his example, he uses a public variable named RunWhen to keep track of that exact time (which is needed to stop the routine). And I think I'd add a boolean variable to the "start Log" button routine that checked to see if it's already scheduled. Just keep track if they turned it on and if they did, exit without doing another schedule. With minor modifications to Chip's code: Option Explicit Public RunWhen As Double Public Const cRunIntervalSeconds = 120 ' two minutes Public Const cRunWhat = "The_Sub" Public TimerIsArmed As Boolean Sub StartTimer() If TimerIsArmed Then Exit Sub RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _ schedule:=True TimerIsArmed = True End Sub Sub StopTimer() On Error Resume Next Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=False TimerIsArmed = False End Sub Sub The_Sub() MsgBox "hi from the sub" TimerIsArmed = False StartTimer End Sub "dale1627 <" wrote: Hoping that some one here can help me with a data logging spreadsheet that I am trying to set up. Right now I am gathering data from one of our machines that has a Programmable Logic Controller with a network card in it. I am getting my data, but am having a hard time with my logging and clear log. What happens is, when I press the button "Start Log" my data should be saved in 15 min increments. When I am finished logging, I want to clear the log and stop it until I am ready to begin agian. I have my macros set up and am currently logging, problem is when I press my "Clear Log" button everything goes crazy. It may log every few seconds or every minute. Also if someone presses the "Start Log" button more that once this occurs. Hopefully some one will be willing to look at my macro and set me straight. "Start Logging" macro- Sub LoggingFunction() Dim nextCell As Integer If ThisWorkbook.Sheets(3).Cells(1, 1) = "" Then nextCell = 5 Else nextCell = ThisWorkbook.Sheets(3).Cells(1, 1) End If ThisWorkbook.Sheets(2).Cells(nextCell, 1) = Now() ThisWorkbook.Sheets(2).Cells(nextCell, 2) = ThisWorkbook.Sheets(1).Cells(3, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 3) = ThisWorkbook.Sheets(1).Cells(4, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 4) = ThisWorkbook.Sheets(1).Cells(5, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 5) = ThisWorkbook.Sheets(1).Cells(6, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 6) = ThisWorkbook.Sheets(1).Cells(7, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 7) = ThisWorkbook.Sheets(1).Cells(8, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 8) = ThisWorkbook.Sheets(1).Cells(9, 2) nextCell = nextCell + 1 ThisWorkbook.Sheets(3).Cells(1, 1) = nextCell Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction" Exit Sub End Sub "Clear Log" macro- Sub ClearLog() Dim i As Integer i = 5 For i = 5 To ThisWorkbook.Sheets(3).Cells(1, 1) ThisWorkbook.Sheets(2).Cells(i, 1) = "" ThisWorkbook.Sheets(2).Cells(i, 2) = "" ThisWorkbook.Sheets(2).Cells(i, 3) = "" ThisWorkbook.Sheets(2).Cells(i, 4) = "" ThisWorkbook.Sheets(2).Cells(i, 5) = "" ThisWorkbook.Sheets(2).Cells(i, 6) = "" ThisWorkbook.Sheets(2).Cells(i, 7) = "" ThisWorkbook.Sheets(2).Cells(i, 8) = "" Next i ThisWorkbook.Sheets(3).Cells(1, 1) = "" Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction", Schedule:=False End Sub Thanks for any help. Dale --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
How to end macro on inital active worksheet containing macro button that was clicked | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |