Define global variables

Question

I'm trying to test some algorithms in LibreOffice Calc and I would like to have some global variables visible in all cell/sheets. I searched the Internet and all the posts I have seen are so cryptic and verbose!

What are some simple instructions of how can I do that?

score 14 · Answer 1 · edited Oct 21 '21 at 19:33

14

Go to Sheet → Named Ranges and Expressions → Define. Set name to "MyVar1" and expression to 5. Or for strings, use quotes as in "foo". Then press Add.

Now enter =MyVar1 * 2 in a cell.

edited Oct 21 '21 at 19:33

Peter Mortensen

30,738
21
105
131

answered Nov 22 '17 at 17:00

Jim K

12,824
2
22
51

1

This is actually way easier than the other answer, Thanks! – Foad S. Farimani Nov 22 '17 at 19:45
3

To add to Jim's answer, you can quickly define a cell as a variable by selecting it and entering the desired variable name into the 'Name Box' (e.g. where it says 'A1' in Jim's second screenshot) – krd Apr 28 '18 at 21:55

score 5 · Answer 2 · edited Oct 21 '21 at 19:34

5

One strategy is to save the global variables you need on a sheet:

Select the cell you want to reference in a calculation and type a variable name into the 'Name Box' in the top left where it normally says the Cell Column Row.

Elsewhere in your project you can reference the variable name from the previous step:

edited Oct 21 '21 at 19:34

Peter Mortensen

30,738
21
105
131

answered Apr 28 '18 at 22:15

krd

2,096
2
14
11

score 3 · Accepted Answer · edited Oct 21 '21 at 19:38

Using user-defined functions should be the most flexible solution to define constants. In the following, I assume the current Calc spreadsheet file is named test1.ods. Replace it with the real file name in the following steps:

In Calc, open menu Tools → Macros → Organize Macros → LibreOffice Basic:
At the left, select the current document test1.ods, and click New...:
Click OK (Module1 is OK).

Now, the Basic IDE should appear:

Below End Sub, enter the following BASIC code:

 Function Var1()
     Var1 = "foo"
 End Function

 Function Var2()
     Var2 = 42
 End Function

The IDE should look as follows:

[![Enter image description here][5]][5]

Hit Ctrl + S to save.

This way, you've defined two global constants (to be precise: two custom functions that return a constant value). Now, we will use them in your spreadsheet. Switch to the LibreOffice Calc's main window with file test1.ods, select an empty cell, and enter the following formula:

=Var1()

LibreOffice will display the return value of your custom Var1() formula, a simple string. If your constant is a number, you can use it for calculations. Select another empty cell, and enter:

=Var2() * 2

LibreOffice will display the result 84.

I like the idea, although there is an easier way that avoids macros. — Jim K, Nov 22 '17 at 17:18
@JimK I agree - i didn't knew that you can explicitly set the scope of named ranges. +1 for your solution, i would recommend it as accepted answer. — tohuwawohu, Nov 23 '17 at 12:17

Define global variables

3 Answers3

Linked