Public Sub ListStrToArray(ByVal sOrigStr As String, ByRef sArray() As String, Optional lCols As Long, Optional lRows As Long)
' ' This routine gets a string in the format ' ' Item 1 | item 2 | item 3 | item 4 | ' ' And returns an array of these items (the | is a pipe symbol) ' Dim nRows As Long Dim lPos As Long Dim lPos2 As Long Dim bOver As Boolean Dim lTRowCount As Long Dim lTColCount As Long Dim nCols As Long Dim lCount As Long On Error GoTo vbErrorHandler If lRows > 0 Then nRows = lRows Else nRows = ItemCount(sOrigStr, "|") End If If lCols > 0 Then nCols = lCols Else nCols = 1 End If ReDim sArray(nCols, nRows) lPos = 0 lCount = 0 lTRowCount = 1 lTColCount = 1 Do lPos2 = InStr(lPos + 1, sOrigStr, "|") If lPos2 = 0 And lPos = 0 Then lCount = 1 Exit Do Else If lPos2 = 0 Then If Mid$(sOrigStr, lPos + 1, 1) <> "" Then lCount = lCount + 1 lPos2 = Len(sOrigStr) + 1 sArray(lTColCount, lTRowCount) = Mid$(sOrigStr, lPos + 1, lPos2 - lPos - 1) Exit Do Else Exit Do End If Else sArray(lTColCount, lTRowCount) = Mid$(sOrigStr, lPos + 1, lPos2 - lPos - 1) 'lTRowCount = lTRowCount + 1 lTColCount = lTColCount + 1 If lTColCount > nCols Then lTColCount = 1 lTRowCount = lTRowCount + 1 End If End If End If lCount = lCount + 1 lPos = lPos2 Loop Exit Sub vbErrorHandler: Err.Raise Err.Number, "StrHandler.ListStrToArray", Err.Description End Sub |