Cleaning Up My JoinRange Arguments
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.
| 38 |
| 142 |
| 146 |
| 175 |
| 214 |
| 217 |
I’d use JoinRange like
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
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.
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:
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…
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
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.
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
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.