VBA IBAN Validator

From February 2014 onwards, IBAN will be the new European standard for bank account numbers. This standardization is part of the SEPA initiative to improve the efficiency of cross-border euro payments. I wrote an IBAN validator in VBA for an MS Access database I’m maintaining, but you can also use it in Excel or any other application that supports VBA.

The function ValidateIban takes an IBAN bank account number and returns True if the number is valid and False otherwise. It checks if the length is correct as per country and if, after rearranging and converting the IBAN code to an integer, the remainder of the modulo 97 operation equals 1. See wikipedia for a description of how to validate an IBAN number.

Sinterklaas kapoentje

Het is weer bijna Sinterklaasavond, dus tijd voor cadeaus, surprises en gedichten!

Een originele manier om je gedicht te presenteren is door middel van een Word document waarbij het gedicht woord voor woord verschijnt zodra je het document opent. Dit is vrij simpel te bewerkstelligen. Je schrijft eerst de tekst van het gedicht in een nieuw en leeg Word document. Vervolgens selecteer je alle tekst en maak je de kleur van de tekst wit zodat je niet meer kunt zien dat er wat staat. Door middel van de volgende VBA macro code maak je dan woord voor woord het gedicht zichtbaar:

Verder moet je dan nog wat code toevoegen om bovenstaande subroutine aan te roepen zodra het document geopend wordt.

Zie hier voor een voorbeeld. Denk er wel om dat je het uitvoeren van macro’s moet toestaan.

Mathematician’s Birthday Calendar

In my previous post I described a way of doing web scraping using XPath and VB. I showed how to get stock quotes from the web into Excel using this method. Recently I used the same method to get a list of birthdays of mathematicians. The data was scraped from Wikipedia lemmas like January_1January_2, etc. looking for lines containing the word “mathematician”. If you’re a math enthusiast you can get the calendar from here. Check out the code if you want to make a similar thing for physicists.

Web scraping using XPath and Visual Basic

More and more web services are popping up. Getting the weather in Paris or the price of a book on Amazon is only a REST, SOAP or XML-RPC call away. Unfortunately, most of the web content being delivered is still intended to be rendered in a browser and the providers of that content haven’t included a nice web service API for automated extraction.

Web scraping is a technique for extracting information from websites that don’t provide web services. One common way to find the required info on a webpage is to use regular expressions. A much better approach however, is to make use of XPath. XPath is a XML query language that can be used to locate a specific part of an XML document. One approach taken by some web scrapers is to parse an HTML document into an XML document and then use XPath to query the document. The problem with this approach is that most HTML out there won’t parse into XML too well. I will explain how to circumvent this problem below.

Let’s consider the following scenario. We want to automatically get a certain stock quote from the web, in this case the stock quote for AEGON, which happens to be my current employer. This data is published on the following website: http://www.marketwatch.com/quotes/aeg. We want to put the stock price in a cell in an Excel worksheet, and we’ll use VBA and the Microsoft Internet Control to navigate to the mentioned webpage and scrape the needed data.

First, we start by firing up Firefox and navigate to the webpage. Make sure you have the Firebug add-on installed and select the required data. Right-click and choose “Inspect element”.

Then hover over the required content in the Firebug pane, right click and select “Copy XPath”.

The XPath is now copied to the clipboard and looks like this: /html/body/form/div/div/div[7]/div[3]/div[3]/div/h2/span[2]/span.

Now we open a new Excel document, place a Button on the first worksheet and write the following code in it’s _Click() event.

Make sure you set references to the Microsoft Internet Controls and the Microsoft HTML Object Library. The XPath we got using Firefox/Firebug is used as an argument to the function getXPathElement. I wrote this getXPathElement function to be able to address locations in a DOM document as one would normally do in an XML document.

This function doesn’t support the full XPath syntax, but it serves our purposes. Clicking the button opens an Internet Explorer window and get’s the stock price value in cell A1. Download the full project here.

Countdown clock in Excel

My work buddy Jurre is drastically changing his career path for the better by relocating to Ireland this week. To help him keep track of the time he has left in his current job I made him a countdown clock in Excel. It shows the number of days, hours, minutes and seconds left both in separate cells of a sheet and in the titlebar of the Excel window. It demonstrates the use of the scarcely documented Excel function DATEDIF and the Application.OnTime method to update the clock at one second intervals. The workbook you can download here counts down to the new year 2010, but you can easily change the date and time it counts down to.

Mess with MS Access reports

When designing a MS Access form you can easily disable the minimize and maximize buttons by selecting the appropriate option in the form’ properties window. Such an option doesn’t exist for Access reports. However you can still mess with these report’ features by making use of VBA and the Win32 API.

To disable the minimize and or maximize buttons of a report window, place the following code in a VBA code module:

Then write the following code for the report’ OnOpen event:

Simply comment out one of the lines to disable only the minimize or maximize button.

Hide MS Access window from code

Suppose you’re building an application in MS Access, and your user interface consists of only one form. You don’t want to show the main application window, just the form you’ve designed. The most obvious way to do this is by making your form a popup form and do a call to the Windows API

from the Form_Open event to hide the main Access window. This will work, but there is a catch. Your application won’t be visible anymore in the windows taskbar and will easily get lost behind other programs.

Continue reading

Printing from EXTRA! Basic.

Attachmate EXTRA! is terminal emulation software to connect to mainframes. Part of this software is a Basic interpreter that allows you to write automation macros that do screenscraping on terminal screens. The scripting language used is called Extra Basic. Scripts have the extension .ebm, which stands for Extra Basic Macro.

This scripting language is a lot like Visual Basic, or QBasic and most code you can simply Copy & Paste. But there are differences.

At work, when extending an old macro (made by a now retired colleague) with printing capabilities I ran into some limitations of Extra Basic.

In QBasic there used to be a command LPRINT to send text to a line printer. Neither Visual Basic or Extra Basic have this command. To print from Visual Basic you use the Printer object, but to print from Extra Basic you need another approach.

Continue reading