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

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

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.

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.

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

  1. 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

  2. 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

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

  3. 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.

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

  5. Nope, and <PRE> 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.

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

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

    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.

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

    , the

    , and the

    . 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.