visual basic excel comma separated list from cells
' NOTE: This is for MS Excel specifically
'===============================================================================
'>> MakeCommaSeparatedList(Optional rValues As Range)
'===============================================================================
' Makes a single line of comma separated values from all the cells
' Order: (a) left-to-right; (b) top-to-bottom
'===============================================================================
Public Function MakeCommaSeparatedList(Optional rValues As Range)
Dim sFunct As String: sFunct = "MakeCommaSeparatedList"
Dim bDebugging As Boolean: bDebugging = True
'*********************************
' VALIDATIONS and declarations
'*********************************
'(DECLARATIONS)
Dim wbInit As Workbook: Set wbInit = ActiveWorkbook
Dim wsInit As Worksheet: Set wsInit = ActiveSheet
Dim s_rInit As String: s_rInit = Selection.Address
Dim sErrMsg As String
'On Error GoTo ErrHandling
'(SETTINGS/SETUP)
Application.ScreenUpdating = False
Dim sCommaLst As String: sCommaLst = ""
Dim bFirst As Boolean: bFirst = True
'(VALIDATIONS)
'A) None... yet
If (rValues Is Nothing) Then
Set rValues = Selection
End If
If (bDebugging = True) Then
Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
& "Running.. [rValues:" & rValues.Address & "]"
End If
' To force an error:
' sErrMsg = sErrMsg & vbNewLine _
' & "Error message here."
' Err.Raise -1
'---------------------------------
' WORK
'---------------------------------
'1) Enter the cell value, followed by a comma, into the comma list
'Z) Reactivate the initial workbook/worksheet
'--(1)
For Each cell In rValues
If (bFirst = True) Then
sCommaLst = sCommaLst & cell.Value
Else
sCommaLst = sCommaLst & "," & cell.Value
End If
bFirst = False
Next
'--(Z)
wbInit.Activate
wsInit.Activate
Range(s_rInit).Select
'-----------v-----------DEBUG INFO-----------v-----------
MakeCommaSeparatedList = sCommaLst
If (bDebugging = True) Then
Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
& "Returning [CommaList:" & MakeCommaSeparatedList & "]"
Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
& "Complete [if not debugging, make bDebugging = false]"
End If
Application.ScreenUpdating = True
Exit Function
ErrHandling:
Application.ScreenUpdating = True
Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
& " -> Failed"
MsgBox _
Title:="Errors in the function: " & sFunct _
, Prompt:=Err.Description _
& vbNewLine & sErrMsg _
, Buttons:=vbCritical
End Function