Excel freezing when using .ontimer event

I am using an .ontimer command to trigger a call to a sub every 6 seconds. However it seems that this event is very unreliable -Excel freezes up -- intermittently -- so I have been reading up on using the Windows API command SLEEP ---- if I understand that function correctly -- it simply pauses that thread --- so does that mean other threads within that workbook code gets time to finish --- before the thread where the SLEEP commands resides will continue. So I guess my questions is that if I want to use the SLEEP command --- I can then have a loop that runs for a very long time -- and inside the loop --- I use the SLEEP command to pause that piece of code for 6 seconds --- when it wakes up -- I then call the sub again. In other words is the SLEEP command used in this way less prone to freeze up excel than the .ontimer function.

I hope this makes sense.

OK -- got it sorted --- The SLEEP --- I use to pause a thread --- however it does mean that all code in XL is not being executed --- so dont use SLEEP as a timer --- However I am reading CSV file every 6 seconds -- so sometimes I have a sharing conflict -- before I open any new CSV file(s) I execute SLEEP for 500 milliseconds --- this allow the 3rd party application to finish saving the data to the CSV file before I try to read it --- After I have read and parsed the CSV file -- I kill it -- again I am using a SLEEP of 500 MS --- else the code may get ahead of itself and cannot kill the file as the other sub has not closed it yet --- strange but true. So that is how I use the SLEEP command. Next I wrote my own timer --- it contains the DoEvents statement --- so what this does it releases the code and hands control to Windows. So that works very well to 'distract' xl2007 ----- and temporaly stop executing xl code. I am writting to a cell what procedure is being processed as I could not use the debugger as excel froze up.


Anyway -- just wanted to share in case anybody else is having trouble using a timer in combination with I/O operations.

The TimeOnOFF can be toggled using code to be True/False -- It is dimmed as a Public Boolean variable

Code:

---

Public Sub TimerXX
  Application.StatusBar = "Timer On"   
  SS = Second(Now) + DelayTimeSeconds
  While TimeOnOFF = True
        If Second(Now) > SS Then
            Call ReadDataFromAnalyzer
            SS = Second(Now) + DelayTimeSeconds
        End If
        DoEvents
    Wend
    Application.StatusBar = "DataPickUp = Off"
End Sub

---

Rasm,

So you set the timer for 1/2 second and during that time you keep Windows occupied on other things. Then when the timer times out you read the data from the Analyzer, signal that the data packet has been received and reset the timer to grab the next data packet.

1) If you were to measure the amount of time it takes for the packet to be successfully imported, then you could programmatically set the delay time to remove any deadtime when nothing is getting done.
2) I have found that data feeds don't work very well unless your code actually stops running. For example, if you decided to use a do loop nothing would get done because the CPUs won't allow any other process to run, including data feeds. The reason for this is that many data feeds are not designed to be parallel in nature.
3) Your solution is particularly interesting in that you have stopped the code and through the use of DoEvents you have allowed the data feed to update, doesn't work with all feeds though.
4) The other approach is to create a query data table that is linked to your feed database. No code or csv files would be necessary in this scenario. The data would just show up.

Thank you for your feedback.

Steve

Steve

My CSV files come from an analyzer -- and it comes as flat ASCII files --- so I appreciate your comments about using a table -- but not applicable here --- I dont think. My timer is actually set to 6 seconds --- so evey 6 seconds I go look for ASCII files -- what I found was that if the device had been running --- But xl 2007 not open -- I would have hundreds of ASCII files to process. The way my code works is as follows

0) Creates an array all ASCII file names
1) Opens and reads ASCII file content into a dynamic array --- using the split command - one file at a time
2) Closes the file
3) Places the file content into my worksheet
4) Kills the ASCII file - I process files one at atime -- to protect from data loss.
6) If NOT all files processed -- I repeat from step 1

What happens is the I get an error when I try to Kill the file --- however if I use the Sleep 500 before I execute the Kill command -- the code has time to execute (This only happens when I have a large number of files to process) --- I think it is some sort of file sharing violation -- but I dont know for sure. The 500 MS may be a long pause --- but I think the time depends on the CPU speed and how fast the hard disk is accessed --- so since I only get new data every 6 seconds -- I have time to spare (except when large number files). I also keep track of when the workbook.save was executed -- as I have a variable that allows the user to autosave the workbook every so many minuttes. Since I add a new row to the worksheet every 6 seconds -- the worksheet rapidly become large.

Yes -- The DoEvents command appears to stop xl2007 from freezing ---- however all my xl2007 icons are greyed out --- even the office button -- but when I stop my timer it become active again.

So I am puzzled --- but it is a bit odd and I am not really sure why it all works.

Your feed back is most appreciated as I would like to understand my code - lol

I now use -- my own manual timer --- DoEvents & the Windows API Sleep ------ so really crazy.

Code:

---


Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Somecode
  Sleep 500
End Sub

---

Rasm,

I was just trying to learn from you and it appears that you are approaching the coding correctly. In my similar situation I have live data being collected from a stock market feed. My query table is hook on one end to the incoming data from the stock market in one instance of excel and the other end of the table is located in my number crunching machine in another instance of excel. The number cruncher simply does a refresh every 1 second and promulgates this data to the rest of the program with a simple task of determining whether we should BUY or SELL.
I had to purchace a workstation with 16 cores in order to get enough horsepower to make a trade decision within a 2 second window.
Anyway, it was pleasure understanding what you are doing as it has helped me in my approach.

Steve

You better use the querytable_afterrefresh event.