Life in the Fast Lane: Excel Buffer Class (VBA)

Monday, 05 September 2011 00:00 Last Updated on Tuesday, 06 September 2011 03:33 Written by Steve Powell

Introduction

In a previous article I wrote about creating a buffer class in C# to access cells in a worksheet. The theory behind it was that accessing Excel one cell at a time is slow while doing it in batches is significantly faster. The same theory applies to all languages that you can program Excel in so in this article I present a class for VBA projects that does the same thing.

About Buffer Class

I use a class because this way the buffer and location of the buffer are held in member variables so you could use multiple buffer objects without any problems. The only major limitations for this class are:

1. It’s column based. So if you access Excel row by row this would be useless but changing it shouldn’t be too difficult.
2. It acts on the active sheet only. So you should probably record the active sheet at the beginning of a subroutine then restore it afterwards before returning.

ExcelBufferClass has the following public methods:

• ReadCell(Row, Column) – Returns the value of the cell at the coordinates using the buffer.
• WriteCell(Row, Column, Value) – Writes Value to the buffer.
• Flush() – Flushes the buffer out to the spreadsheet. So if WriteCell has been called earlier this will copy the buffer to the sheet.

In order for the buffer to improve performance you must access cells from top to bottom, one column at a time. If you need a different access pattern you’ll have to make adjustments.

Download

Download the Excel workbook with ExcelBufferClass and some example code for Excel 2007/2010 here.