runtime error 7 Out of Memory using vba collection

Question

I'm new to vba in excel, infact its the first time for me. Along the way i've managed to work most things out and find answers in the net. However the latest issue im having i cant find an answer, solution to my problem.

this function, downloads a json string, and is usually 5+mb big. and this loop finds the opening and closing {} for each component and adds it to a collection. All is well until I get to around the 1600/35k entry and the runtime error happens.

There is a 2nd error, with a out of string message, but assigning vbNullString to the string stops this from happening

Dim cnt As Long
Dim json As String, idKey As String
Dim strStart As Long, strFinish As Long

cnt = 0
strStart = 1
strFinish = 0
On Error GoTo HandleIt
Do While strFinish <= Len(dataStr) - 5
    json = vbNullString
    strStart = InStr(strStart, dataStr, "{""Id"":", vbBinaryCompare)
    strFinish = InStr(strStart, dataStr, "}", vbBinaryCompare) - 1
    json = Mid$(dataStr, strStart, strFinish)

    ' Get the itemID for the key
    'strStart = InStr(6, json, ",", vbBinaryCompare)
    'idKey = Mid$(json, 7, strStart - 7)

    TSMDataBulk.add json
    strStart = strFinish
    cnt = cnt + 1
Loop

tltd: Everything runs fine until TSMDataBulk.add causes a out of memory error. Can anyone help?

Could you add them to an array instead?

Mathieu Guindon
– Mathieu Guindon

2016-03-09 22:34:58 +00:00
Commented Mar 9, 2016 at 22:34 — Mathieu Guindon
– Mathieu Guindon, Commented Mar 9, 2016 at 22:34

barrowc · Accepted Answer · 2016-03-10 00:12:42Z

2

It looks like you are using Mid$ as though the parameters were: Mid$(string, start, end) In reality, the parameters are: Mid$(string, start, length)

So if strStart was 5000 and strFinish was 5050, you would be returning all of the characters from 5000 to 10549 instead of returning those from 5000 to 5050. This may well explain the out of memory errors as you get further into the string.

Change:

json = Mid$(dataStr, strStart, strFinish)

to:

json = Mid$(dataStr, strStart, (strFinish - strStart) + 1)

answered Mar 10, 2016 at 0:12

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

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

2 Comments

user4039065 Over a year ago

Good catch! Completely missed that myself.

Lee Over a year ago

Thankyou barrwc. That was spot on. I'de give you some rep but my rep is too low to do that!

user4039065 · Accepted Answer · 2016-03-09 22:35:26Z

1

TSMDataBulk looks like it is a Scripting.Dictionary object. You are adding a key but no item.

TSMDataBulk.add key:=json, item:=json
'alternate no. 1
TSMDataBulk.add key:=json, item:=""
'alternate no. 2 (overwrite method)
TSMDataBulk.item(json) = ""

Judging by your sample code there is a session id in the first position. I might try to use that as the key and put the remainder in as the item.

answered Mar 9, 2016 at 22:35

user4039065

1 Comment

Lee Over a year ago

It is Jeeped. and the add() command is item:=, key:=, before:=, after:= with item: being the only bit of info required. I've tried using the dictionary but i'm still getting the same answer

Collectives™ on Stack Overflow

runtime error 7 Out of Memory using vba collection

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related