How to set Excel’s ActivePrinter using VBA?

Changing Excel’s ActivePrinter using VBA isn’t as straightforward as you might think. In particular there is a localization issue.

Normalize data: from Userfriendly to Pivotfriendly

If possible I always use pivottables to create reports in Excel. Pivottables need well formed, normalized data tables as source, which are usually not suitable for data entry. In this post I describe two techniques to bridge the gap between userfriendly and pivotfriendly data structures.

Pivottable data connection models

The way a datasource connects to a PivotTable depends on the datasource. In this post an overview of the six connection models that you can create by means of Excel’s userinterface.

Excel and XML: four observations and two bugs

I have been working with Xml files in Excel for some time now, and thought it is time to share some observations.

Create Xml from Schema

In this post I describe a tool to create an Xml file from a schema (Xsd) using VBA and the DOM and SOM objects of the Microsoft XML library MSXML version 6.

Get XPaths from workbook – update

In Get XPath objects from Excel workbook I described a method to retrieve all mapped XPaths from a workbook. In this post I describe another method, using the Worksheet.XmlMapQuery function. It’s simpler, and faster.

IBAN validation with BBAN format check

This post describes free and ready to use Excel/VBA functions for IBAN validation, based on Mod 97 calculation and a format check of the country-specific Basic Bank Account Number (BBAN).

Sepa Direct Debits: from Excel to Pain

Europe is preparing for SEPA, the Single Euro Payments Area. One of the SEPA goals is to replace all national direct debit standards by one XML based SEPA standard by 1 February 2014. This article shows how to create such an Xml file from Excel, without scripting.

Get XPath objects from Excel workbook

When working with XML enabled workbooks the relevant objects and properties are easy to retrieve. Unfortunately a workbook has no built-in collection of XPath objects. How can we get a list of all XPath objects in a workbook?

Mastermind in Excel

In this post an Excel worksheet implementation of the well known Mastermind game. I developed the Mastermind part of this workbook several years ago. For this showcase app I added a scoreboard, and turned it into a so called dictator application.

Sequence of events in Userforms

In a previous post I wrote about the sequence of events in workbooks. This post is about the sequence of events in a simple userform. To built a reliable userform you need to know something about the userform mysteries.

A few notes on MSForms 2.0

Userforms and userform controls are not part of the Excel Object Model but have their own package. In this article some general notes on the MSForms library.

Debug and trace in VBA

Often when you debug your Excel app you want to monitor execution. Unfortunately the VBE has no built in trace tools. In this post I describe a simple but effective trace utility.

A true click event on worksheets

The worksheet object has a doubleclick and a rightclick event but strangely no click event. Instead, the worksheet has the SelectionChange event. Unfortunately this event won’t fire if we click the ActiveCell again. In this article I will show how to implement a true click event in a worksheet.

Disable Shift key on open

A user can prevent the Workbook_Open event from firing by holding down the Shift key during startup. Since Excel 2007 it is possible to ignore this, and run your code anyway.

The active control of a userform

If you need to know which control on a userform has the focus you could consider to use the ActiveControl property of the UserForm. However, this property does not necessarily point to the control having the focus.

The sequence of events in workbooks

When you open a workbook Excel not only raises the Workbook_Open event but also a few other events. The same applies to switching worksheets, or closing the workbook. In this post I examine the sequence of activation/deactivation events in an Excel multiple window application, and with a ribbon custom tab. However, most of the findings […]