Set Array equal to Range

[RyanH]

I currently have an array of userform control values. I need to list these
controls values in a column. I currently use a For...Next Loop to list the
elements, but I would like to not use a loop. The could I have below doesn't
work, why?

Option Base 1

Sub Test()

Dim aryControls As Variant

aryControls = Array(Control1, Control2, Control3, etc.)
Sheets("Data Storage").Range(Cells(1, lngColumn), Cells(UBound(ctlArray),
lngColumn)).Value = ctlArray

End Sub

 

[RB Smissaert]

To write an array to a range the array will have to be a 2-D array and yours
is a 1-D array.
Why worry about the For Next loop? Your array is very small, so
performance-wise it won't make a difference.

RBS

 

[RyanH]

I really would like to learn how. The Loop works great, but I am trying to
learn to work with Array a little more. Is there a way to make it a
2-dimensional Array and make this code work?
--
Cheers,
Ryan

To write an array to a range the array will have to be a 2-D array and yours
is a 1-D array.
Why worry about the For Next loop? Your array is very small, so
performance-wise it won't make a difference.

RBS

 

[RB Smissaert]

Well, you can dim a 2-D array, loop through your 1-D array and copy to the
2-D array, but you won't gain much there.

RBS

I really would like to learn how. The Loop works great, but I am trying to
learn to work with Array a little more. Is there a way to make it a
2-dimensional Array and make this code work?

 

[RyanH]

So there is no way to set a Variant Variable to a 2D Array? For example,

ctrlArray = Array(Array(Control1, Control2, etc.),Array(""))

 

[RB Smissaert]

Don't think so.

RBS

So there is no way to set a Variant Variable to a 2D Array? For example,

ctrlArray = Array(Array(Control1, Control2, etc.),Array(""))

 

[Dana DeLouis]

but I would like to not use a loop.

Hi. These are not controls, but does this idea help?

Sub Demo()
Dim v
v = Array(11, 12, 13)

Range("B1").Resize(3) = WorksheetFunction.Transpose(v)
End Sub

- - -
HTH
Dana DeLouis