Get values every 4 minutes

[pippinsmckey]

Hey guys,

I'm fairlly new to VBA programming. I want an updating field to be copied into the next empty column every 4 minutes and was wondering if anyone had an idea to do so, with the least amount of processing power to do so. At the same time, I want a time stamp to be inserted above the column.

At first I used "end.Xl" and copy/paste-special, but that was quite consuming of data power.

Preferably it should go to one worksheet to another, without automatic screen updates and such. This is the code I've come up with so far:

<code>Option Explicit 
Dim vWhen As Variant 
Public col As Integer    
Sub MD()     Dim LC As Double      
Application.ScreenUpdating = False     
Dim workbook1 As Workbook     Set workbook1 = Workbooks("Final.xlsm")     
workbook1.Activate     Dim ws1, ws2 As Worksheet     
Set ws1 = Sheets("ICAP")     Set ws2 = Sheets("Data")     
Dim i As Long     i = 4     
ws1.Cells(3, col) = Now()         
ws1.Cells(i, col) = ws2.Cells(i, 4)         
i = i + 1     col = col + 1     
ws1.Cells(1, 5) = col          
Application.ScreenUpdating = True

</code>
End Sub

<code>Sub Morning() Application.OnTime TimeValue("08:30:00"), "MD", TimeValue("17:00:00"), TimeValue("00:04:00") End Sub</code>

pipinsmckey

 

[mole999]

be aware that your four minutes will probably wander, I used to run a process and over a few days the system clock drifted as it wasn't being updated from a time server, and also the excel meant the machine couldn't be used for other tasks

 

[pippinsmckey]

Thanks for advising me mole999. I don't think that will be a huge problem since I always turn off my computer at circa 17.30 and start it the next day at 07.00 or so, so it will never run 24/7.

Can you please look at the Application.OnTime code and see if there's any mistakes in there? Can't simply get it to work, get errors et cetera. I seem to follow the instructions (and it looks to be in the right format), but am I missing something?

Kind regards,
pippinsmckey

 

[mole999]

I used a fudge for mine in the past

this seems to be as simple as possible Application.OnTime Method (Excel)

must make sure that the timer is actually turned off when you finish, run from workbook before close event

 

[JoeMo]

Can you please look at the Application.OnTime code and see if there's any mistakes in there? Can't simply get it to work, get errors et cetera. I seem to follow the instructions (and it looks to be in the right format), but am I missing something?

Kind regards,
pippinsmckey

The fourth argument (Schedule) of Application.OnTime is boolean - so must be either True or False.

Try changing this:<code>
Application.OnTime TimeValue("08:30:00"), "MD", TimeValue("17:00:00"), TimeValue("00:04:00")</code>

to this:

<code>Application.OnTime TimeValue("08:30:00"), "MD", TimeValue("17:00:00"), True</code></pre>

 

[pippinsmckey]

mole999: Yeah, I tried doing them all three separately but could not get it to end. I obviously and most likely did something wrong. That's when I saw that you could include all of them in the same section/code, and it looked fairly simple, so that's why I went for it that way.

JoeMo: Yes, but that does not run the procedure each 4 minutes, does it? Should I just add that to the end? Should there be a false in there as well or is that taken for granted, that if it is not in between 8:30 and 17:00 it is per automation false?

 

[mole999]

i think you need the remove the speech marks from MD as you are calling a routine

 

[pippinsmckey]

I will have a look at it and try to run it first thing in the morning (since I don't get the automatically updated data from this computer).

I'll return with the results and such!

Many thanks for the help so far, unfortunately I might need more in the soon to be future.

Kind regards,
pippinsmckey

 

[JoeMo]

JoeMo: Yes, but that does not run the procedure each 4 minutes, does it? Should I just add that to the end? Should there be a false in there as well or is that taken for granted, that if it is not in between 8:30 and 17:00 it is per automation false?

Take a look at this thread for a procedure that runs every 5 minutes which you can easily adapt.

http://www.mrexcel.com/forum/excel-questions/797687-autosave-every-5-minutes.html#post3901388

 

[pippinsmckey]

Yeah, I've actually had a look on a similar one but that one does not do it within a certain time interval (8.30 AM to 5 PM), and that is where it becomes tricky for a simpleton as me. To run it on startup I know how to, but I want it to not go live until 8.30 as specified...