Excel VBA code: Workbook to Array

VBA: Excel Workbook to Array

The VBA Arrays are a very effective way to work with data in Excel. The Array is a "table in the memory" that allows you to work extremely fast in multidimensional dataset without the need to have the source of the data open. In this article, you can find a "ready to use" VBA fonction that loads the content of an Excel Workbook to Array variable.
The code is easy to implement and 100% generic – you can use it immediately as is without modifications.

Excel Workbook to Array: how it works

The function WorkbookToArray() has one argument (of type Workbook) – that is the Excel Workbook that you want to load into the Array.
The output of the function is, logically, a three-dimensional Array that contains all the data from the Workbook.

The three dimensions are:

  • the Worksheet of the Workbook
  • the Row of the Worksheet
  • the Column of the Worksheet

Once the data are loaded to the Array (= to the memory), you can close the input workbook and just continue to use it's content from the Array.

That makes easy the use of loops in the dataset (and the use of, e.g., the conditions) but it allows you also to access whatever data-point of the Workbook content. Just select the exact place you want to see the value of.

For that, you just have to use the 3 coordinates listed above: the number of the Worksheet, the number of the Row and the number of the Column (below, you can see some examples of the use).

Important Remark: don't forget that by default, the counting of the values in an Array starts by 0. So if you want to see the first element of the Array, you need to "ask for the element n°0"!

VBA code "ready to use"

You can just copy/paste the following code to your VBA project. You don't need nothing else to use it.

Example of use of the WorkbookToArray() VBA function

To illustrate how this code can be use and how you can extract the values from the Array, I wrote the following small VBA procedure.
It loads the content of the active Workbook to an Array called "MyArray" and then it shows several values in the MessageBox :

Of course, you can use, as input, any Excel Workbook (not only the active Workbook). And of course, the way you use the output Array depends only on your needs.

I hope that this piece of code will help to make your work more efficient!

Laissez un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *