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
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)

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.