Array question

[Greg Snidow]

Greetings all. I tried to post this question earlier, but I don't see it
now, so if it shows up twice, I apologize. If I have a range, say three
columns by 10 rows of all numerical data, can I populate that range into a
array variable, and use it somewhere else? I read through many posts here
about arrays, but I have never used them, did not know they existed, so I am
just looking for a very basic explanation of how to do it. Maybe a link to
a good article? Thank you.

Greg

 

[OssieMac]

Hi Greg,

In the following example it assigns the range values to an array then uses
msgbox to read the values from the 2 dimensional array.

Ensure you use ".Value" on the end of the range when assigning it or it does
not work.

Sub RangeToArray()

Dim arrMyArray()
Dim i As Long
Dim j As Long

With Sheets("Sheet1")
arrMyArray = .Range("A1:C10").Value
End With

'Number elements down (first dimension)
For i = 1 To UBound(arrMyArray)

'Number elements across (second dimension)
For j = 1 To UBound(arrMyArray, 2)
MsgBox arrMyArray(i, j)
Next j

Next i

End Sub

 

[OssieMac]

Also Google (or other favourite search engine) "excel vba array tutorial"
and you should find some more info.

 

[Greg Snidow]

OssieMac, thank you for the elementary explanation. The reason I'm
interested in arrays is I have many workbooks that copy and paste ranges
inside of loops, so the ranges change with each i, so the screen is jumping
all over the place, and it just looks bad. So, I am gathering that once I
Dim the array, say MyArray(), and I put it in a loop where for each i,
MyArray = SomeVariableRange, the values for the new range will overwrite the
existing values in the array from the previous i? I tried it out on a loop
that copies a solver solution range for 70 runs, and populates a report
section. The data seemed to be no different than if I had copied and pasted
the ranges. Am I missing something, or is this how they are supposed to
work? Thanks again.

Greg

 

[Rick Rothstein]

The screen "jumping all over the place" problem is probably due to your
continually changing what is selected. You rarely have to select a range
(single or multiple cells) in order to operate on them. Here is how I have
explained it in the past...

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

 

[Rick Rothstein]

Oh, I meant to include the following in my other post...

You can stop the screen jumping problem by freezing the screen during
processing. Put this statement at the beginning of your code...

Application.ScreenUpdating = False

and then put this next statement at the end of your code...

Application.ScreenUpdating = True

Note that if your code crashes (errors out), then the second statement above
will not have executed and your Excel screen will be frozen. If that
happens, you can "unfreeze" it by executing the second statement above
directly in the Immediate Window. If you have any error trapping enabled and
if that code ends the execution of your code, then you should include this
statement within that error trapping code so that it will "unfreeze" the
screen automatically for you.

 

[Greg Snidow]

Rick, thanks for the tips. I think I actually read the post you referenced,
and so I am not selecting any ranges, but rather just pasting into them
without selecting them as your post suggests. However, every time I copy a
range, the blinking dashed lines appear around the range, then jump to the
next range I'm copying, then it jumps down to the range to which I am
writing, even though I am not selecting. Is there a way to avoid
Range("somerange").copy? The arrays seem to do the trick. My screen is
almost perfectly still, while the values being passed from the array just
populate quietly.

 

[Greg Snidow]

Also, the macro in question is running solver against a variable set of data
70 times. It took anywhere from 28 seconds to 35 seconds when I was doing
copy and paste. Using arrays instead has brought it down to between 16 and
20 seconds. Now, I will say that my laptop's performance seems to be
dependent upon how it is feeling on any particular day. All I can say is by
taking out the copy and paste part, I am seeing performance not seen with
this macro.

 

[Rick Rothstein]

Since you didn't post any code, it is hard to tell what you are doing, but I
suspect you are doing a Copy in one line and then a PasteSpecial in another
line. If you don't mind copying everything from the source range (that would
be formula, values, formats, etc.), then you can do this in a single line of
code. Say your source range is C3:H9 on Sheet1 and you wanted to copy this
to a destination whose top left cell is M12 on Sheet2, then you could do
like this without using any arrays...

Worksheets("Sheet1").Range("C3:H9").Copy Worksheets("Sheet2").Range("M12")

By the way, did you try my Application.ScreenUpdating suggestion with your
original code? That should have hidden the blinking dashed lines during your
codes execution.

 

[Greg Snidow]

I was doing copy the source row, then pastespecial paste:=values only. Like
I said, the laptop I use at work is pretty hurtin'. I did try the
screenupdate line, and it worked beautifully. It actually shaved another two
or three seconds off the time. One thing I am noticing, though, is that even
though this laptop, with a single core 1.6g processor, 2G of ram, and Excel
2003 is slow by today's standards, my personal laptop has a dual core 1.8g
processor, and 4G of ram with Excel 2007, and the same macro takes twice as
long to run on the newer machine. Could this be that Excel is not programmed
to benifit from multi-threading?