How do I assign range to variant and use

[Mike H]

I want to work at increasing the speed of macros that work with large
ranges.

I'm told that I can DIM a variable as VARIANT and assign a range to it.
So far so good, but how can that be used? That is, how can maniulate
individual elements in that array (if it's correct to call it an array)?

Here's the stub I'm working with. Note that I'm simply trying to figure
out how these prodcedures work.

The data in A1:E1 is 1,2,3,4,5. I want to change the value 1 to 2 and
write a range of cells back to A2:E2 that looks like 2,2,3,4,5 (so I
know that I actually did something!)

Sub TestVariant

Dim RgArray as Variant
Dim LowBound as Integer, Highbound as Integer
Dim MyArray() As Single

RgArray = Application.Range("A1:E1")
LowBound = LBound(RgArray, 2)
HighBound = UBound(RgArray, 2)

ReDim MyArray(LowBound To HighBound)

'The next statement DOES NOT WORK and produces an error!
'What I'm trying to do is load all the values in RgArray into the local
'array, manipulate the data, and write it back.

MyArray = RgArray 'Does not work

MyArray(1) = 2

'What follows works, but since my array is all zeros at this
'point, except for MyArray(1) (because the MyArray = RgArray
'didn't work), I'm not going to get the result I want. Of course,
'I'm getting 2,0,0,0,0.

RgArray = MyArray
Range ("A2:E2").Value = Rgarray

End Sub

I'm missing something simple but essential here but I can't find it.
What am I missing?

 

[Dave Peterson]

Maybe this will give you some ideas:

Option Explicit
Sub TestVariant()
Dim RgArray As Variant

RgArray = ActiveSheet.Range("A1:E1").Value
'rgarray is now a 1 row by 5 column array

'first row, first column is gonna be 2
RgArray(1, 1) = 2

'use one cell and resize it to match the array size that you picked up
ActiveSheet.Range("a2") _
.Resize(UBound(RgArray, 1) - LBound(RgArray, 1) + 1, _
UBound(RgArray, 2) - LBound(RgArray, 2) + 1).Value _
= RgArray
End Sub

I could have used:

ActiveSheet.Range("a2") _
.Resize(UBound(RgArray, 1), _
UBound(RgArray, 2)).Value _
= RgArray


But it could be a problem if I copy the code and my new array isn't 1 based.

 

[Mike H]

Oh, I see! I tried what you pointed out and it worked precisely as I
wanted. I see that what I was missing is that the variant array, at
least in this case, is actually a 2-dimensional array (even though the
number of rows is 1). So I refer to the elements in this array as (1,2),
(1,2) ... (1,5). Hopefully I've restated what you explained to me
correctly.

Very cool. Thanks for passing that along, Dave.

 

[Dave Peterson]

Yep.

And one way to convert a 2 dimensional array (1 row by multiple columns) into a
1 dimensional array:

RgArray = ActiveSheet.Range("A1:E1").Value
'rgarray is now a 1 row by 5 column array

With Application
RgArray = .Transpose(.Transpose(RgArray))
End With

And one way to convert a 2 dimensional array (multiple rows by 1 column):

RgArray = ActiveSheet.Range("A1:a5").Value
With Application
RgArray = .Transpose(RgArray)
End With

======
Depending on your version of excel, application.transpose will fail if there are
more than 5461 (IIRC) elements. (xl2k and below will fail and xl2002+ won't.)

 

[Mike H]

Very good stuff, Dave. Oh, and thanks for reminding me of clean ways to
state, what do you call it?, a structure, such as the one beginning with
"With Application".

Best to you...

Mike

 

[Dave Peterson]

I call it a "with" statement. But I'd understand if you'd call it a structure
<vbg>.

 

[Mike H]

I appreciate your humor

Hope to cross your path again sometime, Dave.

Regards,
Mike

 

[Dave Peterson]

Words that you'll come to regret!

<vvbg>

I appreciate your humor

Hope to cross your path again sometime, Dave.

Regards,
Mike