Automated Goal Seek in Macro

[Scunizi]

I've been banging my head against the wall trying to get Goal Seek to function inside a macro. I've only been using the Record Macro function to try to generate the macro. My skills at VB or OO Basic are non-existent. One of the constraints of Goal Seek is manually entering the Target Value. Since I'm converting from Excel I find this requirement limiting.

After recording a macro I can't seem to get it to run even to the point of manually entering the Target Value.. Below you will see what is contained in the recorded macro.. I noticed this shows a lot of "point to" statements probably because this "recording" was done with the mouse interacting.. Basically the 3 cells I need to run Goal Seek on are D13=Variable Cell, D14=Formula Cell and D15=Target Value.. The resulting number needs to be entered into D13, teh Variable Cell. The target value is calculated else where in the sheet and changes each time I use the sheet.

If anyone can help me figure this out it would be much appreciated.

Code: Select all

Sub Macro1

End Sub


sub First_Column_Calc
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 ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Navigator"
args1(0).Value = true

dispatcher.executeDispatch(document, ".uno:Navigator", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Navigator"
args2(0).Value = false

dispatcher.executeDispatch(document, ".uno:Navigator", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$D$13"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

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

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


end sub

[Villeroy]

My skills at VB or OO Basic are non-existent.

So the following information may be not helpful at all:
Each spreadsheet document supports service com.sun.star.sheet.SpreadsheetDocument with interface com.sun.star.sheet.XGoalSeek
Method goalSeek returns a structure com.sun.star.sheet.GoalResult with two values for divergence and result.