Manipuler les formules

Formules sous Excel

Il existe des centaines de formules disponibles sous Excel et même un utilisateur très avancé n’en utilisera qu’une infime proportion. Sans avoir besoin d’être un expert en formules et fonctions Excel, voici pour débuter un bref rappel de l’onglet Formules dans le ruban Excel et quelques erreurs en sortie de formules auxquelles vous aurez à faire en VBA.

Onglet Formules

Dans le ruban Excel, les formules ont leur propre onglet dédié.

Le ruban comporte un groupe Bibliothèque de fonctions où les formules sont regroupées par catégories.

Vous pouvez saisir vous-même vos formules directement dans les cellules ou dans la barre de formules.

Les formules commencent par le signe = et elles utilisent aussi bien des fonctions Excel que des cellules ou des valeurs écrites « en dur ».

=A1+B2 
=ANNEE(C3) 
=TEXTE(AUJOURDHUI();"AAAA-MM") 

Exemple 1 : différents types de formules Excel

Chacune de ces formules fournit en sortie une valeur, de différent type de données selon les valeurs et les fonctions utilisées (texte, valeur numérique, date, booléen, etc.).

Formules en VBA

Comme vous allez le découvrir dans les sections suivantes, il y a plusieurs manières de rédiger vos formules en VBA. Selon votre aisance avec la langue de Shakespeare ou encore selon vos contraintes d’entreprise, vous serez amené à utiliser l’une ou l’autre des méthodes.

Utiliser l’Enregistreur de macros pour commencer

Pour commencer, utilisez l’Enregistreur de macros pour voir quel code est généré par défaut lorsque vous ajoutez des formules dans les cellules.

Manipulations à effectuer

Voici les cinq étapes à réaliser :

1 - Remplir les cellules

 À partir d’un nouveau classeur ou d’un classeur existant, saisissez dans les cellules A1 à C3 les données suivantes :

2 - Lancer l’Enregistreur de macros

 Comme vu dans le chapitre L’Enregistreur de macros, lancez l’enregistreur de macros et nommez la macro Macro1.

3 - Ajouter les formules

 Dans la cellule D1, ajoutez la formule suivante : =RECHERCHEV(1;A:C;3;FAUX)

 Dans la cellule E1, ajoutez la formule suivante : =RECHERCHEV("Martin Dupont";B:C;2;FAUX)

4 - Arrêter l’Enregistreur de macros

 Comme vu dans le chapitre L’Enregistreur de macros, arrêtez l’Enregistreur de macros.

5 - Analyser le code généré automatiquement

 Accédez à l’interface VBE ; voici le code que vous devriez voir dans le module nouvellement créé :

Sub Macro1() 
   Range("D1").Select 
   ActiveCell.FormulaR1C1 = "=VLOOKUP(1,C[-3]:C[-1],3,FALSE)" 
   Range("E1").Select 
   ActiveCell.FormulaR1C1 = "=VLOOKUP(""Martin Dupont"",
C[-3]:C[-2],2,FALSE)" 
End Sub 

Exemple 2 : code généré par l’Enregistreur de macros

Analyse du code généré par l’Enregistreur de macros

La première ligne de la macro consiste à sélectionner la cellule D1, vous la connaissez maintenant.

Range("D1").Select 

La seconde ligne utilise la cellule active (D1) pour y affecter une formule en passant par la propriété FormulaR1C1, que vous verrez plus en détail dans le paragraphe suivant.

ActiveCell.FormulaR1C1 = "=VLOOKUP(1,C[-3]:C[-1],3,FALSE)" 

La formule s’applique directement au niveau d’une cellule, de type Range/Cells.

Vous pouvez constater que la formule est passée sous forme de chaînes de caractères, avec le signe = comme premier caractère, comme ce que vous avez saisi manuellement.

Ce que vous pouvez ensuite remarquer est que la formule prise en compte par l’Enregistreur de macros est écrite en anglais (VLOOKUP correspond en effet à RECHERCHEV dans les formules Excel). D’autre part, dans notre exemple, le caractère de séparation des paramètres de la fonction Excel, initialement un point-virgule (;), est transformé en virgule (,).

Ensuite, la notation R1C1 est utilisée ici pour représenter les cellules relativement les unes par rapport aux autres. Le nombre représenté entre crochet permet de déterminer le décalage...

Utiliser ses propres fonctions dans les formules Excel

Une autre possibilité très pratique avec VBA est de pouvoir utiliser vos propres fonctions directement dans les cellules Excel.

Pour y arriver, c’est très simple. Les fonctions que vous souhaitez utiliser dans des formules devront être rédigées dans des modules (et non dans ThisWorkbook, ni dans les feuilles ou encore dans des modules de classe). La portée des fonctions devra également être Public.

Voici un exemple de formule qui retourne la valeur 1.

Public Function Retourne1() As Integer 
   Retourne1 = 1 
End Function 

Exemple 17 : rendre une fonction personnalisée disponible dans les formules

Lorsque vous avez rédigé ces quelques lignes, en allant dans une cellule Excel et en tapant le nom de la fonction, elle apparaîtra dans la liste des fonctions disponibles, en respectant la casse de nommage.

En validant la formule, la valeur de retour (ici 1) s’affichera directement dans la cellule.

Cela fonctionne également si vous avez une fonction qui nécessite des paramètres.

'Fonction retournant le carré de la valeur passée en paramètre 
Public Function RetourneLeCarre(iValeur As Integer) As Integer 
   RetourneLeCarre = iValeur * iValeur 
End Function 

Exemple 18 : fonction personnalisée nécessitant...

Utiliser les formules Excel directement en VBA - WorksheetFunction

Vous pouvez utiliser directement les fonctions en VBA sans passer par des formules Excel, grâce à la propriété WorksheetFunction de l’objet Application.

Application.WorksheetFunction.NomFonction(Arg1, [Arg2],….) 

Exercices

Les exercices auront pour objectif d’utiliser des formules se basant sur ces données.