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 and try it again! — R3uK
– R3uK, Commented Dec 11, 2015 at 14:58
@Rory : Mkay... So my best solution would be to write this in a text file or a word document? — R3uK
– R3uK, Commented Dec 11, 2015 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
– EEM, Commented Dec 11, 2015 at 15:15

R3uK · Accepted Answer · 2015-12-11 15:20:51Z

2

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

answered Dec 11, 2015 at 15:20

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related