[Solved] VB Excel conversion

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Neil Mouat
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

[Solved] VB Excel conversion

Post by Neil Mouat »

After converting Excel Vb to Open Office Basic i am receiving syntax errors being completely new to macros can someone please help.I have tried and searched but no luck
i'm too old i think, This piece of code is meant to operate two buttons for a timer, simply start and stop .Have copied converted code.syntax error occurs on "object"

Code: Select all

Sub StartTiming()
Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ThisComponent.CurrentController.ActiveSheet[n]ThisComponent.CurrentController.select(oSheet.getCellDim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("A:A").Find("")))
    ThisComponent.getCurrentSelection.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
    
End Sub

Sub EndTiming()
Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ThisComponent.CurrentController.ActiveSheet[n]ThisComponent.CurrentController.select(oSheet.getCellDim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("B:B").Find("")))
    ThisComponent.getCurrentSelection.Value = Time
    Selection.NumberFormat = "hh:mm:ss"
    
    Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ThisComponent.CurrentController.ActiveSheet[n]ThisComponent.CurrentController.select(oSheet.getCellDim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("C:C").Find("")))
    ThisComponent.getCurrentSelection.Value = Format(Time, "Long Time")
    ThisComponent.getCurrentSelection.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Selection.NumberFormat = "mm:ss"
End Sub
Hope this is not too much to ask,Quite possibly other errors as well in which case might be easier to install Excel
Thanks in advance
Neil
Last edited by Neil Mouat on Fri Dec 14, 2018 8:26 pm, edited 2 times in total.
Open Office 4.1.6 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Post by Zizi64 »

What is the [n] in your code?
Why you pasted more than one command line together?


...and there are much more questions...
Last edited by Zizi64 on Thu Dec 13, 2018 8:48 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Post by Zizi64 »

I think, there is not ".FormulaR1C1" property in the AOO/LO API.

There are:

.Formula,
.FormulaLocal,
.String,
.Value,
you can get/set them, and you can get only:
.Error

You must study the descriptions of the API functions, and procedures, if you want to convert MS VBA macros to StarBasic+API environment. Start with Andrew Pitonyak's free macro books.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Post by Zizi64 »

Code: Select all

Selection.NumberFormat = "hh:mm:ss"
Use a predefined template (preformatted by styles), or apply your prefdefined (or created) Cell Styles by your macros instead of the direct formatting.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Neil Mouat
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Post by Neil Mouat »

Thanks for your replies guys ,that was a conversion from an online vb to open office converter thought it might be too good to be true.Never mind it's way beyond me i will
try Exceel
Neil
Open Office 4.1.6 on Windows 10
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: VB Excel conversion

Post by mikele »

Hello,
what do you want to do with this macro?
As fas as I understood: You have 2 button on a sheet (which one?). Pressing the first you want to write the time in a cell of column A, pressing the other write the time in a cell of column B and calculate the difference in a cell of column C. I believe you want to use the first empty cells of columns ABC every time you press the buttons. Is it right?
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Neil Mouat
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Post by Neil Mouat »

Hi
it was a spreadsheet i saw online for a race timer,Simply ..Column A start time...B Finish time...C difference or elapsed time. Then a start button and a stop button which
ran the macro. It was a utube video lesson so i could only copy and paste code rather than download Excel file which may have run in office. i am going to get a copy of Excel i think and work from there.
Thanks for your interest
Neil
Open Office 4.1.6 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Post by Zizi64 »

There is not a real "Timer" procedure or object in the in the AOO/LO, You can put the cells the system clock DateTime values only.
...But what about when you click on the start button (or the stop button) twice?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Neil Mouat
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Post by Neil Mouat »

yes i see that it drops down one row each button press. That i can fix . I was hoping to make a multi timer for cycling time trial and thought i might be able to change things to run different competitor on each row at different interval. Just an idea i had and willing to learn but need that code to work to give me a base to work from.
I have never seen a macro before so a complete novice and don't want to waste your time so if you think this conversion is way out please say.
Neil
Open Office 4.1.6 on Windows 10
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: VB Excel conversion

Post by JeJe »

There's a push button timer posted by Charlie Young in this thread:

viewtopic.php?f=9&t=33935
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: VB Excel conversion

Post by Lupp »

In pursuit of some basic questions triggered by another thread somewhere I sketched a stopwatch spreadsheet about 3 months ago. You find it attached. It was made with LibO 6.some and also runs under AOO though much less smoothly.
Attachments
stopWatchLupp.ods
(13.13 KiB) Downloaded 214 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mikele
Posts: 72
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: VB Excel conversion

Post by mikele »

Hi,
I attached a simple example with one start-stop-button.
Attachments
macro start-stop.ods
(12.07 KiB) Downloaded 229 times
LibreOffice 5.4, 7.0, 7.2 on LinuxMint/Win10
Neil Mouat
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Post by Neil Mouat »

Solved Thanks for the help guys that gives me plenty to work from.
Open Office 4.1.6 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VB Excel conversion

Post by Villeroy »

Apart from the little Basic snippet, my solution requires one push button for each stopwatch.
The code can be stored in the document or under "My Macros".
The button's "additional info" property takes the address of the start cell (for instance Shee1.A2). This target address can be anywhere in the same document.
The elapsed time is stored in the start cell's right neighbour cell when you push the button a second time.
The elapsed time of a running clock can be updated by hitting the F9 key.
The rest is completely up to you.
Attachments
StopWatches.ods
(26.22 KiB) Downloaded 229 times
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
Neil Mouat
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion...Solved

Post by Neil Mouat »

Thanks Villeroy that gives me plenty to work with and hopefully learn from
Open Office 4.1.6 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VB Excel conversion...Solved

Post by Villeroy »

Neil Mouat wrote:Thanks Villeroy that gives me plenty to work with and hopefully learn from
No. All you need is this:
Additional_Info.png
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
Post Reply