[Solved] Code a counter button

Creating a macro - Writing a Script - Using the API

[Solved] Code a counter button

Postby Pedroski55 » Tue Mar 21, 2017 3:24 am

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
Pedroski55
 
Posts: 22
Joined: Wed Aug 31, 2016 2:15 am

Re: code a counter button

Postby MrProgrammer » Tue Mar 21, 2017 3:51 am

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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3039
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: code a counter button

Postby Pedroski55 » Tue Mar 21, 2017 6:46 am

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
Pedroski55
 
Posts: 22
Joined: Wed Aug 31, 2016 2:15 am

Re: code a counter button

Postby robleyd » Tue Mar 21, 2017 6:59 am

Maybe it is as simple as VBA and OpenOffice Basic are two different languages with different syntax?
Cheers
David
OpenOffice 4.1.3 - Windows 7
OpenOffice 4.1.4 RC2 - Slackware 14.2 - 64 bit
User avatar
robleyd
Volunteer
 
Posts: 865
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: code a counter button

Postby JohnV » Tue Mar 21, 2017 7:01 am

This will increase the current cell's value.
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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.
JohnV
Volunteer
 
Posts: 1582
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: code a counter button

Postby Pedroski55 » Tue Mar 21, 2017 7:15 am

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
Pedroski55
 
Posts: 22
Joined: Wed Aug 31, 2016 2:15 am

Re: code a counter button

Postby robleyd » Tue Mar 21, 2017 7:30 am

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
OpenOffice 4.1.3 - Windows 7
OpenOffice 4.1.4 RC2 - Slackware 14.2 - 64 bit
User avatar
robleyd
Volunteer
 
Posts: 865
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: code a counter button

Postby Zizi64 » Tue Mar 21, 2017 8:16 am

@ 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 40 times
Tibor Kovacs, Hungary; LibreOffice4.4.7 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.4.1 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5787
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Code a counter button

Postby Villeroy » Tue Mar 21, 2017 1:57 pm

Running Linux, you do not need a 300MB office suite to add up numbers in a file:
Code: Select all   Expand viewCollapse view
#!/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   Expand viewCollapse view
$ 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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24260
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Code a counter button

Postby Pedroski55 » Wed Mar 22, 2017 1:47 am

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: 22
Joined: Wed Aug 31, 2016 2:15 am

Re: Code a counter button

Postby Pedroski55 » Wed Mar 22, 2017 4:54 am

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
Pedroski55
 
Posts: 22
Joined: Wed Aug 31, 2016 2:15 am

Re: Code a counter button

Postby Zizi64 » Wed Mar 22, 2017 7:32 am

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; LibreOffice4.4.7 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.4.1 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5787
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Code a counter button

Postby Pedroski55 » Wed Mar 22, 2017 11:24 am

Thanks very much, very helpful!
Libre Office 5 on Ubuntu 16.04
Pedroski55
 
Posts: 22
Joined: Wed Aug 31, 2016 2:15 am

Re: [Solved] Code a counter button

Postby RoryOF » Wed Mar 22, 2017 11:31 am

OpenOffice BASIC guide linked at
https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide

Entire earlier version of the guide at
https://wiki.openoffice.org/w/images/c/c1/BasicGuide_OOo3.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.3 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 24934
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Code a counter button

Postby Villeroy » Wed Mar 22, 2017 5:33 pm

My shell script works out of the box with the enter key on your keyboard.
Another solution: download/file.php?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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24260
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Code a counter button

Postby Villeroy » Thu Apr 13, 2017 5:30 pm

Nother solution without macro.
Attachments
counter.ods
(11.37 KiB) Downloaded 41 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24260
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests