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
clause in SQL. Let’s say I have this list of customer IDs and I want to make an
clause.
I’d use JoinRange like
|
=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') |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
|
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,",","(",")",,"'") |
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,"|","(",")",,"'") |
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) |
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 |
|
=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.