Pearson Software Consulting Services

    Alerter    

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.

        

 

 

 

 Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

Copyright 1997-2007  Charles H. Pearson