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.

Option Explicit

Public Function WorkbookToArray(WB As Workbook) As Variant
'by Excel-Malin.com ( https://excel-malin.com )

Dim RowMax As Single
Dim ColumnMax As Single
Dim WS As Worksheet
Dim WorksheetsCount As Single
Dim WorksheetsCounter As Single
Dim RowsCounter As Single
Dim ColumnsCounter As Single
Dim ArrayTemp As Variant

On Error GoTo WorkbookToArrayError

'find the size of the 3 dimensions of the Array (WorksheetsCount, RowMax, ColumnMax)
RowMax = 0
ColumnMax = 0
WorksheetsCount = WB.Worksheets.Count

    For Each WS In WB.Worksheets
        If WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row > RowMax Then _
            RowMax = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        If WS.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column > ColumnMax Then _
            ColumnMax = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    Next WS

ReDim ArrayTemp(WorksheetsCount - 1, RowMax - 1, ColumnMax - 1)

'fill the Array with the values from the Workbook
WorksheetsCounter = 0

    For Each WS In WB.Worksheets
        For RowsCounter = 0 To RowMax - 1
            For ColumnsCounter = 0 To ColumnMax - 1
                ArrayTemp(WorksheetsCounter, RowsCounter, ColumnsCounter) = WS.Cells(RowsCounter + 1, ColumnsCounter + 1).Value
            Next ColumnsCounter
        Next RowsCounter
        WorksheetsCounter = WorksheetsCounter + 1
    Next WS

'assign the temporary Array (ArrayTemp) as result of the function
WorkbookToArray = ArrayTemp
Exit Function

    WorkbookToArray = CVErr(xlErrValue)
End Function

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 :

Sub TestWBtoArray()
'by Excel-Malin.com ( https://excel-malin.com/ )

On Error GoTo TestError

Dim MyWB As Workbook
Set MyWB = ActiveWorkbook

Dim MyArray As Variant
MyArray = WorkbookToArray(MyWB)

Dim SearchedData As String

SearchedData = "Value of: 1st sheet, 2nd row, 4th column = "
MsgBox SearchedData & """" & MyArray(0, 1, 3) & """"

SearchedData = "Value of: 2nd sheet, 10th row, 1st column = "
MsgBox SearchedData & """" & MyArray(1, 9, 0) & """"

SearchedData = "Value of: 9th sheet, 4th row, 15st column = "
MsgBox SearchedData & """" & MyArray(8, 3, 14) & """"

Exit Sub

    MsgBox "Requested data point (" & SearchedData & ") is not available in the Imported Workbook"
End Sub

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.