Stringize A Range
Public Function Stringize_A_Range(RngInQuestion As Range, Optional StrDelimiter As Variant, Optional BolPrefixDelimit As Variant, Optional BolPostfixDelimit As Variant) As String
On Error GoTo Stringize_A_Range_ErrorHandler Dim RngArea As Range Dim RngCell As Range Dim StrResult As String
‘====================================================================================================
‘Iff the supplied range is in fact NOTHING then return a NULL STRING and exit
‘====================================================================================================
If RngInQuestion Is Nothing Then
____Stringize_A_Range = “”
____Exit Function
End If
‘====================================================================================================
‘Iff no delimiter was supplied then set the default delimiter to a comma
‘====================================================================================================
If IsMissing(StrDelimiter) Then
____StrDelimiter = “,”
End If
‘====================================================================================================
‘Iff no PREFIXING delimiter was supplied then set the default to FALSE ie do NOT prefix the result string
‘====================================================================================================
If IsMissing(BolPrefixDelimit) Then
____BolPrefixDelimit = False
End If
‘====================================================================================================
‘Iff no POSTFIXING delimiter was supplied then set the default to FALSE ie do NOT postfix the result string
‘====================================================================================================
If IsMissing(BolPostfixDelimit) Then
____BolPostfixDelimit = False
End If
‘====================================================================================================
‘Iff the result string must be prefixed then prefix same
‘====================================================================================================
If BolPrefixDelimit Then
____StrResult = StrDelimiter
End If
‘====================================================================================================
‘Cycle through each and every CELL in each and every AREA of the supplied range and assemble the result string
‘====================================================================================================
For Each RngArea In RngInQuestion.Areas
____For Each RngCell In RngArea.Cells
________StrResult = StrResult & RngCell.Value & StrDelimiter
____Next RngCell Next RngArea
‘====================================================================================================
‘Iff the result string must be postfixed then postfix same
‘====================================================================================================
If Not BolPostfixDelimit The
____StrResult = Left(StrResult, Len(StrResult) – Len(StrDelimiter))
End If
‘====================================================================================================
‘Return the result and exit the function
‘====================================================================================================
Stringize_A_Range = StrResult
Exit Function
‘====================================================================================================
‘At this juncture the result has NOT been arrived due to an error being raised now return the default error result & exit
‘====================================================================================================
Stringize_A_Range_ErrorHandler:
____Stringize_A_Range = “”
End Function
