User-defined function to split a 1D array into 2D array

Question

The purpose of the UDF is to receive a 1D array and split it into a 2D array. The size of the new array should be dynamic.

Function SPLITARR(ByRef v() As Variant, MaxRow As Integer) As Variant
 Dim ArraySize As Integer
 Dim MaxCols As Integer
 Dim NewArray() As Variant
 Dim x As Integer, y As Integer, z As Integer
 ArraySize = (UBound(v(), 1) - LBound(v(), 1)) + 1
 MaxCols = ArraySize \ MaxRow
 If ArraySize Mod MaxRow > 0 Then MaxCols = MaxCols + 1
 ReDim NewArray(LBound(v(), 1) To MaxRow, 1 To MaxCols)
 For x = LBound(v(), 1) To UBound(v(), 1)
 y = x Mod MaxRow
 If y = 0 Then y = MaxRow
 z = x \ MaxRow
 If x Mod MaxRow = 0 Then z = z - 1
 NewArray(y, z + 1) = v(x, 1)
 Next
 SPLITARR = NewArray()
End Function

and should be called like;

Sub caller()
Dim a() As Variant
a() = Range("A4:A23")
a() = SPLITARR(a(), 5)
ActiveCell.Resize(UBound(a(), 1), UBound(a(), 2)).Value = a()
End Sub

Let range "A4:A23" have a value of (1,2,3,..20) and this call will return

1,6,11,16
2,7,12,17
3,8,13,18
4,9,14,19
5,10,15,20

Answer 1

"receive a 1D array and split it into a 2D array" - it's not true, it receives a 2d array of which 2nd dimension is 1. It won't work receiving an 1D or transposed 2D array. (it probably serves your purpose, just be clear with the description to avoid confusion in the future.

---

If ArraySize Mod MaxRow > 0 Then MaxCols = MaxCols + 1

Although this is correct syntax it's generally a bad practice to write IF in one line.

---

You can simplify your calculations:

y = x Mod MaxRow
If y = 0 Then y = MaxRow

=> y = ( (x - 1) Mod MaxRow) + 1

z = x \ MaxRow
If x Mod MaxRow = 0 Then z = z - 1

=> z = (x-1) \ MaxRow

(These will not work correctly for 0 index, however in your case arrays copied from Range always have index starting from 1.