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.
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.
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.
You can see the second array that is copied below
Importing and exporting arrays is sometimes necessary when developing VBA code. The examples providing here give insights into how to do this.