I have written a macro that calls a third party software engine called EPANET through a provided DLL library. I run this engine and then access certain outputs via some provided functions, all inside VB. Then I write these values to a spreadsheet.
Everything works fine except that, after running the macro through several hundred iterations of this and pasting to several hundred rows in my spreadsheet that contains the macro, the macro starts inserting all zeros. If I close and reopen the file, then start back at the first place the zeros started appearing, the macro works fine and inserts the correct values. But eventually this will happen again.
Is this a memory issue? When I tried to run the macro on all 4400+ rows, my whole computer froze and I had to turn the power off and restart. So then I started doing smaller chunks, which avoided the locking up. But, once the zeros start appearing, even doing 10 rows at a time does not help. It appears as if it reaches some limit where it will no longer insert any values into the spreadsheet other than zeros until I close and reopen the workbook.
The code is nothing special except for the EPANET part. I just access some parameters using the EPANET VB functions. Then I do a simple assignment to the cells using the "...Cells(i,j).Value = mynumber" command, which I use all the time.
I've coded in VB quite a bit, but I've always done spreadsheet manipulations and don't know anything about handling memory explicitly.
Everything works fine except that, after running the macro through several hundred iterations of this and pasting to several hundred rows in my spreadsheet that contains the macro, the macro starts inserting all zeros. If I close and reopen the file, then start back at the first place the zeros started appearing, the macro works fine and inserts the correct values. But eventually this will happen again.
Is this a memory issue? When I tried to run the macro on all 4400+ rows, my whole computer froze and I had to turn the power off and restart. So then I started doing smaller chunks, which avoided the locking up. But, once the zeros start appearing, even doing 10 rows at a time does not help. It appears as if it reaches some limit where it will no longer insert any values into the spreadsheet other than zeros until I close and reopen the workbook.
The code is nothing special except for the EPANET part. I just access some parameters using the EPANET VB functions. Then I do a simple assignment to the cells using the "...Cells(i,j).Value = mynumber" command, which I use all the time.
I've coded in VB quite a bit, but I've always done spreadsheet manipulations and don't know anything about handling memory explicitly.