Excel VBA - out of memory error (runtime 7)

Question

Hey guys, I am getting a very weird error when using VBA in excel. I am using LotusNotes Automation libraries to loop through a view and write everything to cell.

Here is my code (variables of non-interest are removed as I know they are not causing the problem)

Public Function TimeCh()

Set session = New NotesSession 'create a new session (ask for user/pass)
session.Initialize             'initialize the session (allow login)
Set db = session.GetDatabase("HIDDEN") 'Grab the DB
Set view = db.GetView("HIDDEN") 'Get the view




Application.ScreenUpdating = False


'start the loop to go through data
While Not (entry Is Nothing)
    Cells(row, 1) = (entry.ColumnValues(4))
    Cells(row, 2) = (entry.ColumnValues(0))
    Cells(row, 3) = (entry.ColumnValues(26))
    Cells(row, 4) = (entry.ColumnValues(27))
    Cells(row, 5) = (entry.ColumnValues(22))
    Cells(row, 6) = (entry.ColumnValues(20))
    Cells(row, 7) = (entry.ColumnValues(29))
    Cells(row, 8) = (entry.ColumnValues(31))
    Cells(row, 9) = (entry.ColumnValues(30))
    Cells(row, 10) = (entry.ColumnValues(8))
    Cells(row, 11) = (entry.ColumnValues(7))
    Cells(row, 12) = (entry.ColumnValues(21))
    Cells(row, 13) = (entry.ColumnValues(19))
    Cells(row, 14) = (entry.ColumnValues(24))
    Cells(row, 15) = (entry.ColumnValues(25))
    Cells(row, 16) = (entry.ColumnValues(32))
    Cells(row, 17) = (entry.ColumnValues(28))
    Cells(row, 18) = (entry.ColumnValues(9))
    Cells(row, 19) = (entry.ColumnValues(12))
    Cells(row, 20) = (entry.ColumnValues(11))
    Cells(row, 21) = (entry.ColumnValues(23))
    Cells(row, 22) = (entry.ColumnValues(10))
    Cells(row, 23) = (entry.ColumnValues(2))
    Cells(row, 24) = (entry.ColumnValues(33))
    Cells(row, 25) = (entry.ColumnValues(1))
    Cells(row, 26) = (entry.ColumnValues(13))
    Cells(row, 27) = (entry.ColumnValues(5))
    Cells(row, 28) = (entry.ColumnValues(14))
    Cells(row, 29) = (entry.ColumnValues(6))
    Cells(row, 30) = (entry.ColumnValues(18))
    Cells(row, 31) = (entry.ColumnValues(16))
    Cells(row, 32) = (entry.ColumnValues(3))
    Cells(row, 33) = (entry.ColumnValues(15))
    Cells(row, 34) = (entry.ColumnValues(17))
    Cells(row, 35) = (entry.ColumnValues(34))
    row = row + 1
    Set entry = vec.GetNextEntry(entry)
Wend
Application.ScreenUpdating = True

End Function

So it ALWAYS fails on when row = 1425 and column is 35. It gives a "out of memory" (runtime 7 error). It cant be a hardware thing because the system is very stable (dual core, 2 gig ram). And it always crashes at the same row.

I did a search,and I found one relevant thread: http://stackoverflow.com/questions/2721654/out-of-memory-error-in-lotus-notes-automation-from-vba

Has anyone seen something like this before?

Answer 1

Random questions:

1. What data type is row? If you define row as a double, does it still crash?

2. Per the link you quoted (thanks, actually. It was my post), if you define session as a static variable, does it still crash?

3. Even if you don't do #2, do you remember to close your session by the end of the function?

4. This looks like just a subset of a much larger function. How many times is it called in a row?

Answer 2

If the code is consistently failing on the same document and the same column in the view, that would lead me to believe there is some data issue with that one document. Can you trap the error and show some unique identifier for the failing document? I would try to do that, and then look in the 35th column of the view for that document and check the value. Perhaps there is a computation in the view column which is returning @Error or similar - and in turn causing the VBA code to throw the memory exception.

Answer 3

I had the company install Excel 2007 on my computer. Ran the report with no problems at all, so I guess it must be a 2003 internal bug.