Define global variables

user_4999991 0
Viewed 4959

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?

libreoffice-basic libreoffice-calc global-variables
3 Answers

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.

Define Name

Now enter =MyVar1 * 2 in a cell.

Cell formula

user_51005640

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

Variable cell default name

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.

Set name for cell/range of cells

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

Using a variable name in a calculation

user_7987120

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:

    Enter image description here

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

    Enter image description here

  3. Click OK (Module1 is OK).

    Enter image description here

    Now, the Basic IDE should appear:

    Enter image description here

  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.

user_3425460
Related