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
Are there any code examples left?
New code examples in category BASIC
-
BASIC 2022-03-27 17:45:10 JWT EM VBNET
-
BASIC 2022-03-20 07:40:02 get unique random number in vb.net
-
BASIC 2022-03-11 21:30:23 visual basic for loop
-
BASIC 2022-03-05 08:25:09 token em vbnet incluido no header da requisição
-
BASIC 2022-03-03 04:25:02 dos/cmd equivalent to "head"
-
BASIC 2022-02-26 20:20:04 mid visual basic function
-
BASIC 2022-02-23 02:15:02 vscode unindent
-
BASIC 2022-02-18 16:20:01 freecodecamp basic algorithm scripting return largest numbers in arrays
-
BASIC 2022-02-10 15:10:40 visual basic how to create a dynamic button
-
BASIC 2022-02-04 20:30:23 tmux basic commands