Increasing allowed memory of string VBA

Question

So I have some code to sort through some output from an API and extract information into a spreadsheet. The problem is the maximum amount of characters I can set some string variable seems to be around 26513 characters (found using debug.print Len(my-oversized-string). Now is there a way in Excel 2010 to extend how much data a string can hold? I would ideally need my string to hold at least 3,500,000 characters. Is this possible or should I approach this problem from a different way?

The error message I get is subscript out of range and sParagraph = Paragraph(i) is highlighted after clicking debug.

Thanks for your help! Justin

Dim URL As String: URL = "someAPIurlputhere"
Dim Http As New WinHttpRequest
    Http.Open "GET", URL, False
    Http.Send
Dim Resp As String: Resp = Http.ResponseText
Dim Paragraph As Variant
    Debug.Print Len(Resp)
    For i = 1 To 365
        Paragraph = Split(Resp, "date")
        ActiveCell.Offset(1, 0).Activate
        ActiveCell.Range("A1:E1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Dim sParagraph As String: sParagraph = Paragraph(i)
        Dim Lines As Variant: Lines = Split(sParagraph, ",")
        ActiveCell.Offset(0, 1).FormulaR1C1 = Lines(0)
        ActiveCell.Offset(0, 2).FormulaR1C1 = Lines(9)
        ActiveCell.Offset(0, 3).FormulaR1C1 = Lines(27)
        ActiveCell.Offset(0, 4).FormulaR1C1 = Lines(29)
        Erase Paragraph
    Next i

The [Excel specifications and limits](http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx) may help you assess the issue. 26513 characters is not a problem unless you are using it as a formula. Additionally, I was able to replicate what Siddharth shared. Perhaps if you show us how you are using the string, we can help you know what to do differently. — Daniel, Oct 15 '13 at 13:59
I added some more information to see if my issue is clearer to you. The error message I get is subscript out of range and sParagraph = Paragraph(i) is highlighted after clicking debug. This seems to correspond to the amount of information that Resp seems to hold, the max amount of information it seems to hold from the API. This is why I assumed I am reaching some sort of memory cap, I could not think of another reason. — user2882453, Oct 15 '13 at 19:58

score 2 · Accepted Answer · edited May 23 '17 at 10:25

Are you sure it is 26513 characters I was able to create a string with 98,333,767 length which is 94,833,767 longer than your requirement of 3,500,000.

98,333,767 - 3,500,000 = 94,833,767

See this example

Sub Sample()
    Dim s As String
    Dim i As Long

    '~~> This will create a string of 32767 in length
    s = Application.WorksheetFunction.Rept("a", 32767)

    '~~> Adding it in a loop 3000 times to check what length we get
    For i = 1 To 3000
        s = s & Application.WorksheetFunction.Rept("a", 32767)
    Next i

    '~~> 98,333,767
    Debug.Print Len(s)
End Sub

As you can see that we get a string of 98,333,767 in length. The length is as expected

32767 + (32767 X 3000) = 98333767

If my memory serves me right then a variable-length string can contain up to approx 2 billion (2^31) characters. Whereas a fixed-length string can contain up to approx 64K (2^16) characters.

FOLLOWUP FROM COMMENTS

You are hardcoding the values For i = 1 To 365 and it's not necessary that the array will have that many items. Use lbound and ubound to loop through the array.

Also, please, please do not use Activecell/select etc. INTERESTING READ

Try this code (UNTESTED)

Sub Sample()
    Dim URL As String: URL = "someAPIurlputhere"
    Dim Http As New WinHttpRequest
    Dim Resp As String, sParagraph As String
    Dim Paragraph As Variant, Lines As Variant
    Dim i As Long
    Dim ws As Worksheet

    Http.Open "GET", URL, False
    Http.Send

    Resp = Http.ResponseText

    Debug.Print Len(Resp)

    Paragraph = Split(Resp, "date")

    '~~> Change as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Loop through the array
        For i = LBound(Paragraph) To UBound(Paragraph)
            .Range("A1:E1").Insert Shift:=xlDown, _
            CopyOrigin:=xlFormatFromLeftOrAbove

            sParagraph = Paragraph(i)

            Lines = Split(sParagraph, ",")

            '~~> I haven't changed this part. Please do not
            '~~> use Activecell. Work with actual range.
            '.Range("B1").Formula = Lines(0)
            '.Range("C1").Formula = Lines(9)
            '.Range("D1").Formula = Lines(27)
            '.Range("E1").Formula = Lines(29)

            ActiveCell.Offset(0, 1).Formula = Lines(0)
            ActiveCell.Offset(0, 2).Formula = Lines(9)
            ActiveCell.Offset(0, 3).Formula = Lines(27)
            ActiveCell.Offset(0, 4).Formula = Lines(29)

            Erase Paragraph
        Next i
    End With
End Sub

Okay so I made some of the changes you indicated. I couldnt use Lbound because I need the array to start at 1, not 0 so I just put a 1 to Ubound(paragraph). When I hover over Ubound(paragraph) it tells me that the value is 32, just like where it was stopping previously. Doesnt that mean that the variable Resp is not holding all the information being sent to it? Also side note I do not know how to have relative references in Excel without using ActiveCell in some form. I will look this up more and teach myself a better way than Activecell. — user2882453, Oct 16 '13 at 15:44
Since your array is of variant type, It will start at 0. In the loop you can increment the value of `i` in case you want to use `1`. Also regarding the activecell, see the commented code just above `ActiveCell.Offset(0, 1).Formula = Lines(0)` — Siddharth Rout, Oct 16 '13 at 15:53

Increasing allowed memory of string VBA

1 Answers1