WorksheetFunction: Quand et pourquoi l’utiliser

En plus des fonctions spécifiques de VBA, il est possible d'utiliser en VBA certaines fonctions d'Excel – c'est-à-dire les fonctions que l'on peut utiliser dans les cellules dans un classeur Excel comme par exemple RECHERCHEV ou encore SOMME et qui n'ont pas leur équivalents en VBA. Deux possibilités s'offrent donc à nous: soit réécrire les fonctions en question en code VBA (ce qui peut être compliqué et fastidieux), soit on se sert directement dans Excel (sans tout de même passer par des cellules!). En VBA, nous pouvons faire appel à ces fonctions en utilisant l'objet WorksheetFunction.

Voici donc un aperçu des avantages & désavantages de ces fonctions, de leur utilisation en pratique (code VBA) et également la liste de toutes les fonctions Excel accessibles en VBA via WorksheetFunction (le tableau se trouve en bas de ce tutoriel).

Avantages et désavantages de l'utilisation des WorksheetFunction's

L'utilisation de WorksheetFunction est plus appropriée dans certaines situations que dans d'autres. En prenant en considération les éléments qui suivent, vous serez en mesure de savoir quand il est opportun de l'utiliser et quand il vaut mieux chercher une autre solution.

Le majeur avantage qu'offrent les fonctions Excel utilisées directement en VBA est le nombre de fonctions disponibles sans devoir écrire du code VBA. L'utilisation de n'importe laquelle de ces fonctions requiert une seule ligne de code VBA (voir plus bas la section "Utilisation de WorksheetFunction en VBA"). Vous pouvez donc profiter de la fonction RECHERCHEV avec une seule ligne de code VBA… Ou bien, comme mentionné plus haut, vous pouvez écrire en VBA votre propre fonction qui simulera la même fonctionnalité – mais n'imaginez pas que cela se fera avec une ligne de code.

Un autre avantage: grâce à des dizaines de fonctions Excel disponibles, toutes les thèmes sont couverts: dates et temps, mathématique, finance, statistiques, conversions,…

Parmi les désavantages, j'en citerais deux. Un pratique et un beaucoup plus essentiel…

1) Contrairement aux fonctions spécifiques de VBA, l'éditeur VBA (le "VBE") ne donne pas de détails concernant les argument des fonctions Excel. Vous aurez droit au nombre des arguments que la fonction utilise et à l'information si l'argument est obligatoire (sans crochets, par ex.: Arg1 ) ou optionnels (avec crochets, par ex.: [Arg2] ). Vous devez donc trouver vous-mêmes quels arguments et dans quel ordre la fonction utilise – pour cela, il vous suffit de regarder cette fonction directement dans Excel (et non dans l'éditeur VBA).

Pour illustration, voici ce que vous indiquera l'éditeur VBA à propos de la WorksheetFunction VLookup (=RechercheV): 4 arguments dont le dernier est optionnel

En regardant dans Excel, vous verrez que Arg1 correspond à la valeur recherchée, l'Arg2 représente la plage dans laquelle les données sont recherchés, l'Arg3 est le numéro de colonne qui contient le résultat recherché et dans Arg4, il faut indiquer, le cas échéant, si oui ou non il faut utiliser les valeurs approximatives. Attention: contrairement à la formule RECHERCHEV dans la version francophone d'Excel, les valeurs à indiquer dans VBA sont TRUE ou FALSE et non VRAI ou FAUX !

Ce désavantage est plutôt cosmétique et elle impacte surtout l'écriture du code VBA – mais elle n'influence pas la fonctionnalité de votre code. Ce qui n'est pas le cas pour l'autre désavantage sur lequel j'aimerais attirer votre attention.

2) Comme il s'agit des fonctions Excel, le code VBA contenant un objet WorksheetFonction ne fonctionnera que s'il tourne dans Excel. Cela ne vous posera pas de problème si vous travaillez exclusivement avec la combinaison VBA/Excel. Par contre, si vous prévoyez utiliser votre code dans d'autres applications qu'Excel (par exemple Word ou Outlook), vous ne pourrez pas utiliser cette fonctionnalité – car les fonctions Excel ne sont tout simplement pas accessibles dans, par exemple, MS Outlook.

Utilisation de WorksheetFunction en VBA (syntaxe)

L'utilisation est très simple. Vous faites l'appel à une fonction Excel avec le code suivant:

Où NomDeFonction est le nom de la fonction que vous voulez utiliser (voir la liste plus bas). Et ArgumentDeFonction1, … , ArgumentDeFonctionX représentent les valeurs des arguments de la fonction Excel utilisée (le nombre des arguments et leur nature obligatoire/facultatif varient selon la fonction).

Remarque: si vous voulez profiter des fonctions Excel dans VBA, vous devez utiliser les formes anglophones de ces fonctions! Par exemple, pour utiliser la formule RECHERCHEV en VBA, il vous faudra utiliser le terme VLOOKUP. Si vous essayez utiliser comme nom de fonction "RECHERCHEV", votre code VBA se générer une erreur…

Liste de toutes les WorksheetFunction disponibles en VBA

Et voici la liste de toutes les fonctions Excel que vous pouvez utiliser telles quelles en VBA (en anglais, par ordre alphabétique):

Remarque: comme vous pouvez le constater, le point . normalement utilisé dans les noms de fonctions Excel (par exemple NORM.DIST) est remplacé par un souligné _ car le . a un rôle particulier en VBA et ne peut pas être utilisé dans ce contexte. La fonction NORM.DIST se transforme donc en Norm_Dist.

* Cette liste se base sur les fonctions disponibles dans Excel 2010

Pour aller plus loin en VBA

Pour terminer, voici quelques articles et tutoriels qui pourraient vous être utiles dans votre travail avec VBA…

• La liste complète des Fonctions Excel en PDF
• La liste complète des fonctions VBA, des opérateurs, événements et instructions en PDF
• Gestion des dates en VBA
• Manipulation des fichiers dans les macros VBA
• Débloquer VBA dans les fichiers en provenance d'internet et d'email
• Pour en savoir encore plus sur WorksheetFunction: page y dédiée sur le site internet de Microsoft