Getting round Max string size of 32347 in a VBA function

Question

My VBA function stops working with one additional line of code. The final line of code in the working function is: returnString = returnString + "some text from some cell". If I copy paste that line of code, it breaks.

The only cause I can see is string length. When I output the length of returnString in the working function it is 32347. EDIT: So 32347 works, but adding a further 10 characters or so it starts to fail. This looks suspiciously close to a maximum length of some sort. Is there any way round this?

At the moment I am using this Function (not Sub) to return the string to an Excel cell. A separate Sub-routine then picks up the contents of the cell, to integrate it with other data and output a JSON file.

This question appears to ask the same thing, but the max was 255, and so the answers don't help me: Getting around the Max String size in a vba function?. Please note, though, that when I save the 32347-length string, it is 32k. So I can't have hit the 2GB limit!

Any help appreciated!

Function getData(str)

Dim lArray() As String
Dim pData As String
Dim ctr As Integer
Dim ctr2 As Integer
Dim ctr3 As Integer
Dim returnString As String
Set rangetoexport = Worksheets("mysheet").Range("c2:m2728")
Dim id As Long
Dim rowRef As Long
Dim colRef As Integer
Dim var(47) As String

Const idColumn = 1
Const webColumn = 2
Const nameColumn = 3
Const revColumn = 4
Const empColumn = 5
Const ctryColumn = 10
Const indyColumn = 11

Const numFactors = 47

Const labelRow = 1

Const apost = """"






lArray = Split(str, ",")

returnString = "["

  If UBound(lArray) <> 0 Then

        pData = ""

        For ctr = 0 To UBound(lArray)

            'Open the object
            returnString = returnString + "{"

            'Find the row
            id = lArray(ctr)
            rowRef = -1

            For ctr2 = 0 To rangetoexport.Rows.Count
                If rangetoexport.Cells(ctr2, idColumn) = id Then
                    rowRef = ctr2
                    Exit For
                End If
            Next

            'add the parts
            'add the ID and new object

            returnString = returnString & """company"": {"



            returnString = returnString & """ID"":" & rangetoexport.Cells(rowRef, idColumn) & ","
            returnString = returnString & """Website"":""" & rangetoexport.Cells(rowRef, webColumn) & ""","
            returnString = returnString & """Company Name"":""" & rangetoexport.Cells(rowRef, nameColumn) & ""","
            returnString = returnString & """Revenue"":""" & rangetoexport.Cells(rowRef, revColumn) & ""","
            returnString = returnString & """Employee Count"":" & rangetoexport.Cells(rowRef, empColumn) & ","
            returnString = returnString & """Country"":""" & rangetoexport.Cells(rowRef, ctryColumn) & ""","
            returnString = returnString & """Industry Classification (SIC4)"":""" & rangetoexport.Cells(rowRef, indyColumn) & """}},"

            'returnString = returnString & """factors"": {"

            'For ctr3 = 1 To 10


            'Next

            'returnString = returnString & apost & rangetoexport.Cells(labelRow, 12) & apost & ":" & rangetoexport.Cells(rowRef, 12) & ","
            'returnString = returnString & apost & rangetoexport.Cells(labelRow, 12) & apost & ":" & rangetoexport.Cells(rowRef, 12) & ","
            'returnString = returnString & apost & rangetoexport.Cells(labelRow, 12) & apost & ":" & rangetoexport.Cells(rowRef, 12) & ","
            'returnString = returnString & apost & rangetoexport.Cells(labelRow, 12) & apost & ":" & rangetoexport.Cells(rowRef, 12) & ","



            'returnString = returnString & rangetoexport.Cells(rowRef, webColumn) & "},"

        Next

    Else
        returnString = ""


    End If


returnString = Left(returnString, Len(returnString) - 1)

getData = returnString & "]"

The limit is ~2 billion characters for a VBA string variable. If you see truncation its likely because there is a restriction in whatever you are outputting the string to or you have introduced a null character which can cause this. The way to test is to look at `len(returnString)` rather than the content. Note always use & rather than + to concatenate strings. — Alex K., Jul 05 '17 at 16:20
If your function is a UDF then the linked question is *exactly* your issue. Also... if you have 32K characters in an Excel cell, you're doing something wrong. Excel wasn't designed for this. — Mathieu Guindon, Jul 05 '17 at 16:23
Does a string of length 32346 work for you and only begins to fail at 32347? If not, please isolate the threshold at which your code fails. Also, please show your code. — David Zemens, Jul 05 '17 at 16:37
@Alex K, thank you for the info. I used len(returnString) which gave me 32347 for the working string. — emma, Jul 05 '17 at 16:55
@ Mats' Mug - thanks for your comment, but I don't follow. This guy had a problem with 255 characters and it was to do with Application.OnTime only being able to handle 255 characters. How is this the same as my issue? I know I'm abusing Excel slightly, but it has been perfect so far and is the only obvious way I have to create my JSON file. — emma, Jul 05 '17 at 17:00
The problem in the linked question is because Excel uses Pascal strings internally which is why strings in many places in Excel are limited to 255 bytes. I'm not sure that's the case for your problem. I see an apparent limit in this case (using Excel 2010) of 32,767 characters as the return value from a UDF). If you're creating JSON, why not just dump it to a .txt file instead of forcing it in to an Excel cell? — David Zemens, Jul 05 '17 at 17:07
32,767 characters is the stated maximum that can be stored in an Excel cell - see [Excel specifications and limits](https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2016-2013) - but if your text contains line feeds then there is a separate maximum of 253 line feeds per cell — barrowc, Jul 05 '17 at 23:04

score -2 · Answer 1 · answered Jul 05 '17 at 16:21

-2

I would recommend the use of an Array instead. You could create a dynamically sized array and at the end, it is easier to manipulate it.

answered Jul 05 '17 at 16:21

MNF

82
7

1

this doesn't appear to directly answer the question (which admittedly is of somewhat low quality). This perhaps should have been a comment on the OP rather than an "answer". It's not obvious that your suggestion would avoid the error or problem in OP (due in part to lack of specificity in the question itself). – David Zemens Jul 05 '17 at 16:43

Getting round Max string size of 32347 in a VBA function

1 Answers1