Newsletter by

View this email in your browser Newsletter for January 2016

Creating a Table of Content

Do your models have a manual? I bet they don't. The least extensive type of documentation is a table of content listing all worksheets in a workbook. Near the bottom of this message you will find a small macro which:

  • inserts a worksheet named ToC into your workbook if it isn't there
  • Updates a listobject (also known as a table) with the list of worksheets, but with a twist:
  • Remembers any comments you entered into that table and makes sure those comments stay with their worksheet when the table is updated:
You can also download an add-in which has this code included.

Using VBA to contact web services

(Checking validity of European VAT numbers using VBA)

I had some code lying on a dusty shelf which enables me to contact a web service and request information from it. (see near bottom of this newsletter). Looking for some demo webservice I bumped into this one: a web service which returns true or false if sent a European VAT number. The wsdl of this webservice can be found here:

The hard part of communicating with a web service is that you need to pass the service a valid request, called a SOAP envelope (just a piece of XML really). If you do that, the service returns xml code. The code I posted uses the Microsoft XML library (Tools, References in the VBA editor) for both the xml processing and the communications with the webservice.

Suggestions for content

That's right, I'm asking you! If you have an Excel or VBA subject you would love to see covered in my newsletter, drop me a line! I'll even credit you for it (if you wish).

Excel Events, courses

New dates for Excel VBA voor Financials (7th run)

We have set the dates for the seventh run of our successful (Dutch) course "Excel VBA voor Financials", which I present together with Tony De Jonker. Mark the dates: June 1, 8 and 15, 2016 For more information, visit

Financial modelling with Excel

Together with Tony de Jonker I will be hosting this in-house training on February 2, 2016.

Advanced Excel VBA

On February 16 and 23 I will be conducting an in-house training covering advanced VBA subjects such as Error handling, Userform design, Class modules.

The Amsterdam Excel Summit

The dates for our famous Amsterdam Excel Summit have been set! Join us on May 26th and 27th, 2016 in Amsterdam for the third Amsterdam Excel Summit. This time the Summit itself is a one-day event but we have added a post-conference training day: The Excel Charting And Dashboard Masterclass. If you would like to be informed about this event, make sure you visit our event page, where you can register for updates.

Excel Summit South

If coming to our Amsterdam Excel Summit is too much of a trip to you, consider visiting the "Excel Summit South" by our Australian colleagues!

Macro code

Toc creator

Public Sub UpdateTOC()
' Procedure : UpdateTOC
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (
' 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
        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 = "Worksheet"
        oToc.Range("D2").Value = "Link"
        oToc.Range("E2").Value = "Remarks"
        oToc.ListObjects.Add xlSrcRange, oToc.Range("C2:E2"), , xlYes
    End If
    On Error Resume Next
    'Empty the table
    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 = _
            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
        End If
    Application.Calculation = lCalc
    Application.ScreenUpdating = bUpdate
End Sub

Webservice code

Option Explicit

'Requires a reference to the Microsoft XML (v6) library

    Dim sURL As String
    Dim sEnv As String
    Dim xmlhttp As New MSXML2.xmlhttp
    Dim xmlDoc As New MSXML2.DOMDocument    'DOMDocument
    Dim sCountryCode As String
    Dim sVATNo As String
    sURL = ""
    sCountryCode = InputBox("Please enter the countrycode of your customer")
    sVATNo = InputBox("Please enter the VAT code")
    If Len(sCountryCode) > 0 And Len(sVATNo) > 0 Then
        sEnv = "<soapenv:Envelope xmlns:soapenv="""" xmlns:urn="""">"
        sEnv = sEnv & "<soapenv:Header/>"
        sEnv = sEnv & "<soapenv:Body>"
        sEnv = sEnv & "<urn:checkVat>"
        sEnv = sEnv & "<urn:countryCode>" & sCountryCode & "</urn:countryCode>"
        sEnv = sEnv & "<urn:vatNumber>" & sVATNo & "</urn:vatNumber>"
        sEnv = sEnv & "</urn:checkVat>"
        sEnv = sEnv & "</soapenv:Body>"
        sEnv = sEnv & "</soapenv:Envelope>"

        With xmlhttp
            .Open "POST", sURL, False
            .setRequestHeader "Content-Type", "text/xml;"
            .send sEnv
            Set xmlDoc = New MSXML2.DOMDocument
            xmlDoc.LoadXML .responseText
            If LCase(xmlDoc.getElementsByTagName("valid").Item(0).Text) = "true" Then
                MsgBox "Valid VAT number"
                MsgBox "Invalid VAT number"
            End If
        End With
        MsgBox "No VAT or no Country code entered"
    End If
End Sub


Auditing of Formulas made easy

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Mission-critical Excel Model Crashes

Do you have mission-critical Excel files that cause problems? Consider our Excel File Remediation Utility

Nederlandse cursus: Excel VBA voor Financials

Kost uw maandrapportage u vele uren saai en repeterend werk? Dan is het de hoogste tijd voor onze cursus Excel VBA voor Financials!.
De Volgende cursus wordt gegeven op 1, 8 en 15 juni 2016
Copyright © 2016 JKP Application Development Services, All rights reserved.

unsubscribe from this list    update subscription preferences 

Email Marketing Powered by Mailchimp