Calc: How to create a simple "Goal Seek"-macro?

[Pantona]

I tried to create a simple macro to run a “Goal Seek” (same as “Tools” → “Goal Seek…”).

Let’s assume the following situation:

• Formula cell is A1
• Target value is in A2
• Variable cell is A3

I recorded a macro that contains the following code:

REM  *****  BASIC  *****


sub Goalseek
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:GoalSeekDialog", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:GoalSeek", "", 0, Array())


end sub

Unfortunately, nothing happens if I run the macro by hand. How can I adopt it to the upper cell names and make it work?

[Ratslinger]

Hello,

This routine works using .uno:GoalSeekDialog. You must not run it from the Basic IDE. Document is not available from there.

Also do not find any parameters to fill in Target Value & Variable. You would use these in args when running dispatch.

[Pantona]

@Ratslinger: I’ve created a better request that is already working, but not perfectly. You can have a look here: LibreOffice Calc: “Goal Seek” macro with target cell instead of target value?