Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici

Manipuler les formules

Objectifs

Dans ce chapitre, vous allez découvrir les différentes façons d’écrire des formules dans les cellules.

Un rappel sur l’onglet Formules dans Excel sera effectué avant de passer aux formules en VBA puis de poursuivre avec la classe WorksheetFunction.

Vous terminerez ce chapitre avec des exercices pour valider vos nouveaux acquis.

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é.

images/17SOB01N.png

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.).

Erreurs en sortie de formules

Lorsque vous effectuez des formules qui retournent des erreurs, par exemple avec une recherche ne trouvant aucun résultat (avec RECHERCHEV), les cellules affichent des erreurs comme...

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 :

images/17SOB02N.png

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....

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.

images/17SOB10N.png

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.

La propriété WorksheetFunction

La propriété WorksheetFunction regroupe l’ensemble des fonctions Excel disponibles dans l’interface Excel, directement au format VBA.

Tout comme il existe des centaines de fonctions Excel pour les formules, il existe des centaines de fonctions membres de la propriété WorkSheetFunction.

En voici quelques-unes pour vous permettre d’en comprendre le principe.

Membres de la propriété WorksheetFunction

Les points communs des fonctions dans Excel et dans VBA sont leur nom en anglais et l’ordre des paramètres que les fonctions attendent en entrée. Il vous est également possible de passer soit des cellules, soit des valeurs fixes.

La syntaxe générale de cette propriété est la suivante :

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

Exemple 19 : syntaxe générale de la propriété WorksheetFunction

Le nom de la fonction NomFonction dépend de la fonction Excel qui doit être utilisée, le nombre de paramètres est variable selon la fonction utilisée.

Voici quelques exemples de fonctions...

Les options de calcul et le calcul sur une feuille ou un classeur

Les options de calculs en VBA : Calculation

Vous pouvez vous reporter à la section dédiée du chapitre Manipuler l’application Excel pour les propriétés Calculation.

Calculer sur une feuille, calculer tout le classeur : Calculate

Vous pouvez déclencher un calcul sur une feuille ou un classeur au moyen de la méthode Calculate.

La syntaxe sera la suivante :

Worksheets("Feuille").Calculate 

Exemple 23 : utilisation de la méthode Calculate

Exercices

 Avant de réaliser les exercices suivants, créez une feuille Exercices_ManipulationFormules.

 Dans cette feuille, remplissez la plage de cellules A1 à B6 comme sur l’image suivante :

images/17SOB13N.png

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

Écriture de formules en français

Nombre total d’éléments

 Créez une macro FormuleSommeVolumes.

 Cette macro écrira dans la cellule B7 une formule SOMME des volumes. Vous devrez utiliser la formule en français (FormulaLocal).

 Exécutez la macro FormuleSommeVolumes. Le total affiché dans la cellule B7 devra être de 178.

Quantité maximale

 Créez une macro FormuleMaximumVolumes.

 Cette macro écrira dans la cellule B8 une formule MAX des volumes. Vous utiliserez de nouveau la formule en français (FormulaLocal).

 Exécutez la macro FormuleMaximumVolumes. Le total affiché dans la cellule B8 devra être de 75.

Écriture de formules en anglais

Volume total de commandes

 Créez une macro FormuleTotalYODA.

 Cette macro écrira dans la cellule B9 une formule indiquant le nombre total de cartons (SOMME.SI en français, SUMIF en anglais). Vous utiliserez cette fois-ci une formule en anglais (Formula).

 Exécutez la macro FormuleNombreDeCartons. Le total affiché...