You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@openoffice.apache.org by bu...@apache.org on 2014/04/22 06:09:04 UTC
[Issue 124723] New: calc macro using goal seek doesn't work
https://issues.apache.org/ooo/show_bug.cgi?id=124723
Issue ID: 124723
Issue Type: DEFECT
Summary: calc macro using goal seek doesn't work
Product: Calc
Version: 4.0.1
Hardware: PC
OS: Windows XP
Status: UNCONFIRMED
Severity: normal
Priority: P3
Component: programming
Assignee: issues@openoffice.apache.org
Reporter: sjmathis@verizon.net
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
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #9 from Ariel Constenla-Haile <ar...@apache.org> ---
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".
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #11 from Ariel Constenla-Haile <ar...@apache.org> ---
(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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
Edwin Sharp <el...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Keywords| |needmoreinfo
CC| |elish@apache.org
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
mroe <mr...@gmx.net> changed:
What |Removed |Added
----------------------------------------------------------------------------
Keywords| |needmoreinfo
Status|CONFIRMED |RESOLVED
Latest|4.1-dev |---
Confirmation on| |
Resolution|--- |NOT_AN_ISSUE
OS|All |Windows XP
--- Comment #6 from mroe <mr...@gmx.net> ---
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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #3 from Jack Mathis <sj...@verizon.net> ---
Comment on attachment 83243
--> https://issues.apache.org/ooo/attachment.cgi?id=83243
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
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #13 from Jack Mathis <sj...@verizon.net> ---
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
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #12 from Edwin Sharp <el...@apache.org> ---
> 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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
Edwin Sharp <el...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Keywords|needmoreinfo |
Status|UNCONFIRMED |CONFIRMED
Latest|--- |4.1-dev
Confirmation on| |
Ever confirmed|0 |1
OS|Windows XP |All
--- Comment #5 from Edwin Sharp <el...@apache.org> ---
Following steps of comment 3 - goal seek isn't working.
AOO420m1(Build:9800) - Rev. 1586681
2014-04-15_04:11:11 - Rev. 1587398
Debian
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #1 from mroe <mr...@gmx.net> ---
Please provide the steps you was doing and the resulted document with the
recorded macro.
Thank you.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
Edwin Sharp <el...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |CONFIRMED
Latest|--- |4.1-dev
Confirmation on| |
OS|Windows XP |All
--- Comment #8 from Edwin Sharp <el...@apache.org> ---
>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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
Jack Mathis <sj...@verizon.net> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |sjmathis@verizon.net
--- Comment #2 from Jack Mathis <sj...@verizon.net> ---
Created attachment 83243
--> https://issues.apache.org/ooo/attachment.cgi?id=83243&action=edit
example of sheet with failing macro
The attachment is a Calc sheet.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #10 from Edwin Sharp <el...@apache.org> ---
"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.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
--- Comment #4 from Edwin Sharp <el...@apache.org> ---
No macro in your attachment.
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
Edwin Sharp <el...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|NOT_AN_ISSUE |---
--- Comment #7 from Edwin Sharp <el...@apache.org> ---
reopening
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.
[Issue 124723] calc macro using goal seek doesn't work
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=124723
mroe <mr...@gmx.net> changed:
What |Removed |Added
----------------------------------------------------------------------------
Keywords|needmoreinfo |
--
You are receiving this mail because:
You are the assignee for the issue.
You are watching all issue changes.