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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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 WorkbookToArrayError: 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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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 TestError: 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!