Ceci est une ancienne révision du document !
LibreOffice Calc has hundreds of built-in functions. They cover mathematics, including geometry, trigonometry, and calculus; logical functions; cell functions; and text functions. After years of using Calc, I haven't had a need I couldn't solve or a formula I couldn't recreate using the functions in Calc. However, I am aware that the day may come when I will need a highly specialized function, a function that does something that the built-in functions can't, or calculates an extremely complicated formula. Luckily, Calc allows you to use macros as functions. You can create your own functions and call them from within a cell's formula. You can even pass the values of cells or even cell ranges to your functions.
LibreOffice Calc intègre des centaines de fonctions. Elles couvrent les mathématiques, dont la géométrie, lea trogonométrie et le calcul ; des fonctions de logique ; des fonctions sur les cellules ; et des fonctions sur les textes. Après des années d'utilisation de Calc, je n'ai jamais eu un besoin que je n'ai pas résolu ou une formule que je n'aurai pas pu recréer en utilisant les fonctions de Calc.
Cependant, je suis au courant que le jour peut arriver où j'aurai besoin d'une fonction hautement spécialisée, une fonction qui fait ce que les fonctions intégrées ne peuvent pas faire ou qui calcule une formule extrêmement compliquée. Heureusement, Calc vous permet d'utiliser des macros comme fonctions. Vous pouvez créer vos propres fonctions et les appeler dans une la formule d'une cellule. Vous pouvez même passer les valeurs des cellules et même une plage de cellules dans vos fonctions.
Create User-Defined Functions In order to create user defined functions, you first need a module for storing the macros. Create a new Calc file and save it as MacroFunctions.ods. Open the LibreOffice Basic Macros dialog: Tools > Macros > Organize Macros > LibreOffice Basic. In the Macro From list box, select the name of your file, MacroFunctions.ods. Click New. In the New Module dialog, give the module the name UserFunctions and click OK. LibreOffice will open your new module in the LibreOffice Basic IDE. The module automatically defines a blank main subroutine. You can leave it or delete it. The choice is yours. You will not use it in this case. The IDE is a mini programming editor. As a built-in macro editor in an office suite, it's actually pretty good. There are tools for testing and tracking macros, but all that is beyond the scope of today's discussion. In the IDE, you will write your first function. It is very simple. The function just returns the number five. Function NumberFive() NumberFive = 5 End Function The first line is the function definition. It begins with the word “Function” showing it as a function rather than a subroutine (Sub). The main difference between a function and subroutine is that a function returns a value. Next is the function name, NumberFive, followed by parentheses. The parentheses are required, even if they are empty. The second line of this function is the body of the function. The body of a function can contain any number of lines. This function simply returns the number 5. We return a value from a function by setting the function name (without the parentheses) equal to the value we want to return. Finally, we have the line “End Function” which denotes the end of the function. Once you have the new function typed in, click the save button.
Créer des fonctions propres à l'utilisateur
Pour créer des fonctions propres à l'utilisateur, vous avez besoin d'abord d'un module pour enregistrer vos macros. Créer un nouveau fichier Calc et sauvez-le sous le nom Macrofunctions.ods. Ouvrez la boîte de dialogue du Basic LibreOffice des macros : Outils > Macros > Gérer les Macros > LibreOffice Basic. Dans le cadre de navigation Macros de, sélectionnez le nom de votre fichier Macrofunctions.ods. Cliquez sur Nouveau. Dans la fenêtre Nouveau module, saisissez le nom UserFunctions et cliquez sur OK. LibreOffice va ouvrir un nouveau module dans l'IDE Basic LibreOffice. Le module définit automatiquement une sous-routine principale vierge.Vous pouvez la laisser ou l'effacer. A vous de choisir. Vous ne l'utiliserez pas dans cet exemple. L'IDE est un mini-éditeur de programmation. Comme c'est un éditeur de macro intégré dans la suite bureautique, il est plutôt bon. Il y a des outils pour tester et pister les macros, mais tout ceci est hors de propos aujourd'hui. Dans l'IDE, vous allez écrire votre première fonction. C'est très simple. La fonction retourne simplement le nombre cinq.
Function NumberFive()
NumberFive = 5
End Function
To use the macro in our spreadsheet, select a cell and type =NumberFive() When you press Enter, the number 5 will appear in the cell where you typed the formula that uses the function name. You can even use the function as part of a bigger formula like =2 + NumberFive() - 3 which should give you 4. Passing Arguments Having a macro you created and can use in a formula is great. To make a function really useful, you need the ability to send data to the function. You send data by the use of arguments. You can use text, numbers, cell references, and cell ranges as arguments. First, you will create a function which passes a single argument whether a cell reference or a value. Function SqrIt(x) If IsNumeric(x) Then SqrIt = x * x Else SqrIt = 0.0 End If End Function The signature of your new function is like the first one except it has a variable x in the parentheses. The x represents the value you are passing to the function. In the body of the function, it checks x to make sure it is a number. If it is a number, the function multiplies x by itself and returning the result. If x is not a number, it returns 0.0.
Pour utiliser la macro dans notre tableur, sélectionner une cellule et taper
=NumberFive()
Quand vous appuyez sur Entrée, le nombre 5 apparaît dans la cellule où vous avez tapé la formule qui utilise le nom de la fonction. Vous pouvez même utiliser la fonction dans une plus grande formule comme
=2 + NumberFive() - 3
dont le résultat est 4.
Passer des arguments
C'est bien d'avoir créer la macro et de l'utiliser dans une formule. Pour que cette fonction soit vraiment utile, vous avez besoin de pouvoir envoyer des données à la fonction. Vous envoyez des données par les biais des arguments. Comme arguments, vous pouvez utiliser du texte, des nombres, des références de cellules et des plages de cellules. D'abord, vous allez créer une fonction qui passe un seul argument, que ce soit une référence de cellule ou une valeur.
Function SqrIt(x)
If IsNumeric(x) Then
SqrIt = x * x
Else
SqrIt = 0.0
End If
End Function
La déclaration de la nouvelle fonction est presque identique à la première, à l'exception de la variable x entre parenthèses. Ce x représente la valeur que vous passez à la fonction. Dans le corps de la fonction, x est vérifié pour être sûr que c'est un nombre. Si c'est un nombre, la fonction le multiplie par lui-même et retourne le résultat. Si x n'est pas un nombre, elle retourne 0.
You can use hard-coded numbers as x when you use the function: =SqrIt(3) =SqrIt(2.4) Or you can reference cells as x: =SqrIt(A1) =SqrIt(B4) Or as part of a larger formula: =NumberFive() + SqrIt(C4) By default in LibreOffice Basic, arguments are passed by value. When you pass a cell reference to a function, the function does not receive a reference to the actual cell. Instead, the function receives the value of the cell or the result of its formula. If the cell A1 contains the number 3, or a formula that results in 3, the function SqrIt receives the value 3 and not a reference to A1.
Passing Multiple Values While passing one value to a function is useful, sometimes you need to pass multiple values. For example, you create a function that calculates the volume of a cuboid. A simplistic version of this function is represented by the function Vol (shown below). In Vol, you pass the three needed values to calculate the volume of the cuboid. The parentheses contain three variables separated by commas. In the If statement, the function checks whether each of the variables is a number, and then if they are, it returns the product of the values. Otherwise, it returns 0.0. We can use this function much as we have the others. =Vol(2, 3, 4) =Vol(A1, A2, A3) =Vol(SqrIt(A1), A2, A3) =SqrIt(Vol(A1, A2, A3))
Passing A Cell Range So far you have passed single-cell references to your functions, but you might want to pass a cell range rather than a single-cell reference. A cell range is passed to Basic functions as an array. An array is a collection of values. Cell ranges come across as multidimensional arrays, i.e. x(Row, Column). You can use a function named IsArray to determine whether the value being passed is an array. The function SumIt demonstrates the use of a cell range reference. SumIt will accept a single value or a cell range. If the argument is a single value, it returns the value. If the argument is a cell range, it sums the values of all the cells in the range. Code for this is shown on the next page, right hand side. The body of the function defines three variables, TheSum, iRow, and iCol. iRow and iCol are defined as Integers, which are whole numbers, i.e. 1, 2, 3. These two variables are the row and column placeholders. TheSum is defined as a Double, which is a real, floating point number, i.e. 2.34, and will contain our running total. TheSum is assigned an initial value of 0.0.
You have only one argument in your function definition, x. The user could send a single value, single cell reference, or a cell range. The function tests for this using an If statement. It uses the IsArray function to check if x is an array. If it is, SumIt sets up two loops. The outer loop cycles through the row lower-to-upper values. The inner loop cycles through the column lower-to-upper values. The lower values are obtained through the LBound function, and the upper values are obtained through the UBound function. Both functions take a reference to the array, x, and the dimension of the array, 1 or 2. Remember, it references the values through x(row, column). The row is the first level, and the column is the second level. In the middle of the two loops, SumIt takes the current sum, TheSum, and adds the value of the current cell in the array to it, x(iRow, iColumn). Once the first row is complete and iColumn has reached its UBound limit, the iRow increases by one, and the inner loop starts over again. This procedure continues until both iRow and iColumn have reached their UBound limits. The two Next statements end the two loops once they reach the UBound limit for their range. The Else statement handles the case when x is not an array but a single value. It sets TheSum equal to the value of the single argument.
Finally, the function returns the value of TheSum. This function allows you to actually use a cell range as your argument. You can also use a single value or cell as the argument. You can even use the function as part of a bigger function. =SumIt(A3:C6) =SumIt(A1) =SqrIt(SumIt(A1:A5)) While Calc provides you with hundreds of functions for manipulating the data in your spreadsheets, you may occasionally need a specialized function that is not easily duplicated using the functions built into Calc. Once you create a function in Basic, you can call it from a cell using a formula. You can design your functions to accept a single value, a cell reference, or a cell range. This allows you to create very versatile functions.