Make a macro update its result automatically. [closed]

asked 2019-04-26 05:40:45 +0100

Pum gravatar image

updated 2019-04-26 17:33:39 +0100

I just finished coding my first macro and I followed the necessary steps so that it can be called as a regular function in Calc.

The problem is, once I have written my custom function in a cell ( =MyFunction() ), the result gets calculated and the cell that stores the result gets updated with the correct value. However that cell doesnt show the function name like a built-in function would, it just has the result there as if I had written it myself manually. Another problem that arises is that after more data is introduced in the sheet, I need to rerun the function every time, instead of it updating automatically.

So my question is, is there anything that needs to be configured in the macro code or anywhere so that the cell that gets written by the macro gets updated automatically like it would with a build-in function?

Sory if this is a duplicate, I tried looking this up but coulnd't find anything regarding it.

Edit 1 : I do manually return the result of the macro onto a specific cell, in this case AF3

Dim AFX As Object
AFX = ThisComponent.Sheets(0).getCellRangeByName("AF3")
AFX.Value = Value

Value has been calculated properly before

Edit 2 : Okay I just figured out the problem and solution, the quote from Mike

"(are you setting content of some cells directly from your function?)"

and from Lupp

"Did you assign the result to the name of the function as required?"

Made me realise the code above is the problem, so I looked into the docs further in and saw this which went past me last time I was checking these docs: https://documentation.libreoffice.org... (End of Page 9)

I didnt realize you could assing a value to the Function Name, which results in exactly what I wanted. Basically just changed the code above for this:

Function_Name_Implementation = Value

And it works like a charm, thanks.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Pum
close date 2019-04-26 17:36:08.348341

Comments

It looks like you did something wrong in your function, because normally it's expected to work just like you need (are you setting content of some cells directly from your function?). Please provide more information by editing your question (not using "answer"!), like the function itself.

Mike Kaganski gravatar imageMike Kaganski ( 2019-04-26 08:18:07 +0100 )edit

Quoting: "...and the cell that stores the result gets updated with the correct value."
A formula doesn't "write to a cell" but return the result to the cell from where it was called. Did you use a side-effect to write the result somewhere else? Did you assign the result to the name of the function as required?

Lupp gravatar imageLupp ( 2019-04-26 12:01:19 +0100 )edit

Added some extra info to the question. Its solved now. Huge thanks to you two

Pum gravatar imagePum ( 2019-04-26 17:23:21 +0100 )edit