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?

Answer 1

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.

Answer 2

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:

Answer 3

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.