Excel SQLConcat Function Revisited

My original post on my SQLConcat function has received some traffic, but the code had a pretty serious error in it. This was due to my previous lack of understanding about VBA’s IsMissing() function.  I’ve also added some extra functionality to the code to allow for adding parenthesis along with quotes. The added functionality makes it easier to adhoc insert statements like this.

Insert into [tbl] ([col1],[col2])
Values (1,'hello'),(2,'world')


I made a pretty big error in using IsMissing() in my original function. According to MSDN the IsMissing() function only works on variant type arguments.

Returns a Boolean value indicating whether an optional Variant argument has been passed to a procedure.

And goes on to say:

IsMissing does not work on simple data types (such as Integer or Double) because, unlike Variants, they don’t have a provision for a “missing” flag bit. Because of this, the syntax for typed optional arguments allows you to specify a default value. If the argument is omitted when the procedure is called, then the argument will have this default value…

This is doubly bad because the argument that I was checking for was a Boolean type. Since boolean types return false if they have not been set, this could cause issues. Paste this code into an empty module and you’ll see what I mean. The reason that the original version of my code worked, is that if the argument was missing, I was setting it to false anyway. I corrected this by setting a default value in the argument declaration of the function.

Private Sub testIsMissing(Optional bool As Boolean)
If IsMissing(bool) Then
    MsgBox bool
Else
    MsgBox bool
End If
End Sub

Private Sub test()
    testIsMissing True
    testIsMissing False
    testIsMissing
End Sub

So without further ado, here is the updated function. I hope it serves you well.

Function SQLConcat(rng As Range, Optional quoted As Boolean = False, Optional parenthesis As Boolean = False) As String
' ***************************************************************
' * Returns a comma separated list for use in SQL IN statements *
' * Params *
' * - rng: Range of cells to concatenate *
' * - quoted: True/False. If true, values are placed inside *
' * of single quotes. Default of false *
' * - parenthesis: Boolean. *
' * Useful for INSERT INTO tbl VALUES(53),(90),(397) *
' * *
' * Author: Christopher J. McClellan *
' * Published under Creative Commons Attribution-Share Alike *
' * http://creativecommons.org/licenses/by-sa/3.0/ *
' * You are free to change, distribute, and pretty much do *
' * whatever you like with the code, but you must give credit *
' * to the original author and publish any derivitive of this *
' * code under the same license. *
' ***************************************************************

Dim tmp As String 'temporary string
Dim row As Long 'first cell is special case
row = 0 'initalize row count
Dim c As Object 'cell
Dim txtwrapperLeft As String, txtwrapperRight As String

If quoted = True And parenthesis = False Then
 txtwrapperLeft = "'"
 txtwrapperRight = "'"
ElseIf quoted = True And parenthesis = True Then
 txtwrapperLeft = "('"
 txtwrapperRight = "')"
ElseIf quoted = False And parenthesis = True Then
 txtwrapperLeft = "("
 txtwrapperRight = ")"
Else
'quoted = false and parenthesis = false
 txtwrapperLeft = ""
 txtwrapperRight = ""
End If

For Each c In rng.Cells
 If row = 0 Then
 tmp = txtwrapperLeft & c.Value & txtwrapperRight
 Else
 tmp = tmp & "," & txtwrapperLeft & c.Value & txtwrapperRight
 End If
 row = row + 1
 Debug.Print tmp
Next c

'return
SQLConcat = tmp
End Function

Advertisements
  1. Excel SQLConcat Function | Christopher J. McClellan

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: