jkp-ads.com 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:
You can also download an add-in which has this code included.
- 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:
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: http://ec.europa.eu/taxation_customs/vies/faqvies.do#item_16 a web service which returns true or false if sent a European VAT number. The wsdl of this webservice can be found here: http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl
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 topexcelcursus.nl
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!
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
.Name = "ToC"
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
'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
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)
Application.Calculation = lCalc
Application.ScreenUpdating = bUpdate
'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 = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
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=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:urn=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"">"
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>"
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "text/xml;"
Set xmlDoc = New MSXML2.DOMDocument
If LCase(xmlDoc.getElementsByTagName("valid").Item(0).Text) = "true" Then
MsgBox "Valid VAT number"
MsgBox "Invalid VAT number"
MsgBox "No VAT or no Country code entered"