A timer to check external variable

[mtheriault2000]

Hello

I would like to use a timer to read external value every 100 millisecond, how can i do that

The reading sub routine to be trigger when timer reach is limit

Any help appreciated

Martin

 

[Andrew Poulsom]

Maybe this will help you:

http://www.cpearson.com/Excel/OnTime.aspx

but pay attention to the "NOTE OF CAUTION".

 

[Greg Truby]

Wow - that's a pretty sophisticated request because the resolution on the default method one uses to kick off timer-based actions is ONTIME which has a resolution of one second.

There might be some type of Win API function that'd do the trick. Otherwise, you'd have to create a loop that used something like an infinite loop and the QueryPerformanceCounter Win API function. This rather inelegant solution would pin the needle on your CPU essentially tying up the computer and making it dedicated to this one task.

<SUP>EDIT</SUP> Ah, good to know Andrew. I shoulda guessed that Chip would have something on the subject. Looks like the Win API function one needs is SETTIMER, which has a resolution of one millisecond. Here's an MS KB article on the SetTimer function. http://support.microsoft.com/kb/180736 <SUB>/EDIT</SUB>

 

[Greg Truby]

Andrew, per Chip's note of caution, I do not know of any way within VBA to test to see if the user is in edit mode - something that Martin would need to address. Are you aware of a way to do this? [Martin - about the only, again inelegant, workaround that I can see would be to use protection to lock out the sheet and run a check on the worksheet's protectcontect property prior to running the code. If it's protected then the user cannot be in edit mode.]

 

[mtheriault2000]

Thanks Greg / Andrew

I Will read that

Martin

 

[mtheriault2000]

The note of caution is indeed tricky

A NOTE OF CAUTION: If the code executed by the timer changes a cell value, and you are presently in edit mode in Excel (e.g., entering data in a cell), Excel will likely crash completely and you will lose all unsaved work. Use Windows timers with caution.

As i may, in anytime, be doing modification to a cell. That will need a good reflection from me.

Martin

 

[Andrew Poulsom]

Andrew, per Chip's note of caution, I do not know of any way within VBA to test to see if the user is in edit mode - something that Martin would need to address. Are you aware of a way to do this?

It's not straightforward:

http://www.codeproject.com/KB/office/Excel_Edit_Mode.aspx

 

[mtheriault2000]

Andrew, per Chip's note of caution, I do not know of any way within VBA to test to see if the user is in edit mode - something that Martin would need to address. Are you aware of a way to do this? [Martin - about the only, again inelegant, workaround that I can see would be to use protection to lock out the sheet and run a check on the worksheet's protectcontect property prior to running the code. If it's protected then the user cannot be in edit mode.]

Using Protect, unProtect, that could be a possibility.

My goal here is to read from a GlobalVariable.dll, High, Low live quotes Price of a Traded instrument (Forex). These info are coming from an external application. As I don't want to read in a infinite loop, I thought that it would be wiser to read the values in a timed based routine.

And, it seems that there is no freeway to do it

 

[Greg Truby]

Thanks, Andrew. I'd been doing some searching myself. And the only solutions I saw mentioned that looked feasible were based on the same idea - testing one of the built-in menu control's enabled property as a backdoor means of sussing it out. Appreciate your confirmation that there's not some slicker way I didn't know about.

 

[Jaafar Tribak]

If the code executed by the timer changes a cell value, and you are presently in edit mode in Excel a Runtime error occurs which you can easily manage with a simple error trapping routine. Just placing an On Error Resume Next at the start of the Timer Function will avoid crashing the application. Don't know why C.Pearson didn't mention this.