MtBackgroundGui.xla

Next we examine the user interface add-in, MtBackgroundGui.xla. This is an essential part of the application, and provides the following functionality:

Menu and/or toolbar to let the user turn on and off all asynchronous updates.
Dialog to allow the user to manage performance criteria.
Instantiation and initialization of a XlpAsync.PushHandler COM object, which manages the communication between Excel and the add-in, ensuring that there are no timing conflicts.

modCommands module

Open the add-in, and switch to the VBA editor (Alt+F11 in Excel 97).

Open the module modCommands. You can find this in the Project Explorer window:

First, look at the declarations section.

' Name of menu bar
Global Const XLA_MENU = "&MtBackground"

' File containing XLL
Global Const XLL_NAME = "MtBackground.xll"

' Path to be searched for XLL
' It can contain multiple paths separated by ';'
' e.g. "C:\AddIns;G:\SharedAddins"
Global Const XLL_PATH = ".;Debug"
                                            
' Message displayed in error message boxes                                            '
Global Const SUPPORT_MSG = "Contact support at <put your details here>"

These constants will be used when the XLA opens, to control the following:

Constant	Purpose
XLA_MENU	This is the caption of the new menu that will be added to the Excel main menu bar.
XLL_NAME	The name of the XLL file which contains the asynchronous add-in functions.
XLL_PATH	If the XLL is not already open when the XLA is opened, then the XLA will attempt to open it. It will search this path for instances of MtBackground.xll. The path may contain multiple directories, separated by semi-colons. If a directory is relative (i.e. does not contain a colon or a double back-slash) then it will be assumed to be relative to the location of the XLA. The default settings, ".;Debug", are convenient both for development and for distribution. Just ensure that the XLL and the XLA are in the same directory when you distribute your application to users, and everything will be fine. In a development environment, the debug version will be found in teh Debug sub-directory and will be loaded instead.
SUPPORT_MSG	In a live application, this should contain support information. It will appear in any error dialogs.

Next, inspect the SetupMenu() function. This contains code to create and delete the application's menu bar. You can add or remove paragraphs like those shown below to modify the menu. In each case, you should implement the corresponding Public Sub in the module.

Further discussion of the VBA coding used in this module is beyond the scope of this document.

Sub SetupMenu(ByVal bCreate As Boolean)
    ...
    
    ' Add menu items
    Set cmd = barThis.Controls.Add(msoControlButton)
    cmd.Caption = "&Paused"
    cmd.OnAction = "TogglePause"
    Set g_cmdPause = cmd    ' Used by TogglePause() to toggle State
    
    Set cmd = barThis.Controls.Add(msoControlButton)
    cmd.Caption = "&Refresh"
    cmd.OnAction = "RefreshNow"
    
    Set cmd = barThis.Controls.Add(msoControlButton)
    cmd.Caption = "&Connections..."
    cmd.OnAction = "ShowConnections"
    
    Set cmd = barThis.Controls.Add(msoControlButton)
    cmd.Caption = "&Settings..."
    cmd.OnAction = "ShowSettings"

    ...     
End Sub

ThisWorkbook

Next, have a look at the code module for ThisWorkbook. The add-in has been designed so that changes are very rarely required in this module: it is intended that almost all changes to the template can be done in modCommands.bas.

However, you may need to open this module frequently during development. Every time you change code in any module, the variables held in memory by Excel's VBA engine are thrown away and become invalid. So, every time you change some code, you should run the Sub OnOpen() in order to reinitialize the connections between Excel, the XLA and the XLL.

You can do this most easily by placing the cursor somewhere in the body of OnOpen() and pressing the F5 key.

The module also contains the line:

Public WithEvents m_async As XLPASYNCLib.PushHandler

This object handles much of the communication between Excel and the XLL and prevents timing conflicts.

Forms

The form ufConnections displays all current connections between topics and cells. It is a fairly useful tool for monitoring performance and behaviour. The form is displayed when the user clicks the Connections... menu item.

The form ufSettings allows the user to control various run-time parameters of the push engine, and also contains code to set the background thread's tick count.

Let us look at the Populate() function, which is called when the form is opened.

Public Sub Populate()
    On Error Resume Next
    txtRefreshPeriod.Text = CStr(g_async.GetParam("RefreshPeriod"))
    txtTickPeriod.Text = CStr(g_async.GetParam("TickPeriod"))
    chkFormatChangedCells = CBool(g_async.GetParam("FormatChangedCells"))
    chkPaused = g_async.Paused
    txtUpdatePeriod.Text = Application.ExecuteExcel4Macro("MtBackgroundGetPeriod()")
End Sub

The most interesting line here is the last. To call an XLL add-in function from VBA, we use the method Application.ExecuteExcel4Macro(). This returns the current value of the tick period, which is placed in a text field.

The cmdOK_Click() method is called when the OK button is clicked.

Private Sub cmdOK_Click()
    On Error GoTo ErrorHandler
    Call g_async.SetParam("RefreshPeriod", txtRefreshPeriod.Text)
    Call g_async.SetParam("TickPeriod", txtTickPeriod.Text)
    Call g_async.SetParam("FormatChangedCells", chkFormatChangedCells.Value)
    g_async.Paused = chkPaused
    Call Application.ExecuteExcel4Macro("MtBackgroundSetPeriod(" & txtUpdatePeriod.Text & ")")
    Hide
    SetMenuState
    Exit Sub
ErrorHandler:
    MsgBox Err.Description, vbExclamation + vbOK, "Input error"
    Err.Clear
    Populate
    Exit Sub
End Sub

Here we construct the Excel macro text as follows:

"MtBackgroundSetPeriod(" & txtUpdatePeriod.Text & ")"

and pass the command to the XLL via the Application.ExecuteExcel4Macro() method.

Next: MtFeed sample >>