"visual basic excel comma separated list from cells" Code Answer's

visual basic excel comma separated list from cells

By Jakes_Bakes_Code on Aug 14, 2020

' 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

Add Comment