Using a Global String Variable to Transfer Timer Event Data From API to Main Pgm

I apologize up front that the scope of this help request is larger than normal but, at least, I have stated the objective in simple programmatic terms.

I have spent the last year developing an investment solution software package that has only one hurdle to cross to be completed. It involves the use of the code listed below.

The GOALis to populate sTimerResult on line 29 so it can feed into the Select Case in lines 100 - 117. Programmatically, the problem is sTimerResult keeps coming back as a null and the program terminates at line 113.

My platform is 64 bit, Windows 7 Ultimate. It appears that the timer is working by the positive result on line 73. My objective is to apply the code to

HTML Code: 

. For example, when I go out to poll the DDE data interface from a third party, the data may already be available so I won't need the timer and can bypass it; but on the other hand if the data is not there yet, then I will need some mechanism to wait for it and then snag it with out misrepresenting (too badly) the actual time it was created. The code you see represents excerpts from the program. For reasons of confidentiality, it is not contiguous. I am polling 1-minute data.

Please Login or Register  to view this content.

Unless I misread your code,

To me it looks as if you are reinventing Excel's builtin Querytables.

In a class module you can check the _afterupdate event of the querytable, to keep you informed whether new data have been loaded or not.
An alternative could be, dependent of the loaded data' to check the change of the value of a certain cell using an application.timer.
I might be wrong but I think it could be done much simpler that the way you chose...

snb,

You always have refreshing and out-of-the-box solutions which gives me hope that there is a simpler way. I may be asking you, on a contract basis, to have you write what I need, if you are interested.
In the meantime others, outside of this forum, have offered solutions that are not as creative as yours but satisfy the tight schedule I am up against.
As this thread continues I will share those solutions.
For sure, your ideas would seem to provide for a more effective design.

Excel is full of surprises in that not only are there many different ways to solve a problem but there are shorter more efficient ways to get the job done.
In the API approach I was using, there were over 100 lines of code used to establish a millisecond timer. Well, the following 6 lines of code turned out to be a powerhouse and actually worked. They were offered to me by Jacob Hilderbrand.

Please Login or Register  to view this content.

But you won't even need a timer....