The various Excel File Formats

Back in the days of Excel 2003, saving a workbook file is simple. You only save in .XLS file format. However, starting from Excel 2007 saving a workbook got more complicated because there quite a number of formats you can choose. I’m going to focus on the 3 most important Excel file formats to the regular Excel user, and finally give an answer to which is the best Excel file format to use.

The Advantages of Binary Excel File Format

The XLSB binary format is a full-fidelity file format that can store anything you can create in Excel, but the XLSB format is optimized for performance in ways that aren’t possible with a pure XML format. Internally it is a ZIP compressed file and this reduces the disk space required to store files and decreases the bandwidth needed to transport files through e-mail or networks.

The second advantage of the XLSB format is, it supports macro-enabled workbooks. That’s right, if you have VBA codes embedded in a workbook, saving your workbooks in XLSB instead of the usual XLSM format means your files can be smaller, and opens faster by your computer!

Experiment with the XLSB Excel file format

Here is a simple experiment you can do to verify these advantages.
Step 1. Create a normal Excel workbook at fill a huge range say from cells A1:Z500000 with anything.
Step 2. Save this workbook as a XLSX Excel file format, XLSM Excel file format and also as a XLSB Excel file format.

Experimental Results of Excel file formats

The results seems to corroborate with the theory. The XLSX and XLSM Excel file format created files that is 33Mbyte, whereas the same data saved as XLXB Excel file format created a file that is only 2.5Mbyte. This is a 92% reduction in file size!

Opening the XLSX and XLSM Excel file format was painfully slooow. It took about 28 seconds. The XLSB Excel file format takes only about 6 seconds to open on my Intel i7 laptop. This measures about a 75% reduction in file opening times.

It’s obviously clear that XLSB Excel file format is definitely the best file format to work with!

Step 1: Search for the Right Windows API to solve a problem

There are numerous sites that provides sample VBA codes for creating millisecond resolution timers. In this tutorial, not only I will show the complete VBA codes that you can simply copy and use, I will describe the research process involved to create millisecond timers in VBA using Windows APIs so that you can deepen your understanding of using Windows APIs with VBA coding.

Our motivations to create a millisecond timers is to allow us to measure the execution time for a piece of VBA code. Ideally this timer should be able to measure up to millisecond resolutions. However, looking at native VBA and Excel time related functions they only provide up to seconds accuracy.

A web search on “timers using Windows API” eventually lead to this documentation. Great, so we can use 2 Windows APIs QueryPerformanceFrequency function to express the frequency, in counts per second and the QueryPerformanceCounter function to retrieve the current value of the high-resolution performance counter in order to measure time in milliseconds. The basic logic is to determine this logic: (“End Performance Counter” value – “Start Performance Counter” value / Performance Frequency value).

Step 2: How to Create the Windows API Function Declaration for VBA

In a previous article I described how this is done (you might want to go read that before continuing on here). A quick search for “QueryPerformanceFrequency” and “QueryPerformanceCounter” in the text file “Win32API_PtrSafe.TXT” which is the list of Windows API function declarations for VBA yielded this:

What is this LARGE_INTEGER ?

Notice that the arguments to both Windows APIs have the data type LARGE_INTEGER. If we use this declaration as it is, VBA will give us a compile error: “User-defined type not defined”. A quick web search on the phrase “Windows API LARGE_INTEGER” tells us that it “Represents a 64-bit signed integer value”. So we can change the declarations slightly to use the native VBA 64-bit integer data type Currency.

The Complete VBA Code for Millisecond Timer Using Windows APIs

So let’s put these 2 Windows API function declarations at the top of a code module and use it to measure elapsed time in milliseconds.

In my next tutorial we will explore what kind of VBA codes can help to speed up execution times.

Windows API embraces VBA

The Windows Application Programming Interface (Windows API) is a set of functions from the Microsoft Windows operating system that are available to Windows programmers. Fortunately, the Windows API has always exposed a large part of the underlying structure of the Windows systems to VBA programmers.

Why VBA Programmers Need Windows API

Often times a VBA programmer may not be able find a suitable Excel or VBA function to perform a task but is available within the Windows APIs. For example, VBA does not have a function to measure time in milliseconds but it is available in Windows API (I will write another tutorial on this). In such cases, a VBA program is able access Windows APIs by declaring the Windows API function at the top of a code module.

How to declare a Windows API in VBA

A Windows API function must be declared correctly at the top of a VBA code module. The declaration statement will tell VBA 3 important pieces of information.
1. The Window API function name you’re using.
2. The Library location of the the Windows API function.
3. The Window API function’s arguments.

Here is an example:

Let’s try to break this down into plain English.
Declare: A Windows API declaration always start with the Declare keyword.
PtrSafe: By adding PtrSafe after the Declare keyword, it makes this API work with 64-bit Excel. If you are writing VBA code in 32-bit Excel, this keyword should not be used.
Function GetSystemDirectory Lib “kernel32”: This tells VBA that GetSystemDirectory is the name of Windows API that will be used in the VBA code and the API is located in a library called “kernel32”.
Alias “GetSystemDirectoryA”: As a VBA programmer, you can ignore this. What this means is within the kernel32 library, the Windows API actual name is  GetSystemDirectoryA.
(ByVal lpBuffer As String, ByVal nSize As Long) As Long: After calling the function, the systems directory is
contained in lpBuffer, and the length of the directory string is contained in nSize. The Windows API only returns a number indicating the length of the directory string.

Below is a VBA Sub to use this Windows API:

Practical Windows API Tips for the VBA Programmer

As you can see, it is essential to get the Windows API declaration exactly correct. There are many resource in the web that lists down the declarations for Windows API calls. The VBA programmer only needs to copy the declarations and use the functions without understanding the details. As a useful resource you can obtain a list of Windows API declarations for VBA by searching for this file “Win32API_PtrSafe.TXT”. Or you can download it here (don’t worry it’s a plain text file).

How to Deal with 32-bit Excel and 64-bit Excel

To ensure that a Windows API function call to work in 64-bit Excel, we need the key word “ptrSafe” somewhere in the API declaration. To ensure that an API function in both 32-bit and 64-bit Excel, we need to declare two versions of the function declaration by using conditional compiler directives.

What is VBA.

Visual Basic for Applications (VBA) first appeared in Microsoft Excel 5.0 all the way back in 1993. It quickly gathered success among 3rd party software developers to create automation solutions using Excel. The ability to write VBA programs within Excel is still highly valued in today’s job market.

The VBA programming language was upgraded in 2010 with the introduction of Visual Basic for Applications 7 in Microsoft Office applications to support 64-bit & 32-bit Windows operating system. Microsoft Office 2013 and Office 2016 now includes VBA 7.1. With 7 major versions of development over 25 years, VBA is mature programming language with a vast community of developers all over the world.

VBA and Microsoft Office Applications.

VBA programs can never run as standalone program or on its own. It can only run within a host Microsoft Office application. That is the reason why we always write VBA programs within Excel, Outlook, PowerPoint, Word and etc. The VBA code is always embedded within a Microsoft Office document file. It is not a coincidence that VBA is built into most Microsoft Office applications, including Office for Mac OS X (except version 2008).

Code written in VBA is compiled to Microsoft P-Code (packed code), a proprietary intermediate language, which the host applications (like Excel, Word, Outlook) store as a separate portion separate from the document portions of the file. The intermediate code (P-Code) is then executed by the host application. So when there is a computer bug in a VBA program, we often need to kill the associated Office application.

VBA and Office Automation.

Let’s not forget the key purpose of VBA’s existence. VBA allows a computer programmer to write a program within an Office document in order for the Office Application to perform automated tasks. Hence we can write a VBA program within an Excel file to instruct the Excel Application to perform automated work for a human. And expanding on this idea, this VBA program within Excel can even control other Microsoft Office applications like Outlook to send emails.

And till this day, VBA as a programming language is still highly relevant for today’s work environment because of Microsoft Office’s pervasive presence. Knowing how to write VBA programs allow someone to increase the productivity of a small department by automating many routine tasks in Microsoft Office most useful applications such as Excel, Access, Word and Outlook.

To explain the question “What is the Difference between a Macro and VBA ?”, let’s start from a simpler question “What is the Difference between a Book and English?”. Well, a book contains text that humans can read to understand ideas, abstract concepts, instructions and English is the language that a book can be written in. Get the idea? And that is the similar answer to the question “”What is the Difference between a Macro and VBA?”.

Trying to compare a Macro and VBA is quite fruitless as there are little grounds for comparison because both are very different concepts. A better question is to ask “What is the Relationship between a Macro and the computer language VBA”. And my answer will fit this new question much better: A Macro is a set of instructions that when read and understood by Microsoft Excel, it will perform work for the Excel user. Similar to a book, a Macro is written in a language known as “VBA” (which stands for “Visual Basic for Applications”) that Microsoft Excel is built to understand.

Now you might be wondering where on earth does a Macro comes from. You can create a Macro by using an Excel feature called “Macro Recorder” found in the Developer’s Ribbon.

The macro recorder records a user actions so that Excel can repeat them with less human effort. And without going into difficult details, this “Macro” is written in a computer language called “VBA” that was invented by Microsoft back in 1993.

Share this Excel Chart on Your Site

A deadly heat wave in Tokyo, producing record high temperatures in Tokyo in July 2018. The heatwave in Tokyo has toppled temperature records across the country, with Kumagaya in Saitama outside Tokyo setting a new nationwide record on Monday with temperatures hitting 41.1 Celsius. Temperatures in Japan’s western cities of Yamaguchi and Akiotacho reached record highs of 38.8 Celsius and 38.6 C, respectively, on 25th July afternoon. An agriculture ministry official in Tokyo, the capital, warned against “pretty severe price moves” for vegetables if predictions of more weeks of hot weather held up, resulting in less rain than usual.

Japan Meteorological Agency has predicted it will remain hot for a few more weeks, and that we will have less rain than the average. I searched and downloaded some weather data from the Japan Meteorological Agency for Tokyo from the year 1875 to 2018. A total of 144 years of average monthly temperature data. You can take a look here.

Since I’m curious about the average July temperatures in Tokyo, I plotted the July temperatures as a line chart, added a linear trendline and forward forecast for 20 years. Interestingly, the Equation shows 0.0172X + 23.836. What it means is the mean July temperatures are expected to increase by 0.017 degrees Celsius every year.

So even as we can expect more wild temperature outliers like heat waves, we can expect temperatures are rising in Tokyo due to global warming.