Archive for October, 2010

Timed Updater Add-In

The Challenge…

I got a request to create an Excel 2007 Add-In, using standard VBA (i.e. not VSTO), that would update market prices of assets listed in an Excel workbook at timed intervals without requiring any structural modifications to the portfolio workbook, and without requiring any VBA code in the portfolio workbook.

A typical portfolio worksheet contains many, many columns of data, but the only column of interest to the new Add-In is column E, the “Market Price”.  First step is to create a workbook for testing that only contains a subset of the information (see below).

Reduced portfolio for testing purposes

Test Portfolio

Now that we have test data to refer to, let’s create the Add-In.

The Solution…

  • Create a new workbook and open the VBA Editor (Alt-F11).
  • Right-click on the project name (usually “VBAProject”) and select VBAProject Properties.  Use the dialog box to change the Project Name to “TimedUpdater” and change the Project Description to “Execute a function repeatedly at timed intervals”.  Click OK to store these changes.
  • In the interest of keeping this as simple as possible, we’re going to use Excel’s built-in Application.OnTime method as our timer.  We could create a more complex timer with granularity down to the millisecond, but that is not needed here, so let’s create a simple class module to expose Timer functionality.  Insert a new Class Module into the project, name it “CTimer”, and add the following constants and module-level variables:
Option Explicit
Private Const TIMER_INTERVAL = "00:00:03" 'Default Interval = 3 secs 
Private Const TIMED_FUNCTION = "TimedFunction" 'Function called at each Interval 
Private mstrTimerInterval As String 
Private mdatSchedTime As Date
  • Next, add the following public properties to the class module:
Public Property Let TimerInterval(ByVal NewInterval As String) 
 mstrTimerInterval = NewInterval 
End Property
Public Property Get TimerInterval() As String 
 TimerInterval = mstrTimerInterval 
End Property
Public Property Get DefaultTimerInterval() As String 
 DefaultTimerInterval = TIMER_INTERVAL 
End Property 
  • The last task for this module is to create the functions that will control the starting and stopping of the timer:
Sub StartTimer() 
 mdatSchedTime = Now + TimeValue(TIMER_INTERVAL) 
 Application.OnTime mdatSchedTime, TIMED_FUNCTION, , True 
End Sub
Sub StopTimer() 
 Application.OnTime mdatSchedTime, TIMED_FUNCTION, , False 
End Sub

We now have a timer class that can be started and stopped, and has the ability to periodically trigger the execution of the function specified in the global constant TIMED_FUNCTION.  It’s time to save the project, but before you save, click on the “ThisWorkbook” object in the project explorer and look at the properties that appear in the properties list.  Locate the property called “IsAddIn” and set it to True.  Now save the project in a separate testing folder, ensuring that it is saved as an “Excel Add-In (*.xlam)”, and name it “MDTimedUpdater.xlam”.  Next, let’s define that function and create some other global objects and functions required to make this Add-In work.

  • Insert a new standard module and name it “modGlobal”.
  • Add the following variable declarations to the module (we’ll be creating the “CDataAccess” class shortly; for now just add the variable declaration exactly as listed below):
Option Explicit
Public gclsTimer As CTimer 
Private mclsData As CDataAccess
  • Create a generic error reporting routine that we can use to report trapped errors throughout the project:
Public Sub ShowErrorMessages(ByVal StandardErrorObject As VBA.ErrObject, _   
 ByVal SourceModule As String, ByVal SourceMethod As String) 
 'Construct a comprehensive error message based on the passed objects. 
 Dim strMsg As String
 'Handle the standard error, if any 
 If StandardErrorObject.Number <> 0 Then 
 strMsg = "Error: " & CStr(Err.Number) & vbCrLf & "Description: " _ 
 & Err.Description & vbCrLf & vbCrLf 
 End If
 'Finally, tack on the module/method names 
 strMsg = strMsg & SourceModule & "::" & SourceMethod & vbCrLf 
 MsgBox strMsg, vbCritical, "Contact Technical Support for Assistance" 
End Sub
  • Add a new public subroutine to modGlobal, called “InitializeTimer”.  This subroutine will be called when the Add-In is first attached and can also be called by external routines:
Public Sub InitializeTimer() 
 Set gclsTimer = New CTimer 
 With gclsTimer 
 If Trim$(.TimerInterval) = "" Then   
 'If not yet set, use default interval value 
 .TimerInterval = .DefaultTimerInterval 
 End If
 End With 
End Sub
  • Next, another new public subroutine to modGlobal, called “CloseTimer”.  This subroutine will be used to stop the timer from executing and can also be called by external routines.
Public Sub CloseTimer() 
 If Not gclsTimer Is Nothing Then 
 Set gclsTimer = Nothing 
 End If 
End Sub
  • We will be returning to modGlobal later on, to create the function that will be triggered at each interval, but first let’s create some other necessary components.  A real portfolio price-updating application would have to reach out to some database, web service, external file, etc., in order to retrieve actual pricing data.  For purposes of this demonstration, we are simply going to create a dummy data access class.  So, insert a new Class Module into the project, name it CDataAccess and add one function, called “RefreshPortfolioPrices”:
Public Function RefreshPortfolioPrices(ByRef arrPrices As Variant) As Boolean 
Dim blnReturn As Boolean 
Dim intItem As Integer
blnReturn = True 
On Error GoTo RPPError 
'Just fill the array with dummy data for testing. 
'@@@ TO DO:  For the real application, the call to update the 
' data (from database, internet, or any other source) 
'  would be coded here. 
ReDim arrPrices(0 To 20)  
For intItem = 0 To UBound(arrPrices) 
 Randomize arrPrices(intItem) = Rnd(100) 'Add randomly generated price to array
Next intItem
 RefreshPortfolioPrices = blnReturn 
 Exit Function
 blnReturn = False 
 ShowErrorMessages Err, "CDataAccess", "RefreshPortfolioPrices" 
 Resume RPPResume 
End Function

This function simply populates the passed array with randomly generated prices between 1 and 100.  As the code comments say, this is where a real application would reach out to a data source to set actual prices.  This module does not need any other code, so save the project and close it.

  • Let’s return back to modGlobal to add the final function required there.  Create a new function called “TimedFunction”.  This function’s purpose is to create a new instance of the CDataAccess class, if necessary, and to retrieve the updated prices from it, and finally to actually update the portfolio worksheet with the updated prices.
Public Function TimedFunction() As Boolean 
'Function to be exectued at timed intervals. 
Dim ws As Worksheet 
Dim vntValue As Variant 
Dim arrData As Variant 
Dim intArraySize As Integer 
Const PRICES_COL = 5

If Application.Workbooks.Count > 0 Then 
 If ActiveWorkbook.Sheets.Count > 0 Then
 'Just make sure there's at least one worksheet showing
 vntValue = Empty
 On Error Resume Next
 Set vntValue = Application.Evaluate(Chr$(39) & 
 _ ActiveSheet.Name & Chr$(39) & "!A1") 
 On Error GoTo 0
 If Not IsEmpty(vntValue) Then
 Set ws = ActiveSheet
 If ws.Range("A1") = "UPDATEME" Then
 'This is a valid portfolio sheet; refresh risk data
 If mclsData Is Nothing Then
 Set mclsData = New CDataAccess
 End If
 If mclsData.RefreshPortfolioPrices(arrData) Then
 'Update the worksheet
 intArraySize = UBound(arrData) + 1
 ws.Cells(START_DATA_ROW, PRICES_COL).Resize(intArraySize, 1).Value 
 _ = Application.Transpose(arrData)
 End If
  'This is not a valid portfolio sheet; ' no need to refresh risk data.
 End If
  Set ws = Nothing
 End If
 End If
End If 
'Trigger timed function again, unless global 
' object has been destroyed by user action.
If Not (gclsTimer Is Nothing) Then
End If

End Function

This function requires a bit of explanation.  First, keep in mind that the Add-In needs to know what’s going on in the “ActiveWorkbook”.  This will not be the Add-In workbook itself, but some other workbook that the user has opened (hopefully, the portfolio workbook).  If no worksheets are present in the Activeworkbook, then any calls to the Add-In’s functions will generate an error, so we confirm that there is a worksheet open in the “ActiveWorkbook”.  Remember at the beginning of this post I mentioned the user requirements that no structural changes could be made to the portfolio workbooks?  Well all we need for our purposes is some consistent piece of information in the portfolio workbook to allow our Add-In to tell if the active workbook is an actual portfolio workbook that will require pricing updates.  In this example, I typed the words “UPDATEME” in unused cell A1 (using white text on white background so it’s invisible).  This will be the indicator that our Add-In will look for.  If there was already something consistent in all of the client’s portfolio worksheets, then maybe you wouldn’t have to enter anything bogus like this.  But, in this demonstration, we’ll use “UPDATEME” as our indicator.  The TimeFunction routine proceeds to look for “UPDATEME” and, when it finds it, it places the array directly into the target range, transposing it using the built-in Excel Transpose function.

If any of the conditions fail, i.e. if there’s no worksheet open, or if the worksheet does not contain the word “UPDATEME” in cell A1, the procedure just exits gracefully and the user is not even aware that anything is running in the background.

The final point that has to be made is that once the Application.OnTime function executes, it is done and will not execute again.  So, it must be initiated again at the end of the function.  Notice that the code checks for the existence of a gclsTimer object first.  This is because some external process (possibly even the user clicking on the Ribbon) may have stopped the timer and destroyed this object while this function was in the process of executing.  In such a case, we don’t want to try to refer to the object again.  If it’s “Nothing”, then we just leave it be and let the function complete.

  • Working backwards to the beginning, we now have to create the ThisWorkbook methods which manage the whole process.  When an Add-In is attached, either directly by the user or when Excel starts, the Workbook_Open event of ThisWorkbook automatically fires.  We will use this to start our timer process running immediately:
Private Sub Workbook_Open() 
End Sub
  • Conversely, when Excel closes, we want to stop the timer before exiting so we use the Workbook_BeforeClose event of ThisWorkbook to terminate the timer:
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
End Sub

Recall that “InitializeTimer” and “CloseTimer” are both methods we created earlier in modGlobal so they are accessible to any module in the project.  Save the project.  Might also want to compile now (Debug… Compile TimedUpdater) just to flush out any syntax errors that may have seeped into the code.

  • Now, we’re in the home stretch.  As things are right now, the Add-In will function.  If you attach the Add-In normally, the timer will fire and the Add-In will keep looking for an open portfolio workbook to update.  If you have not yet created a portfolio workbook (see the graphic near the top of the article for required layout), do so now.  IMPORTANT: Remember to add “UPDATEME” to cell A1 before you save it.  With the Add-In attached and this new workbook open, you should see the prices in column E updating approximately every 3 seconds.
  • The finishing touch to this project will be to add some Ribbon buttons so that the user can control the starting and stopping of the timer as well as the interval.  Managing the Ribbon is beyond the scope of this post, but if you’d like to follow along, I’ll take you through the process very quickly.

The Ribbon…

  • First, save the Add-In and then close Excel completely.
  • The “Custom UI Editor for Microsoft Office” tool is required.  If you don’t have it installed, you can download it free from http://openxmldeveloper.org/archive/2006/05/25/CustomUIeditor.aspx.
  • Run the Custom UI Editor and use it to open the Add-In (MDTimedUpdate.xlam).  You’ll see the Add-In name listed in the Editor’s left pane (see below).
Custom UI Editor screen shot
Add-In loaded in Custom UI Editor
  • Right-click the Add-In name in the left pane and select “Office 2007 Custom UI Part” from the popup menu (unless, of course, you’re actually using Office 2010 for this exercise).  This will create a hidden XML file within the Add-In project.
  • Copy the following XML into the right pane (we’ll address all of this stuff in another post):
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab id="tabMD" label="MD Add-Ins" > <group id="grpAutoUpdater" label="Auto Updater" > <button id="btnStartTimer" label="Start Timer" imageMso="RecurrenceEdit" size="large" onAction="MDStartTimer" /> <separator id="sep1" /> <button id="btnStopTimer" label="Stop Timer" imageMso="PauseTimer" size="large" onAction="MDStopTimer" /> <separator id="sep2" /> <comboBox id="cboSetInterval" label="Set Interval (seconds):" onChange="cboSetInterval_Click" > <item id="itemSeconds1" label="1" /> <item id="itemSeconds2" label="2" /> <item id="itemSeconds3" label="3" /> <item id="itemSeconds5" label="5" /> <item id="itemSeconds10" label="10" /> <item id="itemSeconds15" label="15" /> <item id="itemSeconds20" label="20" /> <item id="itemSeconds25" label="25" /> <item id="itemSeconds30" label="30" /> </comboBox> </group> </tab> </tabs> </ribbon> </customUI>
  • Click the Save button to save your changes.
  • Click on the last toolbar button on the right (tooltip shows “Generate Callbacks”).  This will generate the Callbacks that you’ll need to plug into your Add-In project in order for the Ribbon to be able to communicate with it (see image, below).  Copy these Callback signatures and paste them into Notepad or any other convenient holding place.
Callbacks generated by Custom UI Editor
VBA Callbacks generated by Custom UI Editor
  • Exit the Custom UI Editor.
  • Return back to Excel and open up the Add-In once again (disabling macros so that the timer does not try to fire when you’re working).
  • Insert a new standard module and name it “modRibbon”.
  • Copy the new subroutine signatures into modRibbon (MDStartTimer, MDStopTimer and cboSetInterval_Click).  We’re now going to add code to these three subroutines.
Sub MDStartTimer(control As IRibbonControl) 'Callback for onAction of Ribbon button btnStartTimer InitializeTimer End Sub
Sub MDStopTimer(control As IRibbonControl) 'Callback for onAction of Ribbon button btnStopTimer CloseTimer End Sub
Sub cboSetInterval_Click(control As IRibbonControl, text As String) 'Callback for cboSetInterval onChange.  This will execute ' when the user clicks the comboBox on the Ribbon.  The "text" ' argument will contain the number of ' seconds to use for the timer interval. If Not gclsTimer Is Nothing Then gclsTimer.TimerInterval = "00:00:" & Right("0" & text, 2) End If End Sub
  • The MDStartTimer subroutine simply passes along control to the InitializeTimer function we defined earlier in modGlobal.  The MDStopTimer subroutine simply makes a call to the CloseTimer subroutine we defined earlier.  The cboSetInterval_Click subroutine contains the code that will be fired when the user chooses an item from the new ComboBox on the Ribbon (which we will see shortly).  The ComboBox will list various choices for the number of seconds to use for the Timer interval.  The value is formatted the way a “TimerInterval” is expected to be formatted (i.e. “00:00:03” for 3 seconds).
  • Save the Add-In and then exit completely.
  • Open Excel and attach our new Add-In.  If all has gone well, you should see a new Ribbon Tab called “MD Add-Ins”.  Click on this tab and you’ll see the “Start Timer” and “Stop Timer” buttons, as well as the “Set Interval (seconds)” ComboBox (see image, below).
New Add-In Ribbon controls
New Add-In Ribbon Controls

Give it a Whirl…

With the Add-In attached, open the test Portfolio workbook that you created earlier.  You should see the asset prices updating approximately every 3 seconds.  Test out the Add-In’s functionality.  Clicking the “Stop Timer” ribbon button should stop the timed pricing updates.  Click “Start Timer” to get them going again.  At any time you can change the interval by selecting a new value from the ComboBox.


With a very small amount of code we have created the framework for an auto-updating portfolio pricing tool.  It can be easily adapted to any project that requires timed updates or monitoring.  A useful additional feature you might want to add would be to retain the user’s Interval selection between sessions so they don’t have to re-set it every time they open Excel.  In future posts we’ll get more into the inner workings of the Ribbon, which can be kind of complex (at least, I think so) and quirky.



Read Full Post »