[Solved] VB Excel conversion

Keyboard macros or custom scripts

[Solved] VB Excel conversion

Postby Neil Mouat » Thu Dec 13, 2018 12:47 am

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   Expand viewCollapse view
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
Neil Mouat
 
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Postby Zizi64 » Thu Dec 13, 2018 8:33 am

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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7824
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Postby Zizi64 » Thu Dec 13, 2018 8:41 am

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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7824
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Postby Zizi64 » Thu Dec 13, 2018 8:51 am

Code: Select all   Expand viewCollapse view
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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7824
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Postby Neil Mouat » Thu Dec 13, 2018 9:09 am

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
Neil Mouat
 
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Postby mikele » Thu Dec 13, 2018 11:24 am

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 on LinuxMint/WinXP/Win7
mikele
 
Posts: 44
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: VB Excel conversion

Postby Neil Mouat » Fri Dec 14, 2018 2:17 am

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
Neil Mouat
 
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Postby Zizi64 » Fri Dec 14, 2018 8:43 am

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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7824
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VB Excel conversion

Postby Neil Mouat » Fri Dec 14, 2018 9:26 am

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
Neil Mouat
 
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Postby JeJe » Fri Dec 14, 2018 3:10 pm

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

viewtopic.php?f=9&t=33935
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 459
Joined: Wed Mar 09, 2016 2:40 pm

Re: VB Excel conversion

Postby Lupp » Fri Dec 14, 2018 6:39 pm

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 12 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: VB Excel conversion

Postby mikele » Fri Dec 14, 2018 6:42 pm

Hi,
I attached a simple example with one start-stop-button.
Attachments
macro start-stop.ods
(12.07 KiB) Downloaded 13 times
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 44
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: VB Excel conversion

Postby Neil Mouat » Fri Dec 14, 2018 8:05 pm

Solved Thanks for the help guys that gives me plenty to work from.
Open Office 4.1.6 on Windows 10
Neil Mouat
 
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion

Postby Villeroy » Fri Dec 14, 2018 8:11 pm

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 14 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VB Excel conversion...Solved

Postby Neil Mouat » Fri Dec 14, 2018 8:27 pm

Thanks Villeroy that gives me plenty to work with and hopefully learn from
Open Office 4.1.6 on Windows 10
Neil Mouat
 
Posts: 6
Joined: Thu Dec 13, 2018 12:24 am

Re: VB Excel conversion...Solved

Postby Villeroy » Fri Dec 14, 2018 9:18 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests