Passing a range into array 1

[osx99]

I'm trying to create an array with 4 dimensions to read in a combination of 4 non-contigous ranges. However, the attached only creates a 2 dimension array

Please can someone advise how to create a 4 dim array? Essentially I want to pass in 3 ranges, make a calculation and pass result into the 4th dimension then paste result back into excel range.

I need this to be fast so I'm trying to avoid looping through each range seperatley. (i.e read all ranges to array in 1 pass then pass result back into range)

  Sub test()
  
  Set varRangeSelect1 = Range(Cells(2, 1), Cells(14, 1))
  Set varRangeSelect2 = Range(Cells(2, 3), Cells(14, 3))
  Set varRangeSelect3 = Range(Cells(2, 4), Cells(14, 4))
  Set varRangeSelect4 = Range(Cells(2, 9), Cells(14, 9))
  Set varRangeSelect5 = Union(varRangeSelect1, varRangeSelect2, varRangeSelect3,varRangeSelect4)
'varRangeSelect5.Select
    Dim strArray As Variant, rng As Range
    Set rng = varRangeSelect5
    strArray = rng
    For i = 1 To UBound(strArray)
    strArray(i, 4) = strArray(i, 1) - strArray(i, 3)
    Next i
    Range("f5:f55").Value = Application.WorksheetFunction.Index(strArray, 0, 4)
  End Sub

Many thanks,
Os

 

[combo]

You can only assign 2D range to 2D variant array:
varArray=rngRange
Finally, you will work with 4 input variant vectors (with 1 of second dimension), that's not so bad.
To speed up things:
- use a variable to hold the size,
- avoid referring to excel (WorksheetFunction),
- consider rearrange input range into one 2D range,
- pass back output result in one go:
rngOutputRange=varOutputArray.


combo