I was wondering if there is a built-in method to handle Excel error values (#N/A, #VALUE etc.). I noticed that Python reads in those values as large negative integers. Specifically, (-2146826246, -2146826281, -2146826265, -2146826259, -2146826273, -2146826252) correspond to #N/A, #DIV/0!, #REF!, #NAME?, #VALUE!, and #NUM! respectively.
As a workaround, I wrote a simple function that checks for these numerical values and handles them accordingly, but this approach has the obvious drawback that it does not distinguish between an error value and a numerical input equal to any of those negative numbers.
Many thanks in advance for your thoughts.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes I am aware of this problem - unfortunately there is little that can be done about it as long as ExcelPython is based on PyWin32, because it is this library that converts Excel error values (which actually arrive as a special variant type) to int.
There is, however, a useful trick I can share with you: Excel cell values always arrive as floats, never as ints (if I remember correctly - please verify that this is indeed the case) so you know that if you get -2146826246 as an int, it's an error, whereas if you get -2146826246.0 as a float, it's the actual numerical value. In Python you can test this with type(x) is float and type(x) is int.
Please let me know if this works for you.
Best regards,
Eric
Last edit: Eric Reynolds 2015-07-29
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thank you very much for your reply. I tried the trick you suggested and it works. You are right that Excel values are read into Python as floats, even if the number itself has no fractional part. Nice workaround!
Thanks again for the quick and very helpful reply, and of course for creating excelpython to begin with - it works very well and it is a great tool for bringing powerful Python functions into Excel.
Best regards,
Florin
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I was wondering if there is a built-in method to handle Excel error values (#N/A, #VALUE etc.). I noticed that Python reads in those values as large negative integers. Specifically, (-2146826246, -2146826281, -2146826265, -2146826259, -2146826273, -2146826252) correspond to #N/A, #DIV/0!, #REF!, #NAME?, #VALUE!, and #NUM! respectively.
As a workaround, I wrote a simple function that checks for these numerical values and handles them accordingly, but this approach has the obvious drawback that it does not distinguish between an error value and a numerical input equal to any of those negative numbers.
Many thanks in advance for your thoughts.
Hi Florin,
Yes I am aware of this problem - unfortunately there is little that can be done about it as long as ExcelPython is based on PyWin32, because it is this library that converts Excel error values (which actually arrive as a special variant type) to int.
There is, however, a useful trick I can share with you: Excel cell values always arrive as floats, never as ints (if I remember correctly - please verify that this is indeed the case) so you know that if you get -2146826246 as an int, it's an error, whereas if you get -2146826246.0 as a float, it's the actual numerical value. In Python you can test this with
type(x) is floatandtype(x) is int.Please let me know if this works for you.
Best regards,
Eric
Last edit: Eric Reynolds 2015-07-29
Hi Eric,
Thank you very much for your reply. I tried the trick you suggested and it works. You are right that Excel values are read into Python as floats, even if the number itself has no fractional part. Nice workaround!
Thanks again for the quick and very helpful reply, and of course for creating excelpython to begin with - it works very well and it is a great tool for bringing powerful Python functions into Excel.
Best regards,
Florin