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?

Hailee

Answer 1

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:

1. In Calc, open menu Tools → Macros → Organize Macros → LibreOffice Basic:

2. At the left, select the current document test1.ods, and click New...:

3. Click OK (Module1 is OK).

   Now, the Basic IDE should appear:

4. 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]
   

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.

Forehead

I like the idea, although there is an easier way that avoids macros. – Ceroplastic

@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. – Forehead

Answer 2

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.

Ceroplastic

This is actually way easier than the other answer, Thanks! – Hailee

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) – Transponder

Answer 3

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:

Transponder