How to Create Subarray Efficiently in VBA?
excel, vba
Solution
Working with arrays is incredibly fast so this will probably give no discernable benefit - althouh I can understand how it may appeal from a coding sense than looping to fill a smaller array
Given you are working with a single element array you could:
- Introduce a "marker" string inside the large array
- `Join` the large array with a delimiter into a single string
- `Split` the large array by the "marker" string, then separate the reduced string into a smaller array with the delimiter
The code below dumps the numbers 1 to 100 into an array, and then splits it as above to pull out the first 10 records
Sub test()
Dim bigArr
Dim subArr
Dim strSep As String
Dim strDelim As String
Dim strNew As String
Dim rowBegin As Long
Dim rowEnd As Long
strDelim = ","
strSep = "||"
'fill array with 1 to 100
bigArr = Application.Transpose(Application.Evaluate("row(1:100)"))
rowBegin = 1
rowEnd = 10
bigArr(rowEnd + 1) = strSep
'make a single string
strNew = Join(bigArr, strDelim)
'split the string at the marker
vArr = Split(strNew, strSep)
ReDim subArr(rowBegin To rowEnd)
'split the smaller string with the desired records
subArr = Split(Left$(vArr(0), Len(vArr(0)) - 1), strDelim)
End Sub
Problem
In my VBA program, I have a big array of data, where I need to constantly use its sub-arrays. My method is: ``` Redim subArr(rowBegin to rowEnd) For r = rowBegin to rowEnd subArr(r) = bigArr(r) Next r ``` Is there any more efficient way to reference this kind of sub-arrays please? Thanks...