Passing variant arrays in Ver 2.0

[Doug Jenkins]

Eric - I see the documentation at github is progressing, but I didn't find an answer to this.

It seems in version 2 that variant arrays are passed to Python as tuples, rather than lists. I discovered this when converting some functions that link with the Alglib library, which were returning errors because they wouldn't accept tuple arguments.

I have fixed the problem by adding to my Python code:
For 1D arrays: x = list(x)
For 2D arrays: x = np.array(x).tolist()

I'm wondering if there is a simpler way to do this, especially for the second one, preferably from VBA.

Another (minor) point: Is it possible to get the add-in to recognise xlsb files as macro-enabled? At the moment I have to save as xlsm to run the add-in, then save back to xlsb (which are much smaller when there is a loy of data).

[Eric Reynolds]

I see your problem especially in the second case, I will have to think about that. I also prefer lists personally, but pywin32 automatically converts arrays to tuples instead, which is why ExcelPython does.

Unfortunately one of the features which was lost in moving over to v2 was the ability to control how the VBA objects are converted to Python objects.

In VBA you could define a converter function like this:

Set tolist = Py.Eval("lambda t: [ [ x for x in y ] if type(y) is tuple else y for y in t ]")
?Py.Str(Py.Call(tolist, Py.Tuple(Range("A1:B2").Value)))
[[None, None], [None, None]]

Otherwise if you're using the add-in, it definitely makes sense to be able to pass arguments as lists / lists of lists so I will add an option to marshal them as such in the next release.

Regarding your second point, I had never even heard about .xlsb files, I will add that in the next release, in the meantime please edit the add-in vba code as follows:

replace

f.WriteLine sTab + "If LCase$(Right$(PyScriptPath, 5)) = "".xlsm"" Then PyScriptPath = Left$(PyScriptPath, Len(PyScriptPath)-5)"

with

f.WriteLine sTab + "PyScriptPath = Left$(PyScriptPath, Len(PyScriptPath)-5)"

and replace

If Right$(wb.name, 5) <> ".xlsm" Then

with

If Right$(wb.name, 5) <> ".xlsm" And Right$(wb.name, 5) <> ".xlsb" Then