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, la trigonomé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 ne pouvais pas recréer en utilisant les fonctions de Calc.
Cependant, je sais 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 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 ranger vos macros. Créez 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 (Integrated Development Environment - Environnement intégré de développement) du Basic LibreOffice. Le module définit automatiquement une sous-routine principale vierge. Vous pouvez la laisser ou l'effacer ; à vous de choisir. Vous ne l'utiliserez pas dans cet exemple. L'IDE est un mini-éditeur de programmation. En tant qu'é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éé 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 le biais d'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.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.
Vous pouvez donner des valeurs numériques à x quand vous utilisez la fonction :
=SqrIt(3) =SqrIt(2.4)
ou faire référence à des cellules pour x :
=SqrIt(A1) =SqrIt(B4)
ou utiliser la fonction dans une formule plus grande :
=NumberFive() + SqrIt(C4)
Par défaut dans LibreOffice Basic, les arguments sont passés par valeur. Quand vous passez la référence d'une cellule à une fonction, la fonction ne reçoit pas une référence de la vraie cellule. A la place, la fonction reçoit la valeur de la cellule ou le résultat de la formule qu'elle contient. Si la cellule A1 contient le chiffre 3, ou une formule dont le résultat est 3, la fonction SqrIt reçoit la valeur 3 et pas une référence à 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))
Passer des valeurs multiples
Si passer une valeur à une fonction est utile, parfois vous avez besoin de passer plusieurs valeurs. Par exemple, vous créez une fonction qui calcule le volume d'un parallélépipède. Une version simpliste de cette fonction est représentée par la fonction Vol (voir ci-dessous).
Dans Vol, vous passez le trois valeurs nécessaires pour calculer le volume d'un parallélépipède. Les parenthèses contiennent trois variables séparées par des virgules. Dans la déclaration If, la fonction vérifie si chacune des variables est un nombre, et si c'est vrai, elle retourne le résultat du produit des valeurs. Sinon, elle retourne 0.0.
Nous pouvons utiliser cette fonction comme nous l'avons fait pour les autres :
=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.
Passer une plage de cellules
Jusqu'ici, vous avez passé des références à une cellule dans vos fonctions, mais vous pouvez passer une plage de cellules plutôt qu'une référence unique. En Basic, une plage de cellules est passée à une fonction comme une matrice. Une matrice est un ensemble de valeurs. Les plages de cellules sont transmises comme des matrices multi-dimensionnelles, par exemple x(ligne, colonne). Vous pouvez utiliser une fonction appelée IsArray pour déterminer si la valeur passée est une matrice. La fonction SumIt démontre l'utilisation de la référence à une plage de cellules. SumIt acceptera une valeur simple ou une plage de cellules. Si l'argument est une simple valeur, il retourne la valeur. Si l'argument est une plage de cellules, il additionne les valeurs de toutes les cellules de la plage. Le code pour cette fonction est présenté à la page suivante, à droite.
Le corps de la fonction définit trois variables, TheSum, iRow, et iCol. iRow et iCol sont définis comme entiers, c'est-à-dire des nombres entiers comme 1, 2, 3… Ces deux variables sont les index de ligne et de colonne. TheSum est défini comme Double, c'est-à-dire un nombre réel, en virgule flottante, par exemple, 2,34. ; il contiendra le résultat courant. TheSum est initialisé à 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.
Il n'y a qu'un seul argument dans la définition de la fonction, x. L'utilisateur peut envoyer, soit une simple valeur, soit la référence d'une cellule, soit une plage de cellules. La fonction fait un test en utilisant la déclaration If. Elle utilise la fonction IsArray pour déterminer si x est une matrice. Si c'est le cas, SumIt parcourt deux boucles. La boucle extérieure parcourt les valeurs de ligne en ordre croissant. La boucle intérieure parcourt les valeurs croissantes des colonnes. Les limites basses sont trouvées avec la fonction Lbound et les bornes hautes avec la fonction Ubound. Ces deux fonctions prennent en référence la matrice x et la dimension de la matrice, 1 ou 2. Souvenez-vous que les valeurs sont référencées par l'intermédiaire de x(ligne, colonne). La ligne est la première valeur, la colonne la deuxième. Au cœur des deux boucles, SumIt prend la valeur de somme courante, TheSum, et lui ajoute la valeur de la cellule courante de la matrice, x(ligne, colonne). Quand la première ligne est finie et que iCol a atteint sa limite Ubound, l'index iRow s'incrémente d'un et la boucle intérieure est relancée. Ce processus continue jusqu'à ce que iRow et iCol atteignent leurs limites Ubound. Les deux déclarations Next terminent les boucles quand elles atteignent leurs limites respectives Ubound. La déclaration Else traite le cas où x n'est pas une matrice mais une valeur simple. TheSum prend la valeur de l'argument unique.
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.
Enfin, la fonction retourne la valeur de TheSum.
Cette fonction vous permet d'utiliser une plage de cellules comme argument. Vous pouvez aussi utiliser une simple valeur ou une cellule comme argument. Vous pouvez même utiliser la fonction dans une fonction plus conséquente.
=SumIt(A3:C6) =SumIt(A1) =SqrIt(SumIt(A1:A5))
Bien que Calc vous fournisse des centaines de fonctions pour manipuler des données dans vos feuilles de calcul, vous pouvez avoir besoin occasionnellement d'une fonction spécialisée qu'il n'est pas facile de construire en utilisant les fonctions intégrées de Calc. Une fois que vous avez créé une fonction en Basic, vous pouvez l'appeler dans une cellule avec une formule. Vous pouvez définir vos fonctions pour qu'elles acceptent de simples valeurs, une référence à une cellule ou à une plage de cellules. Ceci vous donne la possibilité de créer des fonctions très variées.