Page 1 of 1

[Solved] VB Excel conversion

PostPosted: Thu Dec 13, 2018 12:47 am
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   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

Re: VB Excel conversion

PostPosted: Thu Dec 13, 2018 8:33 am
by Zizi64
What is the [n] in your code?
Why you pasted more than one command line together?


...and there are much more questions...

Re: VB Excel conversion

PostPosted: Thu Dec 13, 2018 8:41 am
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.

Re: VB Excel conversion

PostPosted: Thu Dec 13, 2018 8:51 am
by Zizi64
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.

Re: VB Excel conversion

PostPosted: Thu Dec 13, 2018 9:09 am
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

Re: VB Excel conversion

PostPosted: Thu Dec 13, 2018 11:24 am
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?

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 2:17 am
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

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 8:43 am
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?

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 9:26 am
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

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 3:10 pm
by JeJe
There's a push button timer posted by Charlie Young in this thread:

viewtopic.php?f=9&t=33935

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 6:39 pm
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.

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 6:42 pm
by mikele
Hi,
I attached a simple example with one start-stop-button.

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 8:05 pm
by Neil Mouat
Solved Thanks for the help guys that gives me plenty to work from.

Re: VB Excel conversion

PostPosted: Fri Dec 14, 2018 8:11 pm
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.

Re: VB Excel conversion...Solved

PostPosted: Fri Dec 14, 2018 8:27 pm
by Neil Mouat
Thanks Villeroy that gives me plenty to work with and hopefully learn from

Re: VB Excel conversion...Solved

PostPosted: Fri Dec 14, 2018 9:18 pm
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