Page 1 of 1

[Solved] Macros don't seem to know the current cell

PostPosted: Sun Jul 27, 2008 3:03 pm
by peck797
I've recorded a macro that puts "Now" into the current cell and tied it to a hot-key.

When I hit the macro hot-key and quickly move to the next cell, then the macro result gets pasted into the next cell, not the cell that was active when I hit the hot-key. This works as I would expect in Excel (i.e. the macro remembers the active cell when it was invoked) and pastes the result into that cell and not the cell that just happens to be active at the time that it gets around to doing the pasting.

The macro that was produced by the recorder seems very complex for what I want it to do. Is there an easier macro that will work as I want? In any case, is it a bug that the macro does not remember the cell that was active when it was created?

Thanks,

Dan

The macro that was created by the recorder (somewhat top-heavy for the simple task):

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Sub Main

End Sub


sub InsertNow
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 ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
Dim k as string
k = now
args2(0).Value = Str(Now)

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

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:GoDown", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args4())


end sub

Re: Macros don't seem to know the current cell

PostPosted: Sun Jul 27, 2008 5:20 pm
by squenson
I have something different when I record similar macro:
Code: Select all   Expand viewCollapse view
sub Main
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 = "StringName"
args1(0).Value = "=now()"

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


end sub


The uno:EnterString inserts a string at the current location, so when you run the macro with a different active cell, it uses it to place the formula. If you want the macro to always place =now() in the same cell, then you have to record as the first action a click on that cell to select it, then enter the formula.

Re: Macros don't seem to know the current cell

PostPosted: Sun Jul 27, 2008 5:29 pm
by peck797
Thank you for the suggestion. Unfortunately, I don't want the macro to put the value into the same cell all of the time. I want it to put the value into the cell that is active when I start the macro. In fact, this cell will certainly be different each time (the workbook is maintaining a history of times that things happen), so I cannot just make the macro change the active cell to a fixed point; it must use the "current" cell, whatever it happens to be.

I need a way for the macro to remember the cell that was active when I started it; this is what Excel seems to do and it is the most intuitive behaviour for me.

Dan

Re: Macros don't seem to know the current cell

PostPosted: Sun Jul 27, 2008 5:42 pm
by squenson
So your macro should work (it works on my computer as you expect, filling the current cell with the current time), as it does not reference any cell prior to execute. Try also mine, it uses the current cell as well to put the formula =Now().

Re: Macros don't seem to know the current cell

PostPosted: Sun Jul 27, 2008 5:50 pm
by peck797
It works if I do not move to another cell very quickly. If I hit the hotkey and then tab in quick succession, the value gets pasted into the adjacent cell, not the one that was active at the moment that I hit the hotkey.

I am in the habit of hitting hotkey+tab because, as I said, this macro is recording a history of events across a single row or down a column, so the two actions "hotkey+move cursor" always go together. With OO, I must hit the hotkey, pause while the macro runs and puts in the value, then hit tab to move to the next column, for example. Excel allows me to hit hotkey+tab quickly and still puts the value where I expected it and not into the new active cell.

The required "pause" is only a half-second or so but it is definitely required by OO on my machine and is not required by Excel. It may not seem a lot but it is distracting, as I have to take my eye off the thing that I should be watching in order to make sure that OO is working properly :)

I can't make the macro move the active cell because sometimes I need to move across and sometimes down (hotkey+down). So hard-coding the move wouldn't help either. I would just like the macro to put the value where it is when I ran it.

Dan

Re: Macros don't seem to know the current cell

PostPosted: Sun Jul 27, 2008 6:09 pm
by squenson
OOo Calc is definitively slower than Excel, so if you move quickly the current cell, the macro may consider the cell really active at the moment it does the uno:EnterString, and therefore not achieve what you want. I am afraid that there is no simple solution for the 1/2 second delay induced by Calc's performances...

Re: Macros don't seem to know the current cell

PostPosted: Sun Jul 27, 2008 6:40 pm
by peck797
OK, thanks for your help. I'll try to modify my behaviour to suit :)

Dan

Re: Macros don't seem to know the current cell

PostPosted: Mon Jul 28, 2008 11:28 am
by Villeroy
Code: Select all   Expand viewCollapse view
Sub NowToActiveCell()
' calls: getActiveCell
oView = ThisComponent.getCurrentController()
oCell = getActiveCell(oView)
oCell.setFormula("=NOW()")
oCell.setValue(oCell.getValue())
End Sub

Two versions of getActiveCell to get the active cell of a given spreadsheet view
The above method leaves the number formatting up to the user. setFormula("=NOW()") works with all locales independent from any formatting. It does even work with number format "Text" which would normally reject all input of formulae and numbers.

Re: Macros don't seem to know the current cell

PostPosted: Mon Jul 28, 2008 4:53 pm
by peck797
Thank you for your reply. However, unless I misunderstand what you're suggesting, I think that's a solution to a different problem.

The OO macro that I have can get what it thinks is the active cell, but it frequently gets the wrong one when the active cell is changing quickly enough. The trouble is that the macro seems to use the cell that is active at the instant at which it reaches that part of the code, not the one that was active when I invoked the macro.

Excel, on the other hand, seems to take a snapshot and remembers the cell that is active at the time that the macro starts and uses that as its working context. This results in more consistent behaviour.

The result is that the XL macro always does the same thing, whereas OO's behaviour depends on how long the macro takes to execute (how busy the CPU is, etc).

Dan

Re: Macros don't seem to know the current cell

PostPosted: Mon Jul 28, 2008 7:44 pm
by Villeroy
The macro inserts the current time into the active cell, whatever there may be selected as long as ThisComponent represents a spreadsheet. It should be much faster than the recorded dispatches from your initial post.

Re: Macros don't seem to know the current cell

PostPosted: Mon Jul 28, 2008 10:16 pm
by rmdemi
Excuse me, peck797

Villeroy's macro writes the formula to active cell, what is the problem? If you want everything same as excel or vba, it isn't possible.

İt is important that, which sheets Activecell we are talking about. Because we have a lot of active cells. in starbasic you must point this cell.(But vba activecell is enough).

So Squenson and Villeroy give us good snippets. Now topic is solved. Please, edit this thread's initial post and add "[Solved]" to the subject line.

Thanks.

Re: Macros don't seem to know the current cell

PostPosted: Tue Jul 29, 2008 4:11 pm
by peck797
Any speed improvement will definitely help! Thank you for the improved macro.

Dan