Quick TTC Output
OK, one more post about Text to Columns, then I’ll quit. I have two basic outputs coming from this code. I need to populate a listbox (zero based array) and the destination range (one based array). I created the List property to spit out either based on the OneBased argument.
Public Property Get List(Optional OneBased As Boolean = False) As Variant
Dim i As Long, j As Long
Dim clsSplit As CSplit
Dim aReturn() As Variant
Dim lArr1 As Long, lArr2 As Long
Dim lColCnt As Long
Dim lOffset As Long
Dim lLower As Long
Const sSOURCE As String = "List()"
On Error GoTo ErrorHandler
lOffset = CLng(OneBased) + 1
lLower = Abs(CLng(OneBased))
lArr1 = mcolSplits.count - lOffset
lArr2 = Me.ColumnCount - lOffset
ReDim aReturn(lLower To lArr1, lLower To lArr2)
For i = LBound(aReturn, 1) To UBound(aReturn, 1)
Set clsSplit = mcolSplits.Item(i + lOffset)
lColCnt = clsSplit.Columns.count
If Not mbRightToLeft Then
For j = LBound(aReturn, 2) To UBound(aReturn, 2)
If j > clsSplit.Columns.count - lOffset Then Exit For
aReturn(i, j) = clsSplit.Columns.Item(j + lOffset)
Next j
Else
For j = UBound(aReturn, 2) To LBound(aReturn, 2) Step -1
If j < (lArr2 - clsSplit.Columns.count + 1) Then Exit For
aReturn(i, j) = clsSplit.Columns.Item(lColCnt)
lColCnt = lColCnt - 1
Next j
End If
Next i
List = aReturn
ErrorExit:
On Error Resume Next
Exit Property
ErrorHandler:
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Property
Dim i As Long, j As Long
Dim clsSplit As CSplit
Dim aReturn() As Variant
Dim lArr1 As Long, lArr2 As Long
Dim lColCnt As Long
Dim lOffset As Long
Dim lLower As Long
Const sSOURCE As String = "List()"
On Error GoTo ErrorHandler
lOffset = CLng(OneBased) + 1
lLower = Abs(CLng(OneBased))
lArr1 = mcolSplits.count - lOffset
lArr2 = Me.ColumnCount - lOffset
ReDim aReturn(lLower To lArr1, lLower To lArr2)
For i = LBound(aReturn, 1) To UBound(aReturn, 1)
Set clsSplit = mcolSplits.Item(i + lOffset)
lColCnt = clsSplit.Columns.count
If Not mbRightToLeft Then
For j = LBound(aReturn, 2) To UBound(aReturn, 2)
If j > clsSplit.Columns.count - lOffset Then Exit For
aReturn(i, j) = clsSplit.Columns.Item(j + lOffset)
Next j
Else
For j = UBound(aReturn, 2) To LBound(aReturn, 2) Step -1
If j < (lArr2 - clsSplit.Columns.count + 1) Then Exit For
aReturn(i, j) = clsSplit.Columns.Item(lColCnt)
lColCnt = lColCnt - 1
Next j
End If
Next i
List = aReturn
ErrorExit:
On Error Resume Next
Exit Property
ErrorHandler:
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Property
The usage is:
Me.Listbox1.List = clsSplits.List
Sheet1.Range(A1:D10").Value = clsSplits.List(True)
Sheet1.Range(A1:D10").Value = clsSplits.List(True)
