Cleaning Up My JoinRange Arguments

Posted on June 22, 2016 by Dick Kusleika

I’m trying to make my JoinRange function better and I’m failing miserably. A few years ago I added a “macro” argument because I was making so many HTML and Trac tables. I don’t use Trac anymore and I almost never make HTML tables (because I blog so infrequently, I guess). I got rid of that argument. The reason I join ranges most often is to create a big

In

clause in SQL. Let’s say I have this list of customer IDs and I want to make an

In

clause.

142

146

175

214

217

I’d use JoinRange like

=JoinRange(A2:A7,,"','","('","')")

1	=JoinRange(A2:A7,,"','","('","')")

That’s a freakin’ mess. The second argument is the now-defunct macro argument and is blank. The rest of the arguments are

3rd (delimeter): single quote, comma, single quote
4th (beginning): open paren, single quote
5th (ending): single quote, close paren

and I’d get

('38','142','146','175','214','217')

1	('38','142','146','175','214','217')

which I could paste into my SQL statement and roll. I hate typing those arguments. Worse, I hate reading those arguments. It’s pretty hard to read in this blog, but it’s worse in Excel’s formula bar. I thought if I could get rid of the single quotes, it would be cleaner. I rewrote the code to add a Quote argument that would wrap every entry in whatever quotes I supplied.

Public Function JoinRange(rInput As Range, _
    Optional sDelim As String = vbNullString, _
    Optional sLineStart As String = vbNullString, _
    Optional sLineEnd As String = vbNullString, _
    Optional sBlank As String = vbNullString, _
    Optional sQuotes As String = vbNullString) As String
    
    Dim vaCells As Variant
    Dim i As Long, j As Long
    Dim lCnt As Long
    Dim aReturn() As String
    
    vaCells = rInput.Value
    ReDim aReturn(1 To rInput.Cells.Count)
    
    For i = LBound(vaCells, 1) To UBound(vaCells, 1)
        For j = LBound(vaCells, 2) To UBound(vaCells, 2)
            lCnt = lCnt + 1
            If Len(vaCells(i, j)) = 0 Then
                aReturn(lCnt) = sQuotes & sBlank & sQuotes
            Else
                aReturn(lCnt) = sQuotes & vaCells(i, j) & sQuotes
            End If
        Next j
    Next i
    
    JoinRange = sLineStart & Join(aReturn, sDelim) & sLineEnd
    
End Function

Public Function JoinRange(rInput As Range, _

Optional sDelim As String = vbNullString, _

Optional sLineStart As String = vbNullString, _

Optional sLineEnd As String = vbNullString, _

Optional sBlank As String = vbNullString, _

Optional sQuotes As String = vbNullString) As String

Dim vaCells As Variant

Dim i As Long, j As Long

Dim lCnt As Long

Dim aReturn() As String

vaCells = rInput.Value

ReDim aReturn(1 To rInput.Cells.Count)

For i = LBound(vaCells, 1) To UBound(vaCells, 1)

For j = LBound(vaCells, 2) To UBound(vaCells, 2)

lCnt = lCnt + 1

If Len(vaCells(i, j)) = 0 Then

aReturn(lCnt) = sQuotes & sBlank & sQuotes

Else

aReturn(lCnt) = sQuotes & vaCells(i, j) & sQuotes

End If

Next j

Next i

JoinRange = sLineStart & Join(aReturn, sDelim) & sLineEnd

End Function

Now, my formula looks like this:

=JoinRange(A2:A7,",","(",")",,"'")

1	=JoinRange(A2:A7,",","(",")",,"'")

I think we can all agree that this is no better than what I had before. I thought the quotes were the problem, but it’s also that I use a comma as the delimiter and it’s the thing that separates the arguments. If I change it to pipe delimited…

=JoinRange(A2:A7,"|","(",")",,"'")

1	=JoinRange(A2:A7,"\|","(",")",,"'")

Nope. It’s still a headache to read. Based on the number of comments to this post, I’m pretty sure none of you are using predefined names in your book.xlt file. But I do. And If I’m using a workbook that I created, I could use

=JoinRange(A2:A7,xlCOMMA,"(",")",xlSINGLE)

1	=JoinRange(A2:A7,xlCOMMA,"(",")",xlSINGLE)

That’s definitely more readable to me. I guess I need a macro to add those names to any books automatically so I can use them.

Public Sub AddConstantNames()
    
    ActiveWorkbook.Names.Add "xlCOMMA", "="","""
    ActiveWorkbook.Names.Add "xlSPACE", "="" """
    ActiveWorkbook.Names.Add "xlDOUBLE", "="""""
    ActiveWorkbook.Names.Add "xlSINGLE", "=""'"""
    ActiveWorkbook.Names.Add "xlPARENO", "=""("""
    ActiveWorkbook.Names.Add "xlPARENC", "="")"""
    ActiveWorkbook.Names.Add "xlPIPE", "=""|"""
    
End Sub

Public Sub AddConstantNames()

ActiveWorkbook.Names.Add "xlCOMMA", "="","""

ActiveWorkbook.Names.Add "xlSPACE", "="" """

ActiveWorkbook.Names.Add "xlDOUBLE", "="""""

ActiveWorkbook.Names.Add "xlSINGLE", "=""'"""

ActiveWorkbook.Names.Add "xlPARENO", "=""("""

ActiveWorkbook.Names.Add "xlPARENC", "="")"""

ActiveWorkbook.Names.Add "xlPIPE", "=""|"""

End Sub

=JoinRange(A2:A7,xlCOMMA,xlPARENO,xlPARENC,xlSINGLE)

1	=JoinRange(A2:A7,xlCOMMA,xlPARENO,xlPARENC,xlSINGLE)

I’m not crazy. I swear this all makes sense in my head. Plus, if you’ve read this far, you’re probably crazy too.

9 thoughts on “Cleaning Up My JoinRange Arguments”

Donnie says:

June 23, 2016 at 6:10 am

I also have a macro for this purpose. I do not include the open and close parentheses as parameters. I find it makes the formula much easier to read and type. I then just type them in my SQL ide before pasting the result.

Or you could do the parentheses as an optional parameter you just set as true
Dick Kusleika says:

June 23, 2016 at 1:45 pm

I use it for more than just SQL, so my beginning and ending aren’t always parens. Like the table in this post was built using

Visual Basic

=JoinRange(A2:A7,"</td><td>","<tr><td>","</td></tr>")

1

=JoinRange(A2:A7,"</td><td>","<tr><td>","</td></tr>")

I could just leave them off and add them after I paste, but if I’m doing a bunch of rows, that stinks.
Charles Urban says:

June 23, 2016 at 4:58 pm

Assuming the items in range A1:A7 I use these formulas in B2:B7.
38 =”‘”&A2&”‘”
142 =B2&”, ‘”&A3&”‘”
146 =B3&”, ‘”&A4&”‘”
175 =B4&”, ‘”&A5&”‘”
214 =B5&”, ‘”&A6&”‘”
217 =B6&”, ‘”&A7&”‘”

then I cut and past the last one into my “IN” clause.
I call the spreasheet “Marks.” I open it when I beed ti create my in clause. I use a variation of this to create a Values list.

I think we’ve all been doing this for years, and most of us have a function for concatenating a SQL IN() clause:

Public Function InClause(Items As Variant, Optional EncapsulatingQuotes As String = "'") As String
Application.Volatile False

' Returns a comma-separated string containing each nonblank item
' "IN (1,2,3,4,5)"
' "IN ('A', 'B', 'C', 'D', 'E')

' Nigel hefferna , 2009. THIS CODE IS IN THE PUBLIC DOMAIN


Dim i As Long
Dim j As Long
Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long

Dim arrItem(0 To 2) As String
Dim arrList()       As String
Dim varList         As Variant
Dim dicList         As Scripting.Dictionary

Const DQUOTE As String * 1 = """"
Const SQUOTE As String * 1 = "'"
Const COMMA  As String * 1 = ","

Select Case EncapsulatingQuotes
Case Chr(34)
    arrItem(0) = DQUOTE
    arrItem(1) = vbNullString
    arrItem(2) = DQUOTE
Case Chr(39)
    arrItem(0) = SQUOTE
    arrItem(1) = vbNullString
    arrItem(2) = SQUOTE
Case vbTab
    arrItem(0) = vbTab
    arrItem(1) = vbNullString
    arrItem(2) = vbTab
Case Null, Empty, ""
    arrItem(0) = vbNullString
    arrItem(1) = vbNullString
    arrItem(2) = vbNullString
Case Else
    arrItem(0) = EncapsulatingQuotes
    arrItem(1) = vbNullString
    arrItem(2) = EncapsulatingQuotes
End Select

If TypeName(Items) = "Range" Then

    If Items.Cells.Count = 1 Then
        ReDim varList(1 To 1, 1 To 1)
        varList(1, 1) = Items.Value2
    Else
        varList = Items.Value2
    End If
    
ElseIf InStr(TypeName(Items), "(") < 1 Then
    
    ReDim varList(1 To 1, 1 To 1)
    varList(1, 1) = Items

Else

    Select Case ArrayDimensions(Items)
    Case 1
        ReDim varList(LBound(Items) To UBound(Items), 1 To 1)
        For i = LBound(Items) To UBound(Items)
            varList(i) = Items(i)
        Next i
    Case 2
        varList = Items
    Case Else
        Exit Function
    End Select

End If

i_lBound = LBound(varList, 1)
i_uBound = UBound(varList, 1)
j_lBound = LBound(varList, 2)
j_uBound = UBound(varList, 2)
    

Set dicList = New Scripting.Dictionary
    dicList.CompareMode = BinaryCompare

For i = i_lBound To i_uBound Step 1
    For j = j_lBound To j_uBound Step 1
        If IsError(varList(i, j)) Then
            'no action
        ElseIf Len(varList(i, j)) > 0 Then
            arrItem(1) = varList(i, j)
            dicList(arrItem(1)) = Join(arrItem, vbNullString)
        End If
    Next j
Next i

Erase varList

If dicList.Count = 0 Then
    InClause = " IN ( NULL ) "
Else
    arrItem(0) = " IN ( "
    arrItem(1) = Join(dicList.Items, COMMA)
    arrItem(2) = " ) "
    InClause = Join(arrItem, vbNullString)
End If

Erase arrItem
dicList.RemoveAll
Set dicList = Nothing

End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

Public Function InClause(Items As Variant, Optional EncapsulatingQuotes As String = "'") As String

Application.Volatile False

' Returns a comma-separated string containing each nonblank item

' "IN (1,2,3,4,5)"

' "IN ('A', 'B', 'C', 'D', 'E')

' Nigel hefferna , 2009. THIS CODE IS IN THE PUBLIC DOMAIN

Dim i As Long

Dim j As Long

Dim i_lBound As Long

Dim i_uBound As Long

Dim j_lBound As Long

Dim j_uBound As Long

Dim arrItem(0 To 2) As String

Dim arrList() As String

Dim varList As Variant

Dim dicList As Scripting.Dictionary

Const DQUOTE As String * 1 = """"

Const SQUOTE As String * 1 = "'"

Const COMMA As String * 1 = ","

Select Case EncapsulatingQuotes

Case Chr(34)

arrItem(0) = DQUOTE

arrItem(1) = vbNullString

arrItem(2) = DQUOTE

Case Chr(39)

arrItem(0) = SQUOTE

arrItem(1) = vbNullString

arrItem(2) = SQUOTE

Case vbTab

arrItem(0) = vbTab

arrItem(1) = vbNullString

arrItem(2) = vbTab

Case Null, Empty, ""

arrItem(0) = vbNullString

arrItem(1) = vbNullString

arrItem(2) = vbNullString

Case Else

arrItem(0) = EncapsulatingQuotes

arrItem(1) = vbNullString

arrItem(2) = EncapsulatingQuotes

End Select

If TypeName(Items) = "Range" Then

If Items.Cells.Count = 1 Then

ReDim varList(1 To 1, 1 To 1)

varList(1, 1) = Items.Value2

Else

varList = Items.Value2

End If

ElseIf InStr(TypeName(Items), "(") < 1 Then

ReDim varList(1 To 1, 1 To 1)

varList(1, 1) = Items

Else

Select Case ArrayDimensions(Items)

Case 1

ReDim varList(LBound(Items) To UBound(Items), 1 To 1)

For i = LBound(Items) To UBound(Items)

varList(i) = Items(i)

Next i

Case 2

varList = Items

Case Else

Exit Function

End Select

End If

i_lBound = LBound(varList, 1)

i_uBound = UBound(varList, 1)

j_lBound = LBound(varList, 2)

j_uBound = UBound(varList, 2)

Set dicList = New Scripting.Dictionary

dicList.CompareMode = BinaryCompare

For i = i_lBound To i_uBound Step 1

For j = j_lBound To j_uBound Step 1

If IsError(varList(i, j)) Then

'no action

ElseIf Len(varList(i, j)) > 0 Then

arrItem(1) = varList(i, j)

dicList(arrItem(1)) = Join(arrItem, vbNullString)

End If

Next j

Next i

Erase varList

If dicList.Count = 0 Then

InClause = " IN ( NULL ) "

Else

arrItem(0) = " IN ( "

arrItem(1) = Join(dicList.Items, COMMA)

arrItem(2) = " ) "

InClause = Join(arrItem, vbNullString)

End If

Erase arrItem

dicList.RemoveAll

Set dicList = Nothing

End Function

Nigel Heffernan says:

June 29, 2016 at 9:37 am

Nope, and &LT;PRE&GT; tags make no difference.

However the web page textbox control is implemented in Chrome or Internet Explorer, comment text is published on your site with all ASCII 34 double-quotes replaced by CHR(147) and CHR(148) open- and close- double quotes; and all the ASCII 39 single-quotes replaced by CHR(145) and CHR(146) open- and close- single quotes.

It’s nice to know that your site complies with the style guide for the New Yorker magazine, but it’s not a place for publishing code snippets.
Dick Kusleika says:

June 30, 2016 at 8:21 am

@Nigel: I agree it sucks. I fixed the code in your comment. Thanks for sharing the code.
Doug Glancy says:

June 30, 2016 at 3:05 pm

I use a one-line function that works like Charles Urban’s formula above:

Visual Basic

FormatForSqlList = "'" & cell.Value & "',"

1

FormatForSqlList = "'" & cell.Value & "',"

Then just copy as values, do a Find/Replace on the single-quotes to nulls if it’s a list of numbers, get rid of the last comma and type the IN() part. For lists of more than 10 items it’s faster than doing it manually.

Hey Nigel. I just downloaded and tried your function. It fails on ArrayDimensions(Items), which looks like a cool little function itself.

Also, if I use two double-quotes for the second argument (or just put the comma with nothing after it) and step through, the code steps into the the correct Select Case clause (vbnull, etc.) and never steps back out. I don’t know what that means. The result is a #VALUE error in the calling cell. This is with a range of string in A1:A10. In summary, I can’t get it to not encapsulate the list items.
Doug Glancy says:

June 30, 2016 at 3:06 pm

Dick, didn’t you use to use Code Colorer? It seems to me it handles the &amp problem, but maybe not.
Dick Kusleika says:

June 30, 2016 at 3:59 pm

Yes, I do use Code Colorer. If I post a comment, it works fine. But if anyone else does, it screws up the

Visual Basic

&

1

&

, the

Visual Basic

<

1

<

, and the

Visual Basic

>

1

>

. And maybe other stuff. I have no idea why. It must be a permission issue of some sort.

Based on Nigel’s experience, I found a plug in called wp-markdown that’s supposed to make your comments just like posting on stackoverflow. It hasn’t been updated in a couple of years, but it’s still worth a try. I love the SO interface.