Senior author, Open Source enthusiast.

Saturday, February 9, 2019 at 17:40

LibreOffice Calc Macro Example: Simple Cash Register

This is a step by step tutorial to make LibreOffice Calc macro examples with SUM function. We will make a simple cash register program within Calc. You will make use the =SUM() function to total the prices. You will create two macros and call them from two push buttons on the Calc sheet. Continuing my first macro tutorial ever, this is also intended for new comers without prior experience about macro but want to create one. Happy working!

Subscribe to UbuntuBuzz Telegram Channel to get article updates directly.

About LibreOffice, see also Macro Intro | Notebookbar Review | Impress Custom Template | AppImage

Summary

Result first
Table you should create
First step, create calculation macro
Second step, create clearance macro
Third step, test them
Fourth step, create buttons
Fifth step, play
Bonus, create custom toolbar like in the animations
Closing words
Further readings

Result First

The program is really simple. It first calculate every item amount, then total all amounts. Only that. There are only two buttons, one to calculate the total price, and one to clear all cells. I hope you can grasp the basic by this.

(Gif animation: final result of this simple tutorial)

The Table Used

Create this blank table (from A1 to D7) as the place to run your macros later.

First: Create 'Calculate' Macro

This macro will calculate subtotals and total. Please remember, to record spreadsheet functions (SUM, AVG, etc.) you use click and Shift+click instead of typing the cell addresses and ranges. We will name this macro 'calculate1'.

0) Record macro
1) Type under Quantity column: from 1 to 5
2) Type under Price column: from 100 to 500
3) Type = on D2 and click B2 and type * and click C2 and press Enter
4) Drag the result of D2 down to D6
5) Type =SUM( on D7 and click D1 and Shift+Click D6 and press Enter
6) Stop recording macro
7) Name it calculate1

(Gif animation: record a macro to calculate cells)

Second: Create 'Delete' Macro

This macro will empty all cells. We will name this macro 'delete1'.
0) Record macro
1) With keyboard arrow keys, select cells from B2 to D6 and press Delete
2) Select D7 and press Delete
3) Stop recording macro
4) Name it delete1

(Gif animation: record a macro to delete cells)

Third: Test Macros

Before adding any button, we need to test the created macros. Gif animation below shows the two macros as two toolbar buttons (see bonus section below to create your own custom toolbar) to call them quickly as I want. See I filled up the cells and pressed 'calculate1', then it calculates. See I pressed 'delete1', then all cells emptied. That means the test result is OK.

(Gif animation: test created macros quickly and easily)

Fourth: Add Buttons

To add user interface elements like push button, you will need Form Controls toolbar. Go to menu bar View > Toolbars > Form Controls to show it.

Add first button and assign 'calculate1' macro.
Add second button and assign 'delete1' macro.

This gif animation shows how to add new buttons:

(Gif animation: creating buttons on the sheet)

And this animation shows how to assign a macro to a button:

(Gif animation: assign macros to buttons)

See? It is very simple. Up to this point you have completed all necessary tasks.

Finally: Play

Now, play your buttons like in the first animation above. Congratulations, you have created first calculation macro on LibreOffice Calc.

How To Create Custom Toolbar

As a bonus, you can create a custom toolbar that contains your frequently used menus as well as your most used macros. Just like gif animations above! To do so,

1) go to menu bar Tools > Customize > Toolbar > create a new toolbar > name it 'Macro' > Add Command > from left panel: select BASIC > from right panel: select Record Macro and press Add > back to left panel: select LibreOffice Macros > My Macros > Standard > Module1 > from right panel: select a macro and press Add > OK

2) call your custom toolbar: go to menu bar View > Toolbars > 'Macro' > it appears.

(LibreOffice: setup a new custom toolbar)

See? Nothing hard, right?

Closing Words

That's all. I would like to say thank you to soukaina as her video Les macros avec le tableur Calc(LibreOffice) (in French) is the base of this tutorial. Thank you, your have made your video very clear, I can learn so much from your one video although I don't understand French. Anyway, the macros in this article still do not have feature to record every transaction or to calculate taxes or even more advanced, to print out the receipt. These macros are just the basic things. It is very exciting for myself as this is my first chance ever to learn about macro and I have been wanted to learn it since a long time ago. I hope I can record my own learning process in the next tutorials. Happy working!