FOUR ELMS BOOKKEEPING
Mudford, Yeovil, Somerset
WHATEVER HAPPENED TO THE DATE-TIME PICKER IN RECENT RELEASES OF MICROSOFT EXCEL?
Backward compatibility with earlier versions of Excel is something one might expect from Microsoft. Shouldn't Visual Basic UserForms and code
written in Excel 2010 or even Excel 2003 or 2007
still work in Excel 2016 or Excel 2013? Apparently not!
Most code written for the Microsoft Operating System relies on dynamic link libraries produced by Microsoft.
The Visual Basic code is like the top layer of a stack which calls up and executes DLL or OCX files resident in the Windows operating system.
Take away even one element of the supporting framework and the top level code ceases to function. This can cause compatibility issues when
Visual Basic for Applications (VBA) modules are imported into later versions of Excel than the one in which they were originally written: dynamic
link libraries or files needed by the VBA code are sometimes retired from the Excel DLL support framework causing the VBA modules to stop working.
After upgrading to Office 2016 one of the first things I noticed is that many VBA Userforms which I had created for the input of accounting data no longer worked.
The error messages from the Visual Basic compiler were cryptic: the offending forms were declared not to exist despite the fact that they were displayed correctly
on the screen in the IDE form designer. A line by line debug of the form revealed that this perplexing message was triggered by one particular Active-X control:
the date-time picker. What the compiler actually meant was: "the date-time pickers on the form cannot be created" but the closest it could get was to declare
the form non-existent. Like ancient oracles in Greek mythology the compiler sends programmers riddles to decipher.
Figure 1: Date Time Picker
The date-time picker is an extremely useful control on forms. Not only does it facilitate the input of dates by users, but it overcomes any confusion between
US-style and UK-style date formats when dates are input textually as a combination of numerals and backslashes.
On the left is a picture of this control in action automatically
highlighting the default date on which I used this control.The control allows the user to move easily one month or a whole year
backwards or forwards from the current date.
Dates can be entered quickly and accurately: noone used to this well-designed Active-X control would like to revert to entering dates via the keyboard as text.
This makes it disappointing that Microsoft have apparently removed the file which created this Active-X calendar tool,
but not replaced it by any new control which has similar functionality.
Excel 2016 simply lacks an indispensable Active-X control which it enjoyed in the past. So what can one do?
The file which creates the date-time picker is called mscomct2.ocx and used to live in the
C:\Windows\SysWOW64\ directory. If you are about to upgrade from Excel 2003, 2007 or 2010 to Excel 2013 or
later then you may wish to keep a copy of this file so that you can continue to use it after your upgrade. Unfortunately
this file is no longer available for download from Microsoft and only unofficial sites still offer this file for download.
The process of making this control available
in a later version of Office is not entirely straightforward and precautions are advisable to safeguard your Windows registry
and operating system in case you make a mistake.
The date-time picker is an old VB6 control with a long service life stretching back to the days before .NET
(Visual Basic 6 was released in 1998).
As such it is a 32 bit legacy executable. When Microsoft moved to 64 bit operating
systems it divided the dynamic link (DLL and OCX) files between two system folders.
DLL files intended for 64-bit architecture were placed in the C:\Windows\System32\ folder
whereas legacy 32-bit DLL or Active-X files got the C:\Windows\SysWOW64\ folder.
(The names of the folders can be confusing: the 32-bit DLLs go in the folder with 64 in its name
while the 64-bit DLLs go in the other one with 32 in the name.) Putting a DLL in the correct folder is
important. If we place our mscomct2.ocx Active-X control in the SysWOW64
folder the Windows operating system will know that it is a 32-bit executable and make the necessary
adaptations to run a 32-bit executable within a 64-bit operating system.
To get a legacy date-time picker working in Excel 2016 we need to do the following:
- Obtain, copy from an old machine or download a copy of mscomtl2.ocx to the desktop or downloads folder of the machine running Excel 2016.
- Running the machine as administrator, copy mscomtl2.ocx to the C:\Windows\SysWOW64\ folder.
- Register the newly imported mscomtl2.ocx executable with the Windows Registry. In case you make a mistake, you are strongly advised to
make a backup of your Windows Registry
before carrying out this step. This step also requires administrator access to the computer.
PLEASE NOTE: The information in this article is provided in good faith and describes a technique successfully used
by the author on a machine running 64-bit Windows 10 Professional operating system and Excel 2016. However Four Elms Bookkeeping accepts no liability
for any damage to computer equipment that may result from readers attempting to follow the instructions or advice in this article