Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - Tony

Pages: 1 2 [3] 4 5 ... 36
31
Help and Support / Re: Return an excel error value from python to excel
« on: November 29, 2016, 08:06:40 PM »
Hi,

you can use exceptions to pass errors back to Excel (returning them, not raising them).

See https://www.pyxll.com/docs/userguide/udfs.html#passing-errors-as-values for more details.

The dataframe return type from the "pyxll_utils" package converts nans to RuntimeError exceptions. If you are using pandas with PyXLL this might be useful to you:

https://github.com/pyxll/pyxll-utils
http://pyxll-utils.readthedocs.io/en/latest/

Best regards,
Tony

32
Hi Alfredo,

there's another fix that I want to get into this next patch release which may delay when I can release it. I still hope to get it done before next week, but probably not tomorrow now.

I hope that doesn't upset your plans? If it does, let me know and I can do a release just with this fix I made for you.

Kind regards,
Tony

33
Help and Support / Re: Problem with 3.1.0
« on: November 29, 2016, 01:38:38 PM »
Hi Janson,

I really appreciate your perseverance with this! I'm so sorry for the problems with that build - I've been upgrading the build environment for some other changes I'm working on for another release, and I only tested the release I sent you without using a license key.

While doing some more testing I think I finally managed to get the same behaviour you were originally seeing when reloading - that is, it works initially but fails after a couple of reloads. For me, I could only make it fail when using asynchronous functions. I tracked the problem down to the socket module being reloaded.

I've uploaded yet another beta build to beta.pyxll.com. This new build won't reload any of the standard python modules (i.e. those in pythonhome/Lib - with the exception of those in pythonhome/lib/site-packages which will be reloaded) as well as not reloading any packages that include c extensions (e.g. pandas and numpy). Since it's unlikely anyone will be making changes to the standard library while using pyxll I think this is a reasonable change, and will hopefully make the deep reload feature more usable without having to specify lots of exclusions manually.

Apologies again for the problems with the last build. Hopefully this one will be the last!

Kind regards,
Tony

34
General Discussion / Re: select case statement doesn't work
« on: November 28, 2016, 07:18:37 PM »
With regard to the select statement not working, you still have the wrong case in your cases.

If you try the following:

Code: [Select]

Sub Test1()
    x = "A"
    Select Case LCase(x)
    Case "A": MsgBox("A")
    End Select
End Sub

Sub Test2()
    x = "A"
    Select Case LCase(x)
    Case "a": MsgBox("a")
    End Select
End Sub


You will find that Test1 does nothing, but Test2 shows the message box.

Perhaps splitting the Run onto the next line in the case statement would make it clearer what line is running when you step through in the debugger?

I'm pretty sure the lower casing of the string is your problem.

Best regards,
Tony


35
General Discussion / Re: select case statement doesn't work
« on: November 28, 2016, 07:08:48 PM »
Hi,

that error suggests that either you've got the name of the list box wrong, or it's not actually a list box (maybe it's a ComboBox?)

I've just tested this myself, and have attached a working sample.

Best regards,
Tony

36
General Discussion / Re: select case statement doesn't work
« on: November 28, 2016, 06:08:16 PM »
Hi,

can you send me your code for those macros A and B? Perhaps there is something wrong with them. If you set the log level to debug in the config file then you will get more information in the log that will confirm whether or not the macros are being added.

You need to get the list box from the "ListBoxes" collection on the worksheet. Something like this should work:

Code: [Select]
xl = xl_app()
sheet = xl.Sheets("Sheet1")
list_box = sheet.ListBoxes("ListBox1")
value = list_box.Value  # returns the item selected (starting from 1)
text = list_box.List[int(value)-1]  # returns the value corresponding to the selected item

For what it's worth, I just tried this:

Code: [Select]
from pyxll import xl_macro, xlcAlert

@xl_macro
def A():
    xlcAlert("A")

and was able to run it using

Code: [Select]
Sub Test
    Run("A")
End Sub

Best regards,
Tony

37
General Discussion / Re: select case statement doesn't work
« on: November 28, 2016, 04:50:36 PM »
Also, I don't think you need the "Call" in your code. That's for calling a subroutine, whereas "Run" is a method on the Application object. "Run('<macro name>')" should be sufficient.

38
General Discussion / Re: select case statement doesn't work
« on: November 28, 2016, 04:47:10 PM »
Hi,

it looks like a bug in your VBA code to me. LCase will convert the text to lower case, but all of your cases are upper case and so will not match.

Just in case you're not already aware, but you can write macros like this in Python too. If you use the "xl_macro" decorator on a Python function then it will be available as a macro in Excel, which you can assign to a command button (you have to type in the macro name rather than select it from the drop down list).

More information about macros can be found here:
https://www.pyxll.com/docs/userguide/macros.html

Best regards,
Tony

39
Help and Support / Re: Problem with 3.1.0
« on: November 28, 2016, 11:42:30 AM »
Hi Janson,

that behaviour hasn't changed between 3.0 and 3.1. It was always the case that the deep_reload_include option restricts deep reloading to only those packages.

I've been trying to reproduce the problem of reloading failing after a number of attempts, and that of functions not working until the first reload, and have been unable to :(

However, while working on this I did find that if I had a module (A) that was imported by another module (B) and in the modules list in the config I had B followed by A then when I first started A would get imported (by A) and then reloaded by pyxll.

Is it possible that there have been some other code changes or config changes that were made around the same time as moving from 3.0 to 3.1? It could be something like what I described above that should be harmless, but is causing a problem when deep_reload is enabled.

I have fixed this import/reload issue in the latest beta build (which you can download from https://beta.pyxll.com). I was also thinking about the problems with reloading packages like numpy and pandas. In 3.0 any c extension is ignored when reloading, and now I have made that more conservative by not reloading any package that contains a c extension. This means that packages like numpy and pandas will now be automatically excluded from being reloaded.

Reloading Python modules is tricky. The deep reload feature works by building a dependency graph of imported modules and reloads them from the bottom up, but if there are circular dependencies or any more complex dependencies then it may not work. It's really there for convenience while developing code, and so restricting it to code you're working on is a good way to avoid those problems.

Best regards,
Tony

p.s. as you mention changing other users' configs, did you know you can centralise your users' configs by using an external config file? See the 'external_config' option:
https://www.pyxll.com/docs/userguide/config.html#pyxll-settings

40
Help and Support / Re: Problem with 3.1.0
« on: November 25, 2016, 10:40:57 AM »
Hi,

ah, sorry, I mis-typed the url  :-[

That's helpful - I'll do some more investigation and get back to you. There shouldn't have been any changes to the deep reloading behaviour between 3.0 and 3.1 so something has gone wrong...

That behaviour you're seeing with the deep_reload_include option is expected. Without that option it tracks all imported modules and reloads all of them - so, for examples if one of your modules imports pandas, pandas will be reloaded. If you set that option then only those modules/packages will be reloaded. This is useful if you structure your code into packages and want to restrict reloading to only you packages. For example, you might have your pyxll functions in a module "a.pyxll", but all the implementation in "a.x", "a.y", "a.z". When reloading, you want everything in a.* to be reloaded, but not anything from any other packages (eg a.x may import b, and you don't want b to be reloaded). By adding "a" to deep_reload_include it would restrict the deep reloading to a and its sub-packages, so a.x, a.y, a.z all get reloaded but b doesn't.

If it is just pandas you want to excluded from deep reloading (although I expect there are also other modules that don't tolerate being reloaded too) you can add them to the pyxll_reload_exclude list in the config (you should only set deep_reload_include or deep_reload_exclude, not both).

I hope that makes sense? I'll get back to you shortly when I have more information about the problems you're seeing in 3.1.

Best regards,
Tony

41
Help and Support / Re: Problem with 3.1.0
« on: November 24, 2016, 01:11:09 PM »
Hi,

I've found the cause of that error, but I wasn't able to reproduce the reloading behavior you described I'm afraid.

I've uploaded a new beta release to:
https://beta.pyxll.com/download.html#standalone (modified since original post)

This new release fixes the problem that was causing that error you were seeing. Since that error is related to how reloading gets called it's likely it was also the cause of the reload problem you were having (although unfortunately I've not been able to repeat that problem to verify it's fixed).

Please could you try this new version and let me know if it resolves your issues or not?

Best regards,
Tony

42
Help and Support / Re: Problem with 3.1.0
« on: November 24, 2016, 09:53:54 AM »
Hi,

I suspect those problems are related to that error message.

Thanks for reporting this, and apologies for the inconvenience. I will investigate and get back to you shortly...

Kind regards,
Tony

43
Hi Alfredo,

great :) I'll do some more testing to make sure everything's looks ok with this change and assuming nothing major comes up I'll aim to get a new release out by Wednesday next week (November 30th).

Does that work for you?

Best regards,
Tony

44
Hi Alfredo,

apologies for this problem! I think I have found what is causing your issue.

Please can you try downloading a new beta version I have just released? You can download it from
https://beta.pyxll.com/download.html#standalone

If you could let me know whether that fixes your problem or not I'd appreciate it.

Kind regards,
Tony

45
Hi Alfredo,

hmm yes Excel can sometime be problematic when calling Excel functions from its own callbacks... Have you checked that it is a problem with doing it from the ribbon? You could try a normal menu function (using xl_menu) or a macro instead. Possibly it's actually something else in the recalculation that's causing problems - you could try recalculating using Ctrl+Alt+F9 to check that doesn't cause the same problem.

Assuming that recalculating manually works, one other thing you could try is to wrap your code in a macro and then call that macro from the ribbon callback, e.g.

Code: [Select]
from pyxll import xl_macro, xl_app

@xl_macro
def my_macro():
    xl_app().CalculateFull() # or you should be able to use xlcCalculate here

def ribbon_callback():
    xl_app().Run("my_macro")

One thing that will almost always causes Excel to crash is if you have circular dependencies in your functions. Excel does try to detect those and stop you from doing it, but if you have a function that writes back to Excel rather than simply passing arguments then it will not be able to.

Best regards,
Tony

Pages: 1 2 [3] 4 5 ... 36