A
atpgroups
I have a large macro (pretty much a complete application, actually)
running in excel VBA. One function is user-defined limits-monitoring
with a programmable recurrence.
This is done with a collection of items of a class which fires off a
Win32 API timer as defined by this function
Public Declare Function SetTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
Every couple of seconds or so this timer calls a bit of code in one of
my code modules.
Ideally it would call a bit of code in the instance of the class that
created the timer, but unfortunately a limit of the API call is that
it can only call back to static modules (which makes some sense, to be
fair).
Ideally I want the code called by the windows API call to do its thing
and quit as quickly as possible. I certainly can't afford to have
calls to the same bit of code allowed to stack up, that sounds like a
very bad thing.
So, I was hoping to have the timer-handler pass the API event handle
to my main code (which is running all the time) and then quit. I
assume I could do this by having it set the value of a global
variable, and have my main code watch that. However I rather want
handling these timer events to take priority over my main code, and I
want to avoid the situation where the main code is running a routine
which isn't looking at my global flag (say it is waiting for user
input, for example)
Questions..
1) Am I right in assuming that the VBA code called by the Win32 call
is running in a seperate thread to my main VBA routines? I don't so
much mean in the sense of concurrent running as much as having its own
call stack that needs to be cleared before the next time it is
called.
2) Can I hand control off to my main VBA code using user-defined
events? I have created a simple EventRaiser class with a "Trigger"
method which raises an event which is trapped by code in the main
WorkBook object (As only object modules can contain event-handling
code). However single-stepping through with F8 or tracking with debug-
print seems to indicate that RaiseEvent simply calls the event code.
ie, the code after RaiseEvent doesn't run until the event handler code
has completed.
Is this the over-complicated mess that it feels like? What I am really
after is a neat way of having something akin to interrups in a VBA
application.
running in excel VBA. One function is user-defined limits-monitoring
with a programmable recurrence.
This is done with a collection of items of a class which fires off a
Win32 API timer as defined by this function
Public Declare Function SetTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
Every couple of seconds or so this timer calls a bit of code in one of
my code modules.
Ideally it would call a bit of code in the instance of the class that
created the timer, but unfortunately a limit of the API call is that
it can only call back to static modules (which makes some sense, to be
fair).
Ideally I want the code called by the windows API call to do its thing
and quit as quickly as possible. I certainly can't afford to have
calls to the same bit of code allowed to stack up, that sounds like a
very bad thing.
So, I was hoping to have the timer-handler pass the API event handle
to my main code (which is running all the time) and then quit. I
assume I could do this by having it set the value of a global
variable, and have my main code watch that. However I rather want
handling these timer events to take priority over my main code, and I
want to avoid the situation where the main code is running a routine
which isn't looking at my global flag (say it is waiting for user
input, for example)
Questions..
1) Am I right in assuming that the VBA code called by the Win32 call
is running in a seperate thread to my main VBA routines? I don't so
much mean in the sense of concurrent running as much as having its own
call stack that needs to be cleared before the next time it is
called.
2) Can I hand control off to my main VBA code using user-defined
events? I have created a simple EventRaiser class with a "Trigger"
method which raises an event which is trapped by code in the main
WorkBook object (As only object modules can contain event-handling
code). However single-stepping through with F8 or tracking with debug-
print seems to indicate that RaiseEvent simply calls the event code.
ie, the code after RaiseEvent doesn't run until the event handler code
has completed.
Is this the over-complicated mess that it feels like? What I am really
after is a neat way of having something akin to interrups in a VBA
application.