Ever since I’ve started working with MS Excel 2007, I’ve encountered several incidences of “out of memory” problems on a computer with 2Gig of RAM (memory).
This and this point out issues on memory limits with Excel.
I am doing pretty dodgy stuff like calling python scripts from VBA, among other things. Python is much, much nicer for parsing text files however…
Rather than using a COM server for driving a few python scripts from Excel, I was considering using the Microsoft Script Control library. For example:
Public Function os_getcwd()
Dim sc as Object
Set sc = New MSScriptControl.ScriptControl
sc.Language = “python”
sc.ExecuteStatement (“import os”)
os_getcwd = sc.Eval(“os.getcwd()”)
End Function
Using it requires adding the Microsoft Script Control 1.0 reference (VBE->Tool->References), which for some strange reason was pointing to a very strange location. I had to re-register the dll/change the reference location by running regsvr32 <fullpath>/msscript.ocx from the command prompt (cmd.exe) as administrator. Even though the command was issued from an administrative account, the command wasn’t being run as adminstrator. In Windows Vista, issuing commands from the command prompt as administrator requires either typing /runas <username>:administrator <command> in the search box in the start menu or; typing ‘cmd’ in the search box, right clicking the result at the top of the menu box and selecting “Run as administrator”. Anything else results in 0800xxx permission errors.
It works reasonably well if the return type is relatively simple — a flat 1-dimensional array, a string or numeric types — or no value is to be returned. This method will not work if you want to return a dictionary, nested lists or any of the more complex (but fundamental) data types you might take for granted in a more richly featured language.
