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

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.