Author Topic: Macro with paramteres  (Read 760 times)

jelkhoury

  • Newbie
  • *
  • Posts: 14
  • Karma: +0/-0
    • View Profile
Macro with paramteres
« on: January 08, 2015, 12:22:49 PM »
I have created a simple Python class which has a 3  attributes mostly numpy arrays and  a couple of methods to update these attributes.
I also wrote a couple of functions where I am using the shelve module to make my class instances persistent in a .dat file.
I would like to use xl as my GUI to display and update my instances:  A macro which takes the data from xl to update the shelve or load from the shelve and display again in xl.

I used the PYXLL xl_macro decorator with my function which parameters are the instances new attributes
I m struggling to make this macro work in xl especially that it needs the parameters as xl cell ranges

can you Please show me an example of how to assign a macro to a button while its got parameters


Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 605
  • Karma: +14/-1
    • View Profile
Re: Macro with paramteres
« Reply #1 on: January 08, 2015, 02:26:48 PM »
Hi,

macros triggered by buttons in Excel don't take parameters. You can only call macros with parameters using VBA.

You could put your parameters into cells (best to use named ranges) and fetch the values in your marco.

Best regards,
Tony

jelkhoury

  • Newbie
  • *
  • Posts: 14
  • Karma: +0/-0
    • View Profile
Re: Macro with paramteres
« Reply #2 on: January 08, 2015, 04:11:30 PM »
Thank you Tony

so if I write a VBA and create parameters = to my named range cells
can I then within the VBA call my python function with run and pass to it the parameters above?

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 605
  • Karma: +14/-1
    • View Profile
Re: Macro with paramteres
« Reply #3 on: January 08, 2015, 05:00:21 PM »
Yes, you can do that - see https://www.pyxll.com/docs/index.html#calling-macros-from-excel

However, you can just as easily query a named range from python - eg

Code: [Select]
xl = xl_app()  # see automation example
value = xl.Range("MyNamedRange").Value

Regards,
Tony

jelkhoury

  • Newbie
  • *
  • Posts: 14
  • Karma: +0/-0
    • View Profile
Re: Macro with paramteres
« Reply #4 on: January 09, 2015, 05:48:32 AM »
Thank you

I ve tried this and it works

however when I assign a list or an array to a multiple cells RANGE like this:
@xl_macro()
def testingarray():
    xl=xl_app()
    v=array([1,2,3])
    xl.Range("MyOutputRange").Value=v

I get the first item of the list (here 1) in all the cells of the range
how can I expand?

THANKS
« Last Edit: January 10, 2015, 06:01:37 PM by jelkhoury »

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 605
  • Karma: +14/-1
    • View Profile
Re: Macro with paramteres
« Reply #5 on: January 13, 2015, 03:21:31 PM »
Hi,

use a list of lists and assign that to the value. For example, if MyOutputRange is a range of 2 rows x 3 cols, try r.Value = [[1,2,3], [4,5,6]].

Best regards,
Tony