Author Topic: Call pyxll function returning Array from VBA  (Read 1020 times)

pyuser

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Call pyxll function returning Array from VBA
« on: December 04, 2014, 10:25:15 PM »
Hi

I tried to call a pyxll function from VBA. The pyxll function returns an array of string. It works fine when I call it from Excel, but when I do the same in VBA,I kept getting an error.

Below is the simple function I used to test:

@xl_func("string x: string[]", macro=True)
def py_strArr(x):     
    return [ [ x ] for i in xrange(5)] 

in VBA, I wrote:

Sub test_py_macro()
    Dim blgID As Variant
    blgID = Application.Run("py_strArr", "test")
    Debug.Print blgID
End Sub

Can you please help to see what went wrong? And how to get the above pyxll function work in VBA?

Thanks in advance!

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 568
  • Karma: +13/-1
    • View Profile
Re: Call pyxll function returning Array from VBA
« Reply #1 on: December 05, 2014, 11:22:16 AM »
Hi,

where/what is the error? If you step through in the debugger you'll probably find that the error is not in calling your function but in trying to call Debug.Print with an array (type mismatch error?).

You can't print an array using Debug.Print. You can see it in the locals window though. Here's a post on stack overflow I found about how to print an array in VBA:
http://stackoverflow.com/questions/14274949/how-to-print-two-dimensional-array-in-immediate-window-in-vba

As a side note, you probably know that the macro=True isn't necessary for this function (I guess it was left in from another function you were working on). I just thought I'd mention it in case you thought you needed it for some reason.

Best regards,
Tony

pyuser

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Call pyxll function returning Array from VBA
« Reply #2 on: December 05, 2014, 03:25:02 PM »
Thank you very much for all the info. Tony!
It is highly appreciated!

pyuser

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Call pyxll function returning Array from VBA
« Reply #3 on: December 09, 2014, 11:32:31 PM »
Hi Tony,

May I ask a follow-up question on this?

I'm trying to call a function from VBA. But since the inputs are arrays (dates and double array), Can  you please let me know what the correct way of passing the array in?

This is how I did - just for illustration purpose:

Dim inputArr() as Variant
inputArr = Sheets("xxx").Range("A1:A10").Value
output = RUN("py_function", inputArr)

But the output shows an error. I tried to debug and found that the data structure of inputArr is a two dimensional, i.e. array of array. Do you know if that is the problem? If so, could you please let me know how to fix this or correctly pass the array inputs?

Thank you very much for your help!

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 568
  • Karma: +13/-1
    • View Profile
Re: Call pyxll function returning Array from VBA
« Reply #4 on: December 10, 2014, 12:01:00 AM »
Hi,

that's expected :) a range in Excel is a 2d array, even if it's only 1 column wide or 1 row deep.

You can use a custom type to convert a 2d array into a list (see the flatten function in the custom types example), or you can just convert it at the start of your function, or you could use one of the 1d numpy types numpy_row or numpy_column.

eg:

@xl_func("numpy_column x: string")
def my_func(x):
    # x is a 1d numpy array
    return str(x)

or:

@xl_func("float[] x: string")
def my_func(x):
    # x is a 2d list of lists - flatten it to a 1d list
    x = list(itertools.chain(*x))
    return str(x)

I think it's better to be consistent and keep all excel ranges represented the same way in python by default (a list of lists) than to try and second guess what the user expects (e.g. what if you expected a 2d range but only had a single row of data?) and so that's why the behaviour is as it is.

Best regards,
Tony

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 568
  • Karma: +13/-1
    • View Profile
Re: Call pyxll function returning Array from VBA
« Reply #5 on: December 10, 2014, 12:04:18 AM »
Also, for dates you can use the pyxll type converter when you get an array of mixed types, e.g.:

from pyxll import xl_func, get_type_converter

@xl_func("var[] x: string")
def test_func(x):
    # assume first item is a date
    var_to_date = get_type_converter("var", "date")  # returns a function that converts something passed from excel as type 'var' to a python date
    d = var_to_date(x[0][0])
    return d.strftime("%Y-%m-%d")

pyuser

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: Call pyxll function returning Array from VBA
« Reply #6 on: December 11, 2014, 11:54:38 PM »
Hi Tony,
I tried different methods, but still have problems in calling the function from VBA.

My original function can be called successfully from excel using the following approach:

@xl_func ("datetime[] myDates: numpy_column")
def myFunction(myDates):
  date_ = [d[0] for d in myDates]
  ...
  return np.array(myAns)

So this already takes care of the two-dimensional array from Excel. But I don't know why it doesn't work for VBA ranges...

Thank you again for all the help! It is highly appreciated it!



Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 568
  • Karma: +13/-1
    • View Profile
Re: Call pyxll function returning Array from VBA
« Reply #7 on: December 15, 2014, 12:26:56 PM »
Hi,

it must be something to do with how you are constructing the object in VBA or what your python code is expecting. Try passing it as a 'var' type and see what you get on the python side either by attaching a debugger or just printing out the values and types of the arguments. That should help you figure out what's going wrong and what you need to change.

Best regards,
Tony