Découvrez les fondamentaux VBA

Les trois derniers chapitres étaient assez lourds en apprentissage, avec les variables, les opérateurs puis les boucles. Nous allons maintenant nous aventurer sur des sujets plus “simples” en pratique.

Tout au long de ce chapitre, nous allons voir ensemble comment nous pouvons appeler des fonctions ou des procédures pour clarifier notre code.

Appréhendez les procédures

Si nous devions définir ce qu’est une procédure, nous pourrions dire que c’est un morceau de code que nous souhaitons extraire du code initial.

Tout à fait, c’est un sous-programme dans votre code VBA. Vous pouvez faire cela pour augmenter la lisibilité du code ou encore parce que vous utilisez le même code deux fois dans deux macros différentes. Afin de ne pas réécrire le code, nous préférons l’extraire.

Devinez quoi ? Comme d’habitude, il existe plusieurs façons de faire un sous-programme. Je vous ai dit en introduction que c’était un peu plus simple, et c’est vrai. Nous avons deux façons de faire des sous-programmes :

• une subroutine (Sub) (que vous utilisez depuis le début dans votre code) ;

• une fonction (Function). 

Voici un petit tableau qui résume assez simplement ce que peuvent faire une subroutine et une fonction :

Vous voyez rapidement que ce qui distingue une subroutine d’une fonction, ce sont les valeurs qu’elles peuvent retourner.

Mais revenons un instant sur la troisième ligne de ce tableau avec la question “Elles acceptent des arguments ?”.

L’utilisation d’un sous-programme pose un problème : les variables que vous utilisez dans votre programme général ne sont pas disponibles dans ce nouveau sous-programme.

Prenons un exemple :

Je souhaite modifier la date de mon reporting, en transformant le mois 02 en février. Le problème, c’est que je n’ai pas envie de voir ce code avec le gros Select Case directement dans mon code, je souhaite en faire un sous-programme.

Dans mon code, j’ai besoin de lire cette variable du mois qui contient “02 ” et que je dois transformer.

Sub changement_mois()

Dim date_reporting As Date
Dim transformation_date As Integer
Dim mois As String
Dim jour As Integer
Dim annee As Integer

date_reporting = Range("A1").Value
tranformation_date = Month(date_reporting)
jour = Day(date_reporting)
annee = Year(date_reporting)

Select Case tranformation_date
    Case 1
    mois = "janvier"
    Case 2
    mois = "février"
    Case 3
    mois = "mars"
    Case 4
    mois = "avril"
    Case 5
    mois = "mai"
    Case 6
    mois = "juin"
    Case 7
    mois = "juillet"
    Case 8
    mois = "août"
    Case 9
    mois = "septembre"
    Case 10
    mois = "octobre"
    Case 11
    mois = "novembre"
    Case 12
    mois = "décembre"
End Select

Range("D1").Value = "Reporting du " & jour & " " & mois & " " & annee

End Sub

Cette fonction vous permet d’écrire la date dans un titre, “Reporting du mois du 1 février 2023”.

Imaginons que vous ayez besoin de cette fonction dans une autre macro, vous n’aurez pas envie de l’écrire à nouveau. C’est pourquoi vous pouvez en faire un sous-programme que vous aurez seulement besoin d’appeler pour transformer le mois.

Le problème, c’est que la lecture de la date se fait dans le programme principal ; vous avez deux solutions, soit :

• relire dans votre sous-programme la valeur de la cellule qui contient la valeur du mois ;

• passer comme argument le mois que vous avez déjà récupéré à votre sous-programme. 

Pour ce premier test, vous allez relire la variable dans le sous-programme.

Voici ce que cela peut devenir avec une subroutine.

Sub changement_mois_SP()

Dim date_reporting As Date
Dim transformation_date As Integer
Dim mois As String
Dim jour As Integer
Dim annee As Integer

date_reporting = Range("A1").Value
jour = Day(date_reporting)
annee = Year(date_reporting)

transformation_mois

mois = Range("B1").Value

Range("D1").Value = "Reporting du " & jour & " " & mois & " " & annee

End Sub

Sub transformation_mois()

Dim date_reporting As Date
Dim transformation_date As Integer
Dim mois As String

date_reporting = Range("A1").Value

tranformation_date = Month(date_reporting)

Select Case tranformation_date
    Case 1
    mois = "janvier"
    Case 2
    mois = "février"
    Case 3
    mois = "mars"
    Case 4
    mois = "avril"
    Case 5
    mois = "mai"
    Case 6
    mois = "juin"
    Case 7
    mois = "juillet"
    Case 8
    mois = "août"
    Case 9
    mois = "septembre"
    Case 10
    mois = "octobre"
    Case 11
    mois = "novembre"
    Case 12
    mois = "décembre"
End Select

Range("B1").Value = mois

End Sub

Vous avez fractionné le programme initial qui est maintenant bien plus petit et on peut voir qu’il appelle le sous-programme transformation_mois. Le sous-programme : 

• récupère les informations de la date dans le fichier ;

• passe par le  Select Case  ;

• puis stocke la valeur de sa transformation dans la cellule B2. 

Le programme principal n’a plus qu'à récupérer cette valeur en B2 et écrire le titre du reporting.

Nous allons faire une légère digression avant de passer aux arguments.

Vous pouvez limiter la portée des appels pour les différents sous-programme en utilisant la procédure :

• Private : utilisable seulement par les programmes d’un même module :

• Public : utilisable par tous les modules ;

• Static : permet d’utiliser les variables pendant toute la durée du programme (les variables sont globales et non plus locales pour un sous-programme).

Effectivement, de base quand on ne rajoute rien, le sous-programme est par défaut en public.

Si vous souhaitez limiter l’appel de votre programme à votre module, vous écrirez :

Private Sub transformation_mois()

Voyons comment fractionner notre code pour le rendre lisible :

Maîtrisez les arguments de vos fonctions

Comme nous l'avons vu plus haut, vous pouvez fractionner un morceau de programme que vous êtes susceptible de réutiliser, ou tout simplement pour rendre le code plus lisible.

Je ne sais pas si vous avez fait attention, mais dans le sous-programme que nous avons utilisé, nous avons écrit volontairement le résultat de notre Select Case dans la cellule B2. Les sous-programmes ne permettent pas de renvoyer une valeur à un autre sous-programme, c’est pour cela que nous avons écrit dans une cellule sur Excel pour stocker la valeur.

Pour pallier ce problème, nous pouvons utiliser des fonctions. L’utilisation des fonctions est très similaire à la subroutine, à la différence qu’elle peut renvoyer à un autre programme avec la transformation des données.

Regardons ensemble ce que cela va changer sur notre programme initial avec la subroutine.

Sub changement_mois_Fonc()

Dim date_reporting As Date
Dim mois As String
Dim jour As Integer
Dim annee As Integer

date_reporting = Range("A1").Value
jour = Day(date_reporting)
annee = Year(date_reporting)

mois = transformation_mois_fonc(date_reporting)

Range("D1").Value = "Reporting du " & jour & " " & mois & " " & annee

End Sub

Private Function transformation_mois_fonc(ByVal tranformation_date As Date) As String

transformation_mois_fonc = Month(tranformation_date)

Select Case transformation_mois_fonc
    Case 1
    transformation_mois_fonc = "janvier"
    Case 2
    transformation_mois_fonc = "février"
    Case 3
    transformation_mois_fonc = "mars"
    Case 4
    transformation_mois_fonc = "avril"
    Case 5
    transformation_mois_fonc = "mai"
    Case 6
    transformation_mois_fonc = "juin"
    Case 7
    transformation_mois_fonc = "juillet"
    Case 8
    transformation_mois_fonc = "août"
    Case 9
    transformation_mois_fonc = "septembre"
    Case 10
    transformation_mois_fonc = "octobre"
    Case 11
    transformation_mois_fonc = "novembre"
    Case 12
    transformation_mois_fonc = "décembre"
End Select

End Function

Vous voyez dans le code ci-dessus que la fonction est déclarée comme unePrivate Function. Et cette fois, dans les parenthèses de la fonction nous avons ajouté une variable que nous avons déclarée. Elle s’appelle transformation_date, et c’est une date. Cette déclaration vous permet de dire à la fonction que quand vous allez l’appeler dans le programme principal, vous lui donnez une valeur qui sera une date et qui sera automatiquement transférée dans cette nouvelle variable transformation_date.

Dans la déclaration de la fonction, j’ai également ajouté As String à la fin de la déclaration.

Eh bien non, ce n’est pas une erreur. En faisant cela, j’utilise le nom de ma fonction comme une variable et je vais justement retourner cette variable transformation_mois__fonc dans mon programme principal. Vous pouvez également voir que j’ai remplacé la variable qui contient le résultat de mon  Select Case  par cette nouvelle variable.

Retournons maintenant du côté du sous-programme. J’ai seulement changé l’appel de ma fonction :

mois = transformation_mois_fonc(date_reporting)

Je passe dans ma fonction la variable  date_reporting  pour que ma fonction puisse la récupérer. Vous voyez que je stocke la valeur de ma fonction dans la variable mois. En effet, la fonction va nous renvoyer une valeur au travers d’une string (que nous avons déclarée dans la fonction).

Je n’ai volontairement pas encore parlé du  ByVal  que j’ai ajouté dans ma fonction, c’est vrai. Je garde le meilleur pour la fin. 😉

Vous pouvez ajouter soit unByValsoit unByRef, sachant que si vous ne mettez rien, c’est que vous utilisez un  ByRef  . En ajoutant un  ByVal  , je protège ma variable initiale qui ne va pas changer. En effet, la valeur qui est renvoyée par ma fonction est une nouvelle variable. 

En utilisant un  ByRef  , je modifie la variable initiale en la remplaçant par ma transformation.

À vous de jouer !

Votre manager a entendu parler par des collègues que vous aviez continué à vous former en VBA. Il vous demande alors une mise à jour de son code. Il souhaite maintenant que le code s'exécute à l’ouverture du fichier directement, mais il souhaite aussi rajouter une feuille (qui va s’appeler calcul), et ajouter des éléments à l’intérieur : 

• calcul de la quantité moyenne de vente ;

• calcul de la plus petite vente ;

• calcul de la plus grande vente ;

• nombre de cellules de vente à 0 ;

• somme des ventes.

Il faut gérer l’erreur si une feuille avec le même nom existe déjà.

Vous pouvez également en profiter pour créer des sous-programmes pour les différentes boucles afin d’aérer votre code.

Vous avez terminé ? Voici le corrigé en screencast :

En résumé

• Deux types de sous-programmes sont utilisés en VBA : les subroutines et les fonctions.

• Les subroutines sont des sous-programmes simples qui acceptent des paramètres. 

• Si on souhaite utiliser des variables d’un autre programme et en plus renvoyer des variables, il faut utiliser les fonctions. 

Les sous-programmes nous permettent de pouvoir appeler du code à partir d’un autre programme, ou de déclencher un programme en fonction d’une action. Dans le prochain chapitre, nous allons nous consacrer aux interactions entre Excel et les utilisateurs, au travers des boîtes de dialogue et des formulaires.