How to assign part of an array to another shorter array in vba?

Question

i have a larger array of about 8 elements. I want to assign it to another smaller array of 3 elements. How do i do this? This is my code, but i keep getting an error, "Cant assign to array" Thanks for your help.

Dim largeArr() as variant
largeArr = Array(7, 8, 9, 10, 11, 12, 13, 14)

Dim smallArr(3) As Variant

smallArr=largeArr()

score 1 · Accepted Answer · edited May 23 '17 at 11:51

To subset an array, you could feed the small array with individual values of the large one by looping through the desired section.

Sub test()
Dim largeArr(), smallArr(1 To 3) As Variant

largeArr = Array(7, 8, 9, 10, 11, 12, 13, 14)

For x = 1 To 3
  smallArr(x) = largeArr(x + 5)
  Debug.Print smallArr(x) 'Print small array in the Immediate Window
Next x

End Sub

In the loop, adjust the i in largeArr(x+i) to reach the desired section of the large array.

See also How do I slice an array in Excel VBA? .

How to assign part of an array to another shorter array in vba?

1 Answers1