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

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("")

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?


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.

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