Error #VALUE with long string in UDF in VBA for output in Excel

Question

I use the below UDF to concatenate references to include the result in
an SQL query like ref in ('ref1', 'ref2', ...).

The UDF works just fine normally, but when I need to put a huge list of references,
I get #VALUE in Excel.

I already take a look at this answer, but I can't manage to make my UDF work...

I've tried to changed the type of the function from String to Variant (explicitly),
but it didn't change a thing...

I also tried ConcatSQL2 = A(0) and ConcatSQL2 = A for the output,
Dim A(0 To 0) As String for the declaration, ... and again it is not working...
I'm running out of ideas...

For info, the result string is expected to be about 220k long...
To help you generate a lot of text, you can use the Lorem Ipsum generator here!

Public Function ConcatSQL2(Plage As Range, Optional Doublon As Boolean = True)
Dim A() As String, _
    Cel As Range
ReDim A(0)

A(0) = "('"
For Each Cel In Plage.Cells
    If (Cel.Value <> "" And (InStr(1, A(0), Cel.Value) = 0 Or Doublon)) Then
        A(0) = A(0) & Cel.Value & "', '"
    End If
Next
A(0) = Left(A(0), Len(A(0)) - 3) & ")"

ConcatSQL2 = A(0)
End Function

I directly type it in excel `=ConcatSQL2(A2:A15419;FALSE)`. but as I said it **works fine in normal use**, but when the **output is too long**, I get the **error**... so try to [generate a lot of text](http://www.lipsum.com/) and try it again! — R3uK, Dec 11 '15 at 14:58
@Rory : Mkay... So my best solution would be to write this in a text file or a word document? — R3uK, Dec 11 '15 at 15:07
Not really, the limitation is for a worksheet cell. The VBA variable holds the result with no error, since you are using VBA, just don't post the result in a cell, continue working in VBA. — EEM, Dec 11 '15 at 15:15
@Rory : Pasting it into DB software to complete my SQL query, so the text file seems a good solution. — R3uK, Dec 11 '15 at 15:22
I think the performance will suck! In clauses are not efficient generally and that's a LOT of values!! — Rory, Dec 11 '15 at 15:23
@Rory : I'm not an SQL pro, but if you know a more efficient way to do this, I'm open to learn!^^ Anyway, even with the `in`, my query only took a few secs. And as I don't have to automate this, I don't really have a performance issue, but always open to suggestion! — R3uK, Dec 11 '15 at 15:52

score 2 · Accepted Answer · answered Dec 11 '15 at 15:20

Regarding @Rory's comments :

32767 is the maximum number of characters in a cell

I decided to write the output in a text file to be reused afterwards!

Here is the final solution

Public Function ConcatSQL2(Plage As Range, Optional Doublon As Boolean = True)
Dim A(0 To 0) As String, _
    myFile As String, _
    Cel As Range
'ReDim A(0)

A(0) = "('"
For Each Cel In Plage.Cells
    If (Cel.Value <> "" And (InStr(1, A(0), Cel.Value) = 0 Or Doublon)) Then
        A(0) = A(0) & Cel.Value & "', '"
    End If
Next
A(0) = Left(A(0), Len(A(0)) - 3) & ")"

myFile = "Path\FileName.txt"
Open myFile For Output As #1
Write #1, A(0)
Close #1

ConcatSQL2 = A
End Function

Error #VALUE with long string in UDF in VBA for output in Excel

1 Answers1