|
Frequently your Excel VBA application may need to display
various messages to the user, especially if you are the user and you are
trying to debug the application.
With VBA, you have three options:
Using numerous MsgBox statements to display messages
Using Debug.Print statements to display messages
Using the Application.StatusBar to display messages
Each of these has disadvantages. For example,
MsgBox alerts require the user to click the OK button to dismiss the
message. Debug.Print statements don't show up on in the Excel window, so you
have to flip back VBA to see the messages, and you can't programmatically
clear the Immediate window. Application.StatusBar only displays one
message at a time, so one a subsequent message is displayed, the previous
message is lost forever.
The Alerter program addresses and fixes all of these shortcoming, and does
even more. Alert is an ActiveX DLL that can be used in any Office
program, versions 97 and later. Quite simply, Alerter displays a
modeless window in your Excel program. Because it is entirely
modeless, it remains visible in Excel as you work on the worksheet and as
your VBA code executes.
Alerter displays the following window in your Excel application window.
This window will float above your Excel window.
Installation Instructions
Download the DLL File in to a folder such as
C:\Windows\System
or C:\Alerter
.
On the Windows Start button, choose Run, and enter the following, including
the quotes as shown, and click OK.
RegSvr32
"C:\Alerter\Alert.dll"
Of course, change the folder path name to the location to which you downloaded
the DLL file.
Usage Instructions
In your VBA Project in the VBA Editor, go to the
Tools menu, and then References. In the list, scroll down to the
"Alert" entry and place a check next to it. This sets a reference
from your VBA Project to the Alert DLL.
Then, declare and initialize the Alerter object.
For example,
Public Alerter As Alert.Alerter
Sub InitAlerter()
If Alerter Is Nothing Then
Set Alerter = New Alert.Alerter
End If
With Alerter
.SetParentWindow -1
.Caption = "Alerts For Testing"
.EnableEvents = True
.FormVisible = True
.MaxListCount = 100
.MaxPriority = 1000
.MinPriority = 100
.ShowCounter = True
End With
End Sub
This will set up and initialize the Alerter object. To write messages
to the Alerter window, use a line of code like the following:
Alerter.DoAlert AlertText:="This
is a test.", Priority:=-1
The Priority argument is optional. See the Properties section below
for more details.
You may right-click on the Alerts list to copy a
message to the clipboard or to clear the list, or hide the window.
The Alerter Object
Properties |
Description |
Caption |
This sets or returns the caption of the
Alerter form. String. Default is "Alert Messages" |
Count |
This returns the number of messages in the
Alerter list. Read Only. Long. |
EnableEvents |
This sets or returns whether the Alerter
should raise events to its container. Boolean. Default is True. |
FormVisible |
This sets or returns whether the Alerter
window is visible. Boolean. Default is True. |
HWnd |
This returns the Windows Handle of the
Alerter window. Read Only. Long. |
LastMessage |
This returns the last message in the Alerter
list. Read Only. String. |
Left |
This returns the Left position of the Alerter
window. Read Only. Single. |
MaxListCount |
This returns or sets the maximum number of
messages to display in the list. When MaxListCount is reached,
the earliest (top) messages are deleted to make room for later
messages. Long. Default is 0, indicating no maximum. |
MaxPriority |
This sets or returns the maximum priority of
messages to be displayed. If a message is sent with a priority
greater than MaxPriority, it will not be displayed. The values you
use for MaxPriorty, MinPriorty, and the Priority are entirely up to
you. Alerter does not have any "default" settings. They
only restrictions are that your priorities must be greater than
equal to zero, and less than 9999999. Beyond that, priorities
are entirely up to you. Long. Default is 9999999. |
MinPriority |
This sets or returns the minimum priority of
messages to be displayed. If a message is sent with a priority
less than MinPriority, it will not be displayed. This must be
greater than or equal to 0. Long. Default is 0. |
ReturnFocus |
This sets or returns whether focus should
return to the host application. Boolean. Default is
True. |
ShowCounter |
Indicates whether the message counter ID
should be displayed in the message text. This is relevant only
on a message by message basis. It does not apply to existing
messages. Boolean. Default is False. |
ShowPriority |
Indicates whether the message priority ID
should be displayed in the message text. This is relevant only
on a message by message basis. It does not apply to existing
messages. Boolean. Default is False. |
Top |
This returns the Top position of the Alerter
window. Read Only. Single. |
Methods |
|
ClearList |
Clears the Alerter list box. Message
texts are permanently lost. |
DeleteSelectedMessages |
Deletes the selected messages. |
DoAlert |
Sends a message to the Alerter list.
This takes two arguments. First,
AlertText
(required String) is the text to display. Second,
Priority
(optional Long). This indicates the priority of the message.
See AlertText
and AlertText
for more details. If omitted, a
value of -1 is used. Depending on the values of the ShowCounter and
ShowPriority properties, the counter and priority values may be
displayed in the list box. |
ResetCounter |
Resets the counter to zero. |
SetLocation |
Positions the location of the upper left
corner of the Alerter window. This takes two arguments: Top
and Left, both required Singles. The current values of the position
are available through the Top and Left properties of the object. |
SetParentWindow |
Sets the parent window of the Alerter window.
If numeric and less than or equal to 0, the foreground window
(typically the Excel application window) is used. If numeric
and greater than zero, this must be the Windows handle of the parent
window. If text, this is the Window Class name of the new
parent window (e.g., "XLMain"). Typically, you should use
call this method with a value of -1, immediately following the
construction of the object. E.g,
Set Alerter = New Alert.Alert
Alerter.SetParentWindow -1
Note that if you are like me, and tend to test code directly from
the VBA Editor window, this will set the parent window to the VBA
Editor, not Excel. Run your init code from Excel, not the VBE. |
Events |
|
AfterAlert |
Raised after an alert has been posted to the
Alerts list. |
AfterClear |
Raised after the Alerts list has been
cleared. |
AfterPriorityChange |
Raised after either the MaxPriority or the
MinPriority is changed. |
BeforeAlert |
Raised before an alert is posted to the
Alerts list. Cancelable. |
BeforeClear |
Raised before the Alerts list is cleared.
Cancelable. |
BeforeCounterReset |
Raised before the counter is reset to zero.
Cancelable. |
BeforeHide |
Raised before the Alerter window is hidden.
Cancelable. |
BeforePriorityChange |
Raised before either the MaxPriority or the
MinPriority is changed. Cancelable. |
BeforeShow |
Raised before the Alerter window is shown.
Cancelable. |
You can download the Alert.DLL file here.
You can download an example workbook
here.
If you are interested in the VB6 source code, send me an
email message,
explaining just why you want it ("just because" isn't good enough), and I
might send you the source. NOTE: There was a bug
in versions prior to 13:20PM (USA Central Time) 12-November-2002, that would
result in an "Invalid Procedure Call" error. Please download the new
version.
NOTE: Alerter would not run on some installations, because of incompatible
versions of the CommonDialog control. This problem was fixed on
15-November-2002. If you had this problem, please download the latest
version. |
|
|