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

asked 2019-07-08 20:35:08 +0100

Pantona gravatar image

updated 2020-07-20 22:58:29 +0100

Alex Kemp gravatar image

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?

edit retag flag offensive close merge delete



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 gravatar imageRatslinger ( 2019-07-09 00:36:29 +0100 )edit

@Ratslinger: I've created a better request that is already working, but not perfectly. :-) You can have a look here:

Pantona gravatar imagePantona ( 2019-07-09 18:59:38 +0100 )edit