[Solved] Code a counter button

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Pedroski55
Posts: 40
Joined: Wed Aug 31, 2016 2:15 am

[Solved] Code a counter button

Post by Pedroski55 »

I want a button in my spreadsheet. When I click this button, the number in a cell should increase by 1. I have:

Sub Main()
Dim x As Integer
x = Range("A1").Value
Range("A1").Value = x + 1
End Sub

but it is not working. Maybe a semicolon missing? Or totally wrong?

And, if I may, how should I set security settings so that my macros can run?
Last edited by Hagar Delest on Wed Mar 22, 2017 9:36 am, edited 1 time in total.
Reason: tagged [Solved].
Libre Office 5 on Ubuntu 16.04
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: code a counter button

Post by MrProgrammer »

Pedroski55 wrote: When I click this button, the number in a cell should increase by 1.
[Solved] Push Buttons Performing Math in Cells

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Pedroski55
Posts: 40
Joined: Wed Aug 31, 2016 2:15 am

Re: code a counter button

Post by Pedroski55 »

Thanks a lot! Contrary to my expectations, I managed to make the spin buttons work!

I would still like to know what is wrong with the above basic code. It works for a friend who uses Excel. He sent me the code!

I get this error: BASIC runtime error.
Sub-procedure or function procedure not defined.
Last edited by Pedroski55 on Tue Mar 21, 2017 7:01 am, edited 1 time in total.
Libre Office 5 on Ubuntu 16.04
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: code a counter button

Post by robleyd »

Maybe it is as simple as VBA and OpenOffice Basic are two different languages with different syntax?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
JohnV
Volunteer
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: code a counter button

Post by JohnV »

This will increase the current cell's value.

Code: Select all

Sub PlusOne
oDoc = ThisComponent
oCell = oDoc.getCurrentSelection 
oCell.Value = oCell.Value + 1
End Sub
And assuming A1 is on Sheet1 then this will do it.

Code: Select all

Sub PlusOne
oDoc = ThisComponent
oSheet = oDoc.getSheets.getByName("Sheet1")
oCell = oSheet.getCellRangeByName("A1")
oCell.Value = oCell.Value + 1
End Sub
Last edited by JohnV on Tue Mar 21, 2017 7:44 am, edited 1 time in total.
Pedroski55
Posts: 40
Joined: Wed Aug 31, 2016 2:15 am

Re: code a counter button

Post by Pedroski55 »

Ok, in OO BASIC how should I define Range("A1").Value = x + 1

@ JohnV: tried your code, get this: BASIC runtime error.
Property or method not found: Value.
Libre Office 5 on Ubuntu 16.04
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: code a counter button

Post by robleyd »

Have a look at this post which may give you some insight.

Disclaimer; I don't use Basic, but it looks like some of the code in that post is what you want.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: code a counter button

Post by Zizi64 »

@ JohnV: tried your code, get this: BASIC runtime error.
Property or method not found: Value.
The code works for me fine in my LibreOffice 4.4.7. But it will not work if you use it in an .xls file and you can try open it in MS Excel. The MS VBA and the API of the AOO/LO are not compatible. (API = Application Programming Interface. You need call the API function for this task from the StarBasic or from the other supported programming language.)
PlusOne.ods
(9.79 KiB) Downloaded 190 times
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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Code a counter button

Post by Villeroy »

Running Linux, you do not need a 300MB office suite to add up numbers in a file:

Code: Select all

#!/bin/bash
COUNTFILE=~/counter.txt
COUNTVAR=0

function finish {
    echo $COUNTVAR > $COUNTFILE
    echo -e "\nCaught EXIT and saved $COUNTVAR to $COUNTFILE"
}

trap finish EXIT

if [ -f $COUNTFILE ]; then COUNTVAR=`cat  $COUNTFILE`; fi
while true; do
    echo -e "Add integer to $COUNTVAR [default 1]: \c"
    read NUM
    if [ -z $NUM ]; then NUM=1; fi
    let COUNTVAR+=$NUM
done
Save it as ~/bin/counter.sh and make it executable with chmod +x ~/bin/counter.sh.
Start a terminal and run counter.sh
Finish with Ctrl+C or by closing the terminal.
It looks like this:

Code: Select all

$ counter.sh
Add integer to 129 [default 1]: ads
Add integer to 129 [default 1]: gfds
Add integer to 129 [default 1]: 
Add integer to 130 [default 1]: 
Add integer to 131 [default 1]: 12
Add integer to 143 [default 1]: ^C
Caught EXIT and saved 143 to /home/username/counter.txt
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
Pedroski55
Posts: 40
Joined: Wed Aug 31, 2016 2:15 am

Re: Code a counter button

Post by Pedroski55 »

Thanks for all the tips!

I also use Libre Office and Ubuntu. I try to avoid anything Microsoft.

Also got a cold so not so fit right now. I'll try all suggestions, see if I can make it work.

My friend uses this counter to add up cups of coffee sold. He and his roommate have started selling coffee on campus from their dorm. He added a button to his Excel file and each time he sells a certain kind of coffee, he clicks the button to keep a record of how much of what he is selling. I thought that is a handy thing, might be useful for me, not for selling coffee though.
Libre Office 5 on Ubuntu 16.04
Pedroski55
Posts: 40
Joined: Wed Aug 31, 2016 2:15 am

Re: Code a counter button

Post by Pedroski55 »

Thank you all for your patience, I am never going to be a coder! I got it working in the end!

I downloaded the file from Zizi64, and thanks a lot for that! It works great!

Also the 2nd code from JohnV is great and it works! Thanks a lot! Maybe I had a security issue. I have the security set to low, but that's not good.

I guess I just need a list of Open Office Basic commands and syntax, I can learn them slowly. Anyone know a good link?
Libre Office 5 on Ubuntu 16.04
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Code a counter button

Post by Zizi64 »

Thanks a lot! Maybe I had a security issue. I have the security set to low, but that's not good.
It is enough to set the macro security level to Medium. In this case you need to enable (manually) the macros located in a document.
You can store the macros in the Standard directory of the MyMacros in the User profile of the AOO/LO. In this case not needed enable the macros at every launching a file that uses some macros.


I guess I just need a list of Open Office Basic commands and syntax, I can learn them slowly.
These "commands" (functions) are not the parts of the OpenOffice Basic (StarBasic), but they are API functions.
You can use the API functions with all of the supported programming languages.
(API - Application Programming Interface)


Anyone know a good link?
You must study the API functions, and the usage methods of them.
Andrew Pitonyak's macro books are good starting points for you.
http://www.pitonyak.org/oo.php

http://api.libreoffice.org/
http://www.openoffice.org/api/
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.
Pedroski55
Posts: 40
Joined: Wed Aug 31, 2016 2:15 am

Re: [Solved] Code a counter button

Post by Pedroski55 »

Thanks very much, very helpful!
Libre Office 5 on Ubuntu 16.04
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Code a counter button

Post by RoryOF »

OpenOffice BASIC guide linked at
https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide

Entire earlier version of the guide at
https://wiki.openoffice.org/w/images/c/ ... o3.2.0.pdf

Although this is for an earlier version, I think any changes (additions?) are unlikely to be relevant or cause difficulty for most users.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Code a counter button

Post by Villeroy »

My shell script works out of the box with the enter key on your keyboard.
Another solution: http://forum.openoffice.org/en/forum/do ... hp?id=3048 with an input form and a database table. The contained report shows what has been saved when.
You can also put a numeric form control onto a sheet, link a cell to it and use the up/down arrow without any macro.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Code a counter button

Post by Villeroy »

Nother solution without macro.
Attachments
counter.ods
(11.37 KiB) Downloaded 227 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
Post Reply