How fast do you really want your Microsoft Excel to perform, using VBA Code?

Microsoft Excel can be used extensively to work with data.

A worksheet in Excel has 17 billion cells (17,142,120,448) that can contain information on one worksheet of the workbook. Excel has 1,048,576 rows and 16,348 columns (A to XFD).

Excel versions before 2007 have16 million cells (16,777,216) that can contain information on one worksheet of the workbook. This consists of 65,536 rows and 256 columns (A to IV). Note that 65536 is a power of two,   2 to the 16th power (2^16).

How we read this data on the worksheet, store it in our PC’s memory and write it back into the format that we want, can be achieved in various different ways . Microsoft provides numerous different techniques in their Excel application.

We can choose the appropriate techniques that can achieve unheard of high speeds. We can also stick to techniques that achieve the same result but take more time and can even cause our worksheets fall over. This could result in a loss of unsaved data and time.

A very basic example is the difference in time it takes to run through a range of cells of the worksheet, load it into memory, loop through each cell’s data, while the cell’s data is in memory, perform a small calculation on it  and write the changed data back to the worksheet in a different range on the same worksheet.

If the data in the range, consists of 30,000 cells and we load it into memory and use a For… loop to run through it, it takes 2,450 milliseconds. If we use a For…Each loop to run through the 30,000 cells of data it only takes 5 milliseconds. This represent a 48900 % time saving.

The time saving increase exponentially if we load more data. If we apply this same exercise on 200,000 cells and we load it into memory and use a For… loop to run through it, it takes 120,741 milliseconds. If we use a For…Each loop to run through the 200,000 cells of data it only takes 35 milliseconds. This represent a 344874 % time saving.

	30,000 Cells	200,000 cells
Technique
For Loop	2,450 milliseconds	120,741 milliseconds
For … Each Loop	5 milliseconds	35 milliseconds
Time Saving	48,900%	344,874%

If we have to transfer these time savings to an 8 hour working day we can get a lot more done in one day.

The Processing Speed, Random Access Memory , Operating System, 32-bit or 62-bit computers and if the latest updates of our Operating System and Microsoft Office programs also have an effect on the speed. However not nearly as large as when we apply the correct techniques efficiently and effectively.

These techniques comprise a combination of programming techniques (e.g. loading data into an array vs a collection vs a dictionary etc.), using the correct Excel Object model (e.g. using Pivot Table results vs  Advanced filter results vs Data Tables etc.), API interfaces (e.g. using Operating system API’s vs other User Defined API’s).

32-bit versions of Microsoft Excel 2013 and Excel 2016 can take advantage of Large Address Aware (LAA) functionality after installation of the latest updates

This is the first in a range of articles that will be published to create an awareness of time saving techniques that can be applied in the Microsoft Office suite.