For some situations, it's really beneficial if you can import

an array from Excel into the VBA environment.

So I'm going to show you a couple of ways we can do that.

And I'm also going to show you ways in which we can export.

So if you have a local array that's been defined in VBA,

I'm going to show you how you can export that back to Excel.

The first technique I'm going to show you is just to,

what I like to call an iteration import, where we're going to use nested for

loops to iterate through this selection, in this case, and

bring in each element into a newly defined array in VBA.

So the first thing I'm doing here is I'm deeming i and j as integers,

those are indices of iteration.

I'm going to count the number or rows, and count the number of columns so

I need those two variables.

Then I used the Selection.Rows.Count to count the number rows similar

than count the number of columns.

And now we're going to go through a set of nested for loops to bring in the data.

One thing I forgot to do here is to dim an array so

I'm going to dim it as empty parenthesis.

And I'll show you why we do this.

If you don't know to begin with, the size of your array that you're trying

to set up, you're local array, then you need to dim it as an empty parentheses.

And this is going to be, in my case, all I have is integers over here, so

I'm going to dim A empty parentheses as and integer.

So we let the editor know that there's going to be an array,

but right now at the beginning, we don't know the size.

The reason you have to do this,

you have to have all dim statements at the very beginning.

So I can't count the number of rows and columns and then dim, so

I can't put the dim statement there, otherwise, it won't work.

However, once we do know the size,

then I can use something known as the ReDim, so

I can ReDim now A with size nr and nc.

And that's going to be just an array of integers, and

now I can implement my for loops, my nested for loops.

So I've setup my outer for loop that'll iterate through the rows, our three rows.

In each row, we're going to iterate through all three columns.

And what we are doing at each combination of i and j is we're just saying A,

i, j are local array it's going to be = to Selection.Cells i, j.

So the 1,1 position of A would be the selection cells 11 and

so on, and then we can bring in the array.

And I'll just do something just to verify that it's working.

I'll just say message box A 2,2.

So let's go ahead and run through this and

we will look down here in the locals window.

So I go through this, we count the number of rows, we count the number of columns.

Now we iterate for i = 1 to 3, for j = 1 to 3.

A1,1 is = to Selection.Cells 1,1 and we keep going.

So I'll do this a couple of times and

then what we'll do is we'll open up A down here.

Now I haven't put in that statement option base 1.

So now, we have a zeroth row, and a zeroth column.

We might want to add option base 1, depending on what you're doing.

Here, if we're trying to message box the 2,2 position,

it really doesn't matter if we have that zeroth row [COUGH] and the zeroth column,

so let's go ahead and finish this up.

And then we message box the position 2,2, which corresponds to 2,2 of our selection.

Now I've modified this instead of using selection,

I've replaced selection with Range A1 to C3.

So we can go through this and we get the exact same thing.

We define our A array down here just as we did before.

Now another slightly better way to do this,

depending upon what your doing is to dim A as a variance, and

in that case, then I can delete all of this stuff.

The for loops, the counting the number of rows and columns.

I can also eliminate all of this and

I can just use, I can say A = Selection.

And now when I start with a selection over here and I go ahead and step through this,

it's going to bring in the selection and define it as a new variable A.

You notice down here in the Locals window, that when you do this, it doesn't

give you the zeroth row or the zeroth column, it just give you A with the 3x3.

So, A is a 3x3, and then we can message box the 2,2 position.

So this is a pretty efficient way just to bring in elements from the spreadsheet.

And you could also do this as range A1 to C3.

So you could run through this, and bring it in.

You could expand it down here if you wanted to,

and we could message box the 2,2 positions.

You can also do this with a selection so we can just A = Selection.

We go through this and we do the same thing where we assign the selection

to array A, and then we can message box something like the 2,2 position.

So now what I wanted to show you is how we can export an array from VBA to Excel,

so I'm just making A a 3x3 array, full of integers.

And I'm just going to implement a for loop, just so

we can define what A is, A, i, j is going to be = to 2 times i + j,

so we're going to go through, we're going to create our array A.

And then we're going to export that.

And since I've already got this nested for loop setup, I'm just going to go ahead and

put this in the nested for loops here, we're going to export this.

So I'm just going to say range B2 to D4.Cells i, j = i,j.

So this is then exporting A to the spreadsheet to region B2 to D4.

So let's go ahead and run this so I go through here.

Now keep in mind, that I didn't use that option base 1, so

we really have a zeroth row and a zeroth column.

But this is a situation in which it doesn't really matter if we

have a zeroth row and zeroth column.

So I'm going to keep going here, you notice that when i was 1 and

j was 1, I put into range B2 to D4.Cells 1,1 that corresponds to B2,

I put the value that I created, so A of 1,1.

A of 1,1 is down here in the Locals window, you can see that position.

So now I can go to the next position, and that is the value of 4, and

we can keep going and going, and we put those values in there.