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

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
peck797
Posts: 6
Joined: Sun Jul 27, 2008 2:48 pm

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

Post 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

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
Last edited by peck797 on Tue Jul 29, 2008 4:12 pm, edited 1 time in total.
OOo 2.4.X on Ms Windows XP
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

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

Post by squenson »

I have something different when I record similar macro:

Code: Select all

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.
LibreOffice 4.2.3.3. on Ubuntu 14.04
peck797
Posts: 6
Joined: Sun Jul 27, 2008 2:48 pm

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

Post 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
OOo 2.4.X on Ms Windows XP
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

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

Post 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().
LibreOffice 4.2.3.3. on Ubuntu 14.04
peck797
Posts: 6
Joined: Sun Jul 27, 2008 2:48 pm

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

Post 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
OOo 2.4.X on Ms Windows XP
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

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

Post 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...
LibreOffice 4.2.3.3. on Ubuntu 14.04
peck797
Posts: 6
Joined: Sun Jul 27, 2008 2:48 pm

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

Post by peck797 »

OK, thanks for your help. I'll try to modify my behaviour to suit :)

Dan
OOo 2.4.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

Code: Select all

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
peck797
Posts: 6
Joined: Sun Jul 27, 2008 2:48 pm

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

Post 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
OOo 2.4.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
rmdemi
Posts: 10
Joined: Mon Jun 30, 2008 9:45 pm

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

Post 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.
OOo 2.4.X on Ms Windows XP + linux-other
peck797
Posts: 6
Joined: Sun Jul 27, 2008 2:48 pm

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

Post by peck797 »

Any speed improvement will definitely help! Thank you for the improved macro.

Dan
OOo 2.4.X on Ms Windows XP
Post Reply