Mr. Script

Setting the Timer

Mr. Script explores integrating a Web-based order system with electronic software deliver.

• By Chris Brooke
• 10/01/2005

Last month we created a basic VBA application form in Excel that we'll use as a front-end to our "integration application." Before we jump into performing the different tasks we need for the integration, let's step back and look at the big picture of what we're trying to automate.

Remember that we're attempting to integrate a Web-based order system with electronic software delivery and CD burning/shipping (when required).

While we could simply put buttons on our Excel form to manually initiate each task, this is a bit too low-tech for me. I would much rather create an event-based system where each order automatically triggers an "action list," which is placed into a queue. Then we can have our main application simply sit back and watch the queue. In addition to reducing the amount of manual intervention required, this also has the advantage of allowing multiple requests to simply "stack up" while the system is processing other orders. Imagine the frustration if you had to wait until previous orders were completed before entering new ones!

Get in Line
The queue in this case is simply a folder on a network share that's accessible to all users of the order system. Each time an order is placed, a file is written to this folder. Our application retrieves the order detail files, processes it, and deletes it from the queue. Lather, rinse, repeat, until there are no items left to process.

By taking this approach, we can separate the tasks logically. The automation server application (our main Excel VBA app) runs on a specific computer—namely, the computer that has the CD burner attached. Our customer service representatives run a separate application that allows them to add jobs to the queue (at least until we get this thing completely integrated into our existing applications so that it all happens automatically). This will also allow users to independently schedule tasks that aren't triggered by the order system. For example, part of this process involves logging on to the shipping provider's Web site and printing a shipping label. By isolating this function, we can provide users with a quick and automated way of shipping something (or anything) besides a customer order.

A Tweak Here and There
Let's prepare our automation server application by making a few changes to the form we designed last month. Open the Excel spreadsheet you created last month. Click on Tools | Macro | Visual Basic Editor and double-click frmMain to open the design view of your form. Now add text fields, for Order ID, e-mail and so on, as well as a label field and checkbox.

When it comes to the actual code, where we put the different functions depends on what they'll do. The more immediate code will go in the code section of the form's event. Code that we're likely to reuse will go in the module. For example, clicking the "Create a CD for this order …" button will cause code to run that will likely call subroutines from our module1. You separate functionality in this manner to allow for the addition of more forms later. By putting oft-used code in the module, you can make it available application-wide, rather than just form-wide.

Hurry Up and Wait
The first thing you need to do is set up the automation server application to wait for something to do. Since you're using VBA in Excel, you have access to Windows libraries you can't access through VBScript. One such library is User32 (User32.dll), which contains a timer function you'll use to ensure that each queued job completes (one way or another) before the next job is started. This is referred to as a semaphore. You'll set a timer to fire off every 10 seconds. The first thing it does is check to see if a job is running. If so, it resets itself and goes back to sleep. If not, it executes the next task in the queue. In order to use the timer functions of User32, you must first declare it in your module.

Private Declare Function SetTimer Lib "user32" _
(ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) _
As Long
Private Declare Function KillTimer Lib "user32" _
(ByVal HWnd As Long, ByVal
nIDEvent As Long) _
As Long

For most of you, this is a completely new concept, because you can't point to external functions in VBScript. However, if you think way back to my series on Windows Script Components, this will indeed look familiar (from declaring properties of WSCs). For now, though, just think of it similarly to a CreateObject statement. You're simply declaring your intention to use this object and setting aside the resources (more or less).

You also need to create three global variables you'll use with the timer:

Public bProcessing As Long
'Processing? True/False
Public TimerID As Long 'Used to invoke SetTimer
Public TimerSeconds As Single 'Time remaining

Now all you need to do is start the timer. To ensure that the timer is always running, start the timer when the form is loaded. Add this to the form's code:

Private Sub UserForm_Initialize()
Call StartTimer
End Sub

And add this to the module code:

Sub StartTimer()
'If there isn't a timer set, set it
If TimerID = 0 Then
TimerSeconds = 10 'how often to "pop" the timer.
TimerID = SetTimer _
(0&, 0&, TimerSeconds * 1000&, _
AddressOf TimerProc)
End If
End Sub
Public Sub TimerProc _
(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
'The procedure is called by Windows.
'Put your timer-related code here.
If bProcessing = 0 Then Call
CheckQueue
End Sub

Stay With Me …
The UserForm_Initialize sub is called as soon as the form is loaded (and you already added the Auto_Open sub to load the form when the spreadsheet is opened). This in turn calls the StartTimer sub. StartTimer then calls into the User32 library and executes the SetTimer function with a string of arguments. We'll discuss HWnd handles and ID Events later. For now, the arguments to take note of are the time (in milliseconds, which is why we multiply 10 by 1,000) and the AddressOf TimerProc. This is the important one, as you're passing the address of the TimerProc sub.

So now, after the timer counts down 10 seconds, it fires the timer event and calls the TimerProc sub. Put the code you want to execute each timer cycle in this sub; this is where the check is made to see if the app is busy. If bProcessing is set to 'True', simply exit and check again at the next timer cycle. If bProcessing is false, then it's safe to execute some code, which in this case involves checking the queue for any pending jobs.

We'll explore that next month. For now, though, you can take the concept of the semaphore into other tasks—any task, really, that may require this kind of exclusive, or modal, behavior.