Author Topic: Formula not returning updated cell value  (Read 577 times)

kasa

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Formula not returning updated cell value
« on: August 28, 2014, 02:36:49 PM »
Hello All,

I have a scenario where i have a UDF in Python, and exposed it to excel. Lets say that UDF (random number generation) is called in cell A1
in Cell A2 i am doing a calculation based on A1 i.e. Cell A2 would be "=A1+1"

Now, i want the value of cell A2 to be captured by the python. So i defined another UDF which takes cell as input argument.

@xl_func("xl_cell cell: float", macro=True, volatile=True)
def Output(cell):
#     doing something with cell value.
    return cell.value

and i am calling this new function in another cell, lets say A3  as '=Output(A2)'

Now, the problem is that in the output function the cell.value is returning the old value of the cell A2 and not the new one.

To illustrate more clearly:
1st Set of values:
10.8123092
11.8123092
10.40076321

2nd Set: (Doing F9)
11.43074673
12.43074673
11.8123092

3rd Set:(Doing F9)
7.984102032
8.984102032
12.43074673

The highlighted values shows that the Previous value of A2 is captured in this iteration of A3.

can someone please help me how to avoid this and capture the value of a cell, which involves a formula .

Thank you,
kasa.

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 541
  • Karma: +12/-0
    • View Profile
Re: Formula not returning updated cell value
« Reply #1 on: August 28, 2014, 03:00:20 PM »
Hi,

why is your function marked as volatile? If you remove that then excel should compute the dependencies correctly and call your function after the input has been computed. Also, it's not clear why you're using xl_cell, you could just pass the value in directly using a basic type (eg string, int, var etc).

Best regards,
Tony

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 541
  • Karma: +12/-0
    • View Profile
Re: Formula not returning updated cell value
« Reply #2 on: August 28, 2014, 04:29:26 PM »
Another thing you might want to try is a full recalculation and dependency tree rebuild. If Excel has lost track of the dependencies between your function somehow, that would explain why you're not seeing them update in the correct order.

Press Ctrl+Alt+Shift+F9 to rebuild the dependency tree and recalculate and see if that helps.

Best regards,
Tony

kasa

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Formula not returning updated cell value
« Reply #3 on: August 28, 2014, 08:49:46 PM »
Thank you very much Toni. You are right, I dont need to take XL_CELL as input. I can do with a basic data type.

The main problem is that i marked the original random number generator function as Volatile. This is because i want a change of values when i press F9. But i realized that, because of volatile= TRUE, the cell value changes on any action that is performed on sheet, such as typing some data in another cell and press of ENTER key.

So, i need the function to change values when i click on F9, not on every action performed on sheet. Please let me know if you know there is a way?

Thank you ,
Kasa.

kasa

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Formula not returning updated cell value
« Reply #4 on: August 28, 2014, 09:16:24 PM »
Got it !!!  :)

set the function as Macro=True and call Xlccaluationtype(3)

That did the magic .

Thank you,
Kasa.