Importing and Exporting Arrays in VBA

There are times when you need to move data between your worksheet and VBA and vice versa. This post will explain how to move data in either direction.

Importing

In this context, importing will mean moving data from the worksheet to the VBA developer. We are going to move the data below into the developer

Below is the code to do this

Option Base 1
Sub bringArray()
Dim A() As Variant
A = Selection
End Sub

The code does the following

Line one is an option to set the base to 1. This makes sure that VBA does not add an additional column to the array.
Line 2 is where we begin the subroutine
Line 3 dim or create our array as a variant. A variant is a highly flexible data type in VBA.
Line 4 tells VBA to take the data from the highlighted cells in the worksheet and save them in A.

Below is the output

THe imaage above is sharing with you the data that is now inside the developer. The numbers in the value column are the same as the numbers found in the worksheet.

The code below is slightly different in that it can be used when you know exactly what rnage of data you want to take from the worksheet.

Option Base 1
Sub bringArray()
Dim A() As Variant
A = Range("A1:B2")
MsgBox A(1,1)
End Sub

The only difference is in line 4 where the range function is used rather than the selection one. Since the output is the same as the previous example it will not be displayed here.

Export

For export we are taking data from the VBA developer and placing it in an Excel worksheet. This code will involve the use of nest for loops. Below is the code.

Sub sendArray()
Dim i as Integer, j As Integer
Dim A(2,2) As Integer
For i = 1 To 2
	For j = 1 To 2
		A(i,j)=3*i-j
		Range("A1:B2").Cells(i,j) = A(i,j)
	Next J
Next i
End Sub

Here is what the code does

Line 1 declares the subroutine
Line 2-3 declare the variable and array we need. The array is set to 2 x 2 because we know how big it will be .
Line 4-9 are the nested for loop. The outer for loop is for the rows and the inner is for the columns. For the array we are taking the value of the row and multiplying it by 3 before subtracting whatever the value of j is. For example
i =1 and j = 1 then
(3*1)-1 = 2 [This is the output for row 1 column 1
The range function is used to know where to place the results

Below is the output for this code

You can see that the results to the left are duplicating what is found in the right indicating that the code is a success.

The variation below allows you to create the array in the developer place it in the worksheet and also copy it to a cell range you select. Below is the code.

Sub sendArray()
Dim i as Integer, j As Integer
Dim A(2,2) As Integer
For i = 1 To 2
	For j = 1 To 2
		A(i,j)=3*i-j
		Range("A1:B2").Cells(i,j) = A(i,j)
	Next J
Next i
Selection=A
End Sub

What is new is in the second to last line. Here we set selection to A which will be the new location for our output. Below are the results.