A short post today of a simple little utility procedure that I quickly put together to solve a punctual need that I thought I’d share.
Have you ever been given a linear list of items, could be comma separated, and needed to convert the list into a line item style of list? Now for a 3-4 item list , no big deal, you can quickly manually make the change, but when the list grows into the dozens, hundreds, … then it can become tedious and time consuming.
Well, that’s the scenario I was facing earlier this week, and I created the following, very simple, sub to do the work for me.
'---------------------------------------------------------------------------------------
' Procedure : BreakListIntoLineItems
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Take a delimited list of items and break it into line items
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sList : List to split into line items
' sDelim : Item delimiter character(s), if not specified a comma will be assumed/used
'
' Usage:
' ~~~~~~
' BreakListIntoLineItems("My Book, My Book for Mac, My Book Duo, My Book")
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2020-09-23 Initial Release
'---------------------------------------------------------------------------------------
Public Sub BreakListIntoLineItems(ByVal sList As String, _
Optional sDelim As String = ",")
Dim aList
Dim i As Long
On Error GoTo Error_Handler
aList = Split(sList, sDelim)
For i = 0 To UBound(aList)
Debug.Print Trim(aList(i))
Next i
Error_Handler_Exit:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: BreakListIntoLineItems" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Then with this in hand you can call the function by simply doing
BreakListIntoLineItems("My Book, My Book for Mac, My Book Duo, My Book, WD easystore Desktop, My Book Duo, My Passport Wireless, My Passport Ultra Metal, My Passport Ultra (WD Backup), My Passport for Mac, My Passport Wireless Pro, My Passport, My Passport for Mac, WD easystore Portable, My Passport SSD, My Passport Ultra, My Passport Wireless SSD, My Passport Ultra (USB-C), My Passport Ultra for Mac (USB-C), My Passport Go")
and it will return the line item list in the immediate window such as:
My Book My Book for Mac My Book Duo My Book WD easystore Desktop My Book Duo My Passport Wireless My Passport Ultra Metal My Passport Ultra (WD Backup) My Passport for Mac My Passport Wireless Pro My Passport My Passport for Mac WD easystore Portable My Passport SSD My Passport Ultra My Passport Wireless SSD My Passport Ultra (USB-C) My Passport Ultra for Mac (USB-C) My Passport Go
