Splits ANStrArray into 2-Dimension array
Using custom row and column separators, the function will return the 2 Dim array that was passed as argument

Function SplitTo2DArray(ANStrArray, sRowSep, sColSep, toArray)
 Dim vasValue
 Dim nUBoundValue
 Dim avasCells()
 Dim nRowIndex
 Dim nMaxUBoundCells
 Dim nUBoundCells
 Dim asCells()
 Dim nColumnIndex
 ' Split up the table value by rows, get the number of rows, and dim a new array of Variants.
 vasValue = Split(ANStrArray, the_sRowSep)
 nUBoundValue = UBound(vasValue)
 ReDim avasCells(nUBoundValue)
 ' Iterate through each row, and split it into columns. Find the maximum number of columns.
 nMaxUBoundCells = 0
 For nRowIndex = 0 To nUBoundValue
  avasCells(nRowIndex) = Split(vasValue(nRowIndex), sColSep)
  nUBoundCells = UBound(avasCells(nRowIndex))
  If nUBoundCells > nMaxUBoundCells Then nMaxUBoundCells = nUBoundCells
 ' Create a 2D string array to contain the data in <avasCells>.
 ReDim asCells(nUBoundValue, nMaxUBoundCells)
 ' Copy all the data from avasCells() to asCells().
 For nRowIndex = 0 To nUBoundValue
  For nColumnIndex = 0 To UBound(avasCells(nRowIndex))
   asCells(nRowIndex, nColumnIndex) = avasCells(nRowIndex)(nColumnIndex)
 toArray = asCells
End Function


ANStrArray, sRowSep, sColSep, toArray

Views 137 Downloads 56

Perm link