I have tried the SaveAs method in VB between Excel function calls, where I save the Excel file under another name, then kill the Excel process, and then re-instantiate another Excel process for the next function. I don't just use a Workbook.Quit command, I actually terminate the Excel application in the Task Manager so that the memory is freed up (I think).
However, when I re-instantiate Excel app based on the previously saved file, it starts up around 52,000K, then when I start the workbook, it jumps up another 79,000K. Is there anything I can do to bring down both the memory used and the CPU % used?
Also, some of the Excel functions are extremely long, and there is no way I can break them up. By the time the function is finished, the memory and CPU % used almost crash the pc. I can only use the SaveAs method in VB outside the Excel function. Within the Excel function I can't do anything to kill Excel and re-instantiate, so the memory used just keeps growing until the long loop is finished.
Any suggestions would be appreciated.