Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Build Excel Add-in > Core functionality
Deze pagina in het Nederlands

Building an Add-in for Excel, Core functionality

In my experience, add-ins for Excel have something in common when you are building them: the time you spend with the core functionality is only a small proportion of the entire time needed to complete the project. In fact, I think this goes for many software projects.

Creating the Workbook

An Excel add-in is nothing more than a workbook which has been saved as an add-in. You can edit an add-in directly and save it like any other workbook, but I prefer to do all development in a "normal" Excel workbook and then when I am done save-as my development workbook as an add-in.

So open a fresh, brand new workbook (I opened the VBA editor alongside the Excel window, showing just the project explorer):
A new Excel workbook and its VBA project in the project explorer

Kind of smells like a new car, don't you think?

Core functionality

Inserting a module

The basis of this tool is inserting and updating a table of content worksheet named ToC. Since I opted to have a comment line next to each sheet's entry, we need to temporarily store those comments and (after refreshing the ToC) add them back where they belong: with their associated sheetnames.

So first, insert a module:

Inserting a module

and change the module name in the properties window:

The Name property of the module

Your VBA project should look like this now:

The VBAProject with one module

Double-click on modTOC to open the codewindow associated with it.

The core code

Without explaining in detail what the code does (I think you should be able to figure it out using the comment lines) this is what I have in that module:

'-------------------------------------------------------------------------
' Module    : modMain
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 30-4-2015
' Purpose   : The main routines of the tool
'-------------------------------------------------------------------------
Option Explicit

Public Sub UpdateTOC()
'-------------------------------------------------------------------------
' Procedure : UpdateTOC
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 30-4-2015
' Purpose   : Creates (or updates) a Table of Content sheet
'-------------------------------------------------------------------------
    Dim oSh As Object
    Dim oToc As Worksheet
    Dim vRemarks As Variant
    Dim lCt As Long
    Dim lRow As Long
    Dim lCalc As Long
    Dim bUpdate As Boolean
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    lCalc = Application.Calculation
    Application.Calculation = xlCalculationManual

    'Check if worksheet ToC exists, if not, insert one
    If Not IsIn(Worksheets, "ToC") Then
        With Worksheets.Add(Worksheets(1))
            .Name = "ToC"
        End With
        Set oToc = Worksheets("ToC")
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayHeadings = False
    Else
        Set oToc = Worksheets("ToC")
        'We have an existing ToC, store the entire table in an array
        'so we can restore comments later on
        vRemarks = oToc.ListObjects(1).DataBodyRange
    End If
    'Check for a table on the ToC sheet, if missing, insert one
    If oToc.ListObjects.Count = 0 Then
        oToc.Range("C2").Value = "Werkblad"
        oToc.Range("D2").Value = "Snelkoppeling"
        oToc.Range("E2").Value = "Opmerkingen"
        oToc.ListObjects.Add xlSrcRange, oToc.Range("C2:E2"), , xlYes
    End If
    On Error Resume Next
    'Empty the table
    'Ignore errors in case the table already is empty

    oToc.ListObjects(1).DataBodyRange.Rows.Delete
    For Each oSh In Sheets
        If oSh.Visible = xlSheetVisible Then
            lRow = lRow + 1
            oToc.Range("C2").Offset(lRow).Value = oSh.Name
            oToc.Range("C2").Offset(lRow, 1).FormulaR1C1 = _
                "=HYPERLINK(""#'""&RC[-1]&""'!A1"",RC[-1])"
            oToc.Range("C2").Offset(lRow, 2).Value = ""
            'Restore the comment for this sheet
            For lCt = LBound(vRemarks, 1) To UBound(vRemarks, 1)
                If vRemarks(lCt, 1) = oSh.Name Then
                    oToc.Range("C2").Offset(lRow, 2).Value = vRemarks(lCt, 3)
                    Exit For
                End If
            Next
        End If
    Next
    oToc.ListObjects(1).Range.EntireColumn.AutoFit
    Application.Calculation = lCalc
    Application.ScreenUpdating = bUpdate
End Sub

As you may have noticed, this code doesn't work. Worse: it generates a compile error if you try to run UpdateTOC:

Compile error, something's missing

This is because there is a call to a function called "IsIn" which we have not inserted yet. IsIn is a function originally suggested to me a long time ago (when I myself was still a VBA freshman) by my fellow Excel MVP Bill Manville.

Functions module

Since we might need more generic functions, let's insert a new module called modFunctions. All it needs at the moment is this code:

Option Explicit

Function IsIn(vCollection As Variant, ByVal sName As String) As Boolean
'-------------------------------------------------------------------------
' Procedure : IsIn Created by Jan Karel Pieterse
' Company   : JKP Application Development Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 28-12-2005
' Purpose   : Determines if object is in collection
'-------------------------------------------------------------------------
    Dim oObj As Object
    On Error Resume Next
    Set oObj = vCollection(sName)
    If oObj Is Nothing Then
        IsIn = False
    Else
        IsIn = True
    End If
    If IsIn = False Then
        sName = Application.Substitute(sName, "'", "")
        Set oObj = vCollection(sName)
        If oObj Is Nothing Then
            IsIn = False
        Else
            IsIn = True
        End If
    End If
End Function

Now try to run UpdateTOC, it should work!

Next: Creating the Ribbon user interface


 


Comments

All comments about this page:


Comment by: Jeff Weir (5/29/2015 12:32:48 AM)

Hi Jan Karel. Suggestion: Add err.clear before the End Function, to avoid this triggering error handling in the calling sub.

 


Comment by: Jan Karel Pieterse (6/1/2015 8:42:02 AM)

Hi Jeff,

The err.clear has little to do with triggering an error handler (or not). It is the On Error Resume Next in the function that avoids the trigger of an error handler higher up the call stack. Hence Err.Clear is not needed, unless you are using Err.Number = 0 to find out whether an error has been triggered somewhere.

 


Comment by: Jeff weir (6/1/2015 11:08:53 AM)

Sorry, I wasn't very clear in my comment. Agreed, the err.clear makes no difference to this sub. However, in my specific case, lots of my routines have an error handler like this:

errhandler:
If Err.Number <> 0 Then
'error handling code
end if

...so for my needs, I've added the err.clear to your function, so it doesn't inadvertantly trigger that error handler in the calling sub.


 


Comment by: Jan Karel Pieterse (6/1/2015 11:12:28 AM)

Hi Jeff,

Are you saying there is no exit sub or function above your error handler label? That'd be odd?

 


Comment by: Jeff Weir (6/1/2015 11:37:05 AM)

Correct, I don't have an exit above the error handler. Ah...I didn't realise my approach wasn't that common, and of course the lack of err.clear in your routine is only a problem for me because of my different approach.

I use Craig Hatmakers' approach from https://dl.dropboxusercontent.com/u/13737137/Starters/Error_Handling.pdf

He doesn't use Exit Function or Exit Sub to bypass the routine but rather just wraps his error handling code in an error number test because he feels that's closest to the spirit of Structured Programming Techniques. But it's probably also furtherest from what most VBA developers do in the wild.

So he/I use something like this

Select Case err.number
Case = 0 'Do nothing
Case x
Case y
End Select



 


Comment by: Jan Karel Pieterse (6/1/2015 12:32:55 PM)

Hi Jeff,

That is indeed an unusual approach. Doesn't mean the approach is wrong :-)

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].