Apache OpenOffice (AOO) Bugzilla – Issue 124723
calc macro using goal seek doesn't work
Last modified: 2014-04-23 02:49:52 UTC
I recorded a macro that simply used 'goal seek' to calculate a new value for a cell in a row above the cell that I was in, and then moved to the right to prepare to do another cell. The result was two lines of macro code. When I execute this macro, it does nothing. For example, I replaced the values that were in the goal cell and the changing cell with what they were before recording the macro, and then attempted to run the macro that was recorded, but nothing happens, cells don't change, and no error is reported. This works flawlessly in Excel. I apologize in advance if I'm not conforming to your procedures, but I need to make this work before I can migrate my system to Open Office. Thanks, Jack
Please provide the steps you was doing and the resulted document with the recorded macro. Thank you.
Created attachment 83243 [details] example of sheet with failing macro The attachment is a Calc sheet.
Comment on attachment 83243 [details] example of sheet with failing macro After I submitted the above issue, I tried fiddling with editing the macro to try and make it work, but without any luck. I just don't know enough about the macro language to fix it, and I haven't been able to find a good reference to the macro language yet. My feeling is that the basic problem is that the macro is missing something that should define what the variable called "Array()" is, and since the variable is undefined, nothing happens when the macro executes the goal seek command. I left a comment in the macro to that effect. To create the macro, I put the cursor on the cell B10, went up to the menu and selected "Tools > Macros > Record macro ". Then I selected "Tools > Goal seek", and entered the values of "0" in the goal value, and B2 in the changing cell value. (in other words, find the value of B2 that is necessary to force the value in B10 to go to zero. The system responded that it had found an appropriate value and asked me if I wanted to have it entered in cell B10. I said yes. I then moved the cursor one step to the right, using the arrow key. (That part of the macro works fine). I then hit the "Stop recording" button, and saved the macro that had been recorded as "Netbal". To test the macro, I set the value in B2 back to what it was before recording the macro, which is shown in the attached sheet. Then I went to "Tools > Macros > Run Macro" and selected "Netbal" which resides in Module1. The macro then runs, and the cursor jumps over to cell C10, but the value in B10 is not changed. Hope this helps, Jack
No macro in your attachment.
Following steps of comment 3 - goal seek isn't working. AOO420m1(Build:9800) - Rev. 1586681 2014-04-15_04:11:11 - Rev. 1587398 Debian
The macro that I recorded accordingly to your description: sub Netbal 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()) rem ---------------------------------------------------------------------- dim args3(1) as new com.sun.star.beans.PropertyValue args3(0).Name = "By" args3(0).Value = 1 args3(1).Name = "Sel" args3(1).Value = false dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args3()) end sub The dispatcher API doesn't provide access to all functions of AOO and works other than the macro recording in Excel. But if you only want to call the dialog for the GoalSeek, then simply delete the "rem" before dispatcher.executeDispatch(document, ".uno:GoalSeekDialog", "", 0, Array()) . If you want other results you have to use the Basic API directly. If you need help please look for help at the AOO mailing lists. http://openoffice.apache.org/mailing-lists.html For the first try subscribe to the Users Mailing List.
reopening
From user experience point of view this is a horrible bug. The macro idea was invented to make life easy, not difficult! The program doesn't offer even the minimal hint for the need to go to the code and delete for something that should just work in the first place. And user values are also not recorded. Resolving such a bug now will not prevent the need to fix this - more complaints will come in the future.
If this bug is about "how macro recording fails", then please search for dupplicated issues, I'm sure there are several bugs reporting this. On the other hand, the macro recording limitations are document in the Online Help bundled with OpenOffice, and in the Wiki, for example https://wiki.openoffice.org/wiki/Documentation/OOoAuthors_User_Manual/Getting_Started/Sometimes_the_macro_recorder_fails FOr the Online Help search the title "Recording a Macro", it has a list with "Limitations of the macro recorder".
"Limitations of the macro recorder The following actions are not recorded: Opening of windows is not recorded. Actions carried out in another window than where the recorder was started are not recorded. Window switching is not recorded. Actions that are not related to the document contents are not recorded. For example, changes made in the Options dialog, macro organizer, customizing. Selections are recorded only if they are done by using the keyboard (cursor traveling), but not when the mouse is used. The macro recorder works only in Calc and Writer." No mention of goal seek.
(In reply to Edwin Sharp from comment #10) > "Limitations of the macro recorder > The following actions are not recorded: > Opening of windows is not recorded. > Actions carried out in another window than where the recorder was started > are not recorded. > Window switching is not recorded. > Actions that are not related to the document contents are not recorded. For > example, changes made in the Options dialog, macro organizer, customizing. > Selections are recorded only if they are done by using the keyboard (cursor > traveling), but not when the mouse is used. > The macro recorder works only in Calc and Writer." > > No mention of goal seek. Of course there is no mention of every single command that can be recorded, but this text applies to Jack's observations: > Selections are recorded only if they are done by using the keyboard (cursor > traveling), but not when the mouse is used. Jack said: I then moved the cursor one step to the right, using the arrow key. (That part of the macro works fine) > Actions that are not related to the document contents are not recorded This applies to the general bug description by Jack: you can record the execution of a command that opens a modal dialog or a non-modal dialog [vid. http://en.wikipedia.org/wiki/Modal_window for the difference between them] (like the "Goal Seek" dialog), but what you do in that dialog is not recorded.
> Selections are recorded only if they are done by using the keyboard (cursor > traveling), but not when the mouse is used. A simple macro to bold text, recorded using mouse only and bold icon in formatting toolbar works! > Actions that are not related to the document contents are not recorded It takes a lot of imagination to infer that input into goal seek dialog is not recorded.
I don't know why there isn't any macro in the file that I uploaded, as it is in my file. Perhaps it got stripped out somehow? Anyway the macro shown in comment 6 is precisely what my macro shows. I haven't tried un-remarking the goal seek dialog line, as I feel the having to enter the dialog every time I run the macro kind of defeats the purpose of the macro. I'm also baffled as to why a dispatch statement is left in the macro, even though it does nothing. Why not Rem that out also, so the user can tell that the macro isn't going to do anything. Better yet, stick in a flag that indicates that you have done something that isn't supported. It isn't that using the mouse messes things up, as you can record the macro using only key strokes. (tedious, but doable) Also, if you use the mouse to click on the next cell instead of using the arrow key, that part works, although it produces a slightly different macro. Further, the fact that the goal seek statement is in the macro reflects the fact that the recorder has recognized that I've gone up to the tools menu and selected the "Goal Seek" command (with the mouse). The problem is simply that the recording system is not recording the dialog, as was stated very clearly in comment 11. I have managed to find some documentation and an example of code using goal seek, but I haven't had a chance to try it yet. I feel I can probably work around this issue. Many thanks to the references in the comments, which led me in the right direction. Finally, it would seem that some improvement in the documentation of the macro limitations would be helpful. Thanks again to all. Jack