Automated Goal Seek in Macro
Posted: Sat Apr 05, 2008 12:17 am
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.
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