All posts in VBAExcel

Fonctions de base dédiées aux dates

Il existe un grand nombre de fonctions dédiées à la manipulation des dates en VBA. Tant mieux, car sans elles, vous devriez écrire beaucoup plus de lignes de code lorsque vous manipulez des dates et heures…

Voici quelques exemples d’utilisation de ces fonctions :

Debug.Print Date

Debug.Print Time

Debug.Print Now

Debug.Print Day(Date)

Debug.Print Month(Date)

Debug.Print MonthName(Month(Date))

Debug.Print Year(Date)

Debug.Print Weekday(Date)

Debug.Print WeekdayName(Weekday(Date) - 1)

Debug.Print Hour(Now)

Debug.Print Minute(Now)

Debug.Print Second(Now)

Debug.Print Timer

Et voici les résultats affichés dans la fenêtre Exécution :

30/10/2017

17:19:41

30/10/2017 17:19:41

 30

 10

octobre

 2017

 2

lundi

 17

 19

 41

 62381,12

Les fonctions IsDate() et CDate()

La fonction IsDate(d) retourne la valeur :

• Vrai si l’argument est une date ;
• Faux si l’argument n’est pas une date.

La fonction CDate() transforme l’argument chaîne qui lui est passé en un date

Exemples :

Debug.Print "IsDate(""12/8/1960"") : " & IsDate("12/08/1960")

Debug.Print "IsDate(""122/08/1960"") : " & IsDate("122/08/1960")

Dim d As Variant

Debug.Print "d est défini en tant que variant"

Debug.Print "IsDate(d) : " & IsDate(d)

d = CDate("22/12/2018")

Debug.Print "Après cette instruction :"

Debug.Print "  d = CDate(""22/12/2018"")"

Debug.Print "IsDate(d) vaut " & IsDate(d)

Voici le résultat de ce code :

IsDate("12/8/1960") : Vrai

IsDate("122/08/1960") : Faux

d est défini en tant que variant

IsDate(d) : Faux

Après cette instruction :

d = CDate("22/12/2018")

IsDate(d) vaut Vrai

Les fonctions de date qui travaillent sur des intervalles

Plusieurs fonctions vous permettent de travailler simplement sur des intervalles. Par exemple pour ajouter 25 jours à une date ou encore pour soustraire 3 mois à une date. Les intervalles sont codés sous la forme de String. Les différentes valeurs possibles sont résumées dans ce tableau :

La fonction DateAdd()

La fonction DateAdd() retourne un Variant (Date) qui contenant une date à laquelle un intervalle de temps spécifié a été ajouté.

DateAdd(intervalle, nombre, date)

Où :

• intervalle est un des codes du tableau précédent ;
• nombre est le nombre d’intervalles à ajouter à la date de référence, spécifiée dans le troisième argument.

Exemples :

Debug.Print "Date système : " & Date

Debug.Print "Dans 3 mois : " & DateAdd("m", 3, Date)

Debug.Print "Il y a 3 mois : " & DateAdd("m", -3, Date)

Debug.Print "Dans 5 jours : " & DateAdd("y", 5, Date)

Debug.Print "Il y a 1 trimestre : " & DateAdd("q", -1, Date)

Debug.Print "Heure système : " & Time

Debug.Print "Il y a 2h20 : " & DateAdd("n", -140, Time)

Debug.Print "Dans 2 ans : " & DateAdd("yyyy", 2, Now)

Voici le résultat :

Date système : 30/10/2017

Dans 3 mois : 30/01/2018

Il y a 3 mois : 30/07/2017

Dans 5 jours : 04/11/2017

Il y a 1 trimestre : 30/07/2017

Heure système : 17:51:39

Il y a 2h20 : 15:31:39

Dans 2 ans : 30/10/2019 17:51:39

La fonction DateDiff()

La fonction DateDiff() retourne un Variant (Long) qui indique le nombre d’intervalles de temps entre deux dates données. Voici sa syntaxe :

DateDiff(intervalle, date1, date2)

Où intervalle est l’intervalle de temps (voir le tableau au début de la section « Les fonctions de date qui travaillent sur des intervalles ») qui sépare les dates date1 et date2.

Exemples :

Dim d1, d2 As Variant

d1 = "20/12/2016"

d2 = "15/06/2020"

Debug.Print "Les deux dates à comparer : " & d1 & " et " & d2

Debug.Print "Nb de mois entre ces deux dates : " & DateDiff("m", d1, d2)

Debug.Print "Nb de semaines entre ces deux dates : " & DateDiff("ww", d1, d2)

Debug.Print "Nb de jours entre ces deux dates : " & DateDiff("w", d1, d2)

Dim h1, h2 As Variant

h1 = "5:10:12"

h2 = "17:30:45"

Debug.Print "Les deux heures à comparer h1 et h2 : " & h1 & " et " & h2

Debug.Print "Nb d'heures entre h1 et h2 : " & DateDiff("h", h1, h2)

Debug.Print "Nb de minutes entre h1 et h2 : " & DateDiff("n", h1, h2)

Debug.Print "Nb de secondes entre h1 et h2 : " & DateDiff("s", h1, h2)

Voici le résultat de ce code :

Les deux dates à comparer : 20/12/2016 et 15/06/2020

Nb de mois entre ces deux dates : 42

Nb de semaines entre ces deux dates : 182

Nb de jours entre ces deux dates : 181

Les deux heures à comparer h1 et h2 : 5:10:12 et 17:30:45

Nb d'heures entre h1 et h2 : 12

Nb de minutes entre h1 et h2 : 740

Nb de secondes entre h1 et h2 : 44433

La fonction DatePart()

La fonction DatePart() retourne un Variant (Integer) qui représente l’année, le trimestre, le mois, la semaine ou le jour d’une date. Voici sa syntaxe :

DatePart(partie, date)

Où partie représente l’élément à extraire de la date spécifiée en deuxième argument.

Exemples :

Debug.Print "Date système : " & Date

Debug.Print "Semaine de la date système : " & DatePart("ww", Date)

Debug.Print "Trimestre de la date système : " & DatePart("q", Date)

Debug.Print "Jour de la semaine de la date système : " & DatePart("w", Date) – 1

Voici le résultat de ce code :

Date système : 30/10/2017

Semaine de la date système : 44

Trimestre de la date système : 4

Jour de la semaine de la date système : 1

Les fonctions DateSerial() et TimeSerial()

La fonction DateSerial() retourne un Variant (Date) qui correspond à l’année, le mois et le jour passés en argument. Voici sa syntaxe :

DateSerial(annee, mois, jour)

Où annee, mois et jour sont (respectivement) l’année, le mois et le jour de la date à reconstituer.

La fonction DateSerial() retourne un Variant (Date) qui correspond à l’année, le mois et le jour passés en argument. Voici sa syntaxe :

DateSerial(annee, mois, jour)

Où annee, mois et jour sont (respectivement) l’année, le mois et le jour de la date à reconstituer.

La fonction TimeSerial() retourne un Variant (Date) qui correspond aux heures, minutes et secondes passées en argument. Voici sa syntaxe :

TimeSerial(heures, minutes, secondes)

Où heures, minutes et secondes sont (respectivement) les heures, les minutes et les secondes de l’heure à reconstituer.

Exemples :

Debug.Print DateSerial(2018, 10, 25)

Debug.Print TimeSerial(22, 15, 12)

Voici le résultat de ce code :

25/10/2018

22:15:12

DateValue          Renvoie une valeur de type Variant (Date).

TimeValue          Renvoie une valeur de type Variant (Date) contenant une heure.

Mise en forme

La fonction Format() permet de mettre en forme des dates. Elle retourne une valeur de type String. Voici sa syntaxe :

Format(expression, format)

Où expression est l’expression à mettre en forme et format est le format de mise en forme à appliquer à l’expression.

Voici quelques exemples :

Dim d As Date

d = Date

MsgBox Format(d, "dddd dd/mm/yyyy")

MsgBox Format(d, "dd mmmm yyyy")

Voici le résultat de l’exécution :

 <  > <  

Pour aller plus loin avec la fonction Format(), je vous suggère de consulter la page dédiée à cette fonction sur MSDN.

Cet article regroupe les opérateurs et les fonctions dédiés à la manipulation des nombres en VBA Excel.

Opérateurs mathématiques

Les opérateurs mathématiques sont très classiques. Notez cependant l’opérateur de division entière (\) et le module (Mod) qui ne se retrouvent pas dans tous les langages :

Exemples :

Dim n1, n2 As Single

n1 = 13.34

n2 = 3.46

Debug.Print "n1 = " & n1

Debug.Print "n2 = " & n2

Debug.Print "n1^3 = " & n1 ^ 3

Debug.Print "n1/n2 = " & n1 / n2

Debug.Print "n1\n2 = " & n1 \ n2

Debug.Print "n1 Mod 2 = " & n1 Mod n2

Voici le résultat de ce code :

n1 = 13,34

n2 = 3,46

n1^3 = 2373,927704

n1/n2 = 3,85549128697245

n1\n2 = 4

n1 Mod 2 = 1

Opérateurs de comparaison

Les opérateurs de comparaison sont très classiques :

Opérateurs logiques

Les opérateurs logiques sont également très classiques :

Fonctions mathématiques

Le langage VBA contient quelques fonctions mathématiques dédiées à la manipulation des nombres. Elles sont résumées dans le tableau suivant :

Fonctions de conversion

Et pour terminer, voici quelques fonctions de conversion :

Mise en forme

La fonction Format() permet de mettre en forme des nombres. Elle retourne une valeur de type String. Voici sa syntaxe :

Format(expression, format)

Où expression est l’expression à mettre en forme et format est le format de mise en forme à appliquer à l’expression.

Voici quelques exemples :

Dim s As Single

s = 1234.456

MsgBox Format(s, "0 000.00 €")

s = 1234567

MsgBox Format(s, "0.00")

MsgBox Format(s, "#,##0")

Voici le résultat de l’exécution :

Pour aller plus loin avec la fonction Format(), je vous suggère de consulter la page dédiée à cette fonction sur MSDN.

      

Comme vous allez le voir dans cet article, VBA possède de nombreuses fonctions pour manipuler du texte. Au fil de l’article, vous découvrirez les fonctions Len(), Left(), Right(), Mid(), LTrim(), RTrim(), Trim(), UCase(), LCase(), Proper(), Replace(), Val(), IsNumeric(), IsDate() et beaucoup d’autres.

Longueur d’une chaîne

La fonction Len() retourne la longueur de la chaîne passée en argument.

Exemple :

Dim ch As String

ch = "Ceci est une chaîne"

MsgBox "Longueur de la chaîne : " & Len(ch)

Extraction de sous-chaînes

Pour extraire une sous-chaîne d’une chaîne, vous pouvez utiliser les fonctions Left(), Right() et Mid() :

• La fonction Left(ch, nombre) renvoie les nombre caractères au début de la chaîne ch.
• La fonction Right(ch, nombre) renvoie les nombre caractères à la fin de la chaîne ch.
• Enfin, la fonction Mid(ch, deb, long) renvoie les long caractères de la chaîne ch, en partant du caractère de position deb.

Exemples :

Dim s As String

s = "une simple chaîne"

MsgBox Left(s, 5)

MsgBox Right(s, 6)

MsgBox Mid(s, 5, 5)

Voici les boîtes de dialogue affichées par ce code :

  

Suppression d’espaces au debut et/ou à la fin

Les fonctions LTrim(), RTrim() et Trim() permettent de supprimer les espaces (respectivement) au début, à la fin et des deux côtés d’une chaîne.

Exemples :

Dim s As String

s = "    >une chaîne<        "

MsgBox "LTrim(s) : " & LTrim(s) & " longueur " & Len(LTrim(s)) & vbLf & _

       "RTrim(s) : " & RTrim(s) & " longueur " & Len(RTrim(s)) & vbLf & _

       "Trim(s) : " & Trim(s) & " longueur " & Len(Trim(s))

Voici le résultat de l’exécution de ce code :

Casse des caractères

Les fonctions Ucase(), Lcase() et Application.Proper() permettent de modifier la casse des caractères.

Voici un exemple d’utilisation :

Dim s As String

s = "un peu de texte dans une variable String"

MsgBox "Ucase(s) = " & UCase(s) & vbLf & _

       "Lcase(s) = " & LCase(s) & vbLf & _

       "Application.proper(s) = " & Application.Proper(s)

Voici le résultat de ce code :

Valeur numérique d’une chaîne

La fonction Val() convertit son argument chaîne en un nombre lorsque cela est possible. Lorsque cela n’est pas possible, elle retourne la valeur 0.

Voici un exemple de code :

Dim s As String

Dim r As Single

s = "12.25"

r = Val(s)

MsgBox r

s = "bonjour"

r = Val(s)

MsgBox r

Et le résultat de son exécution :

 

Remplacement d’une sous-chaîne par une autre

La fonction Replace() permet de remplacer une sous-chaîne par une autre dans une chaîne.

Voici un exemple de code :

Dim s As String

s = "Illustration de la fonction Replace()"

MsgBox Replace(s, "e", "E")

Ici, tous les « e » sont remplacés par des « E« . Voici le résultat :

Deux autres paramètres peuvent être passés à la fonction Replace() : la position du premier remplacement et le nombre de remplacements. Voici un exemple pour mieux comprendre comment utiliser ces paramètres :

Dim s As String

s = "la la la la lère"

MsgBox Replace(s, "la", "LA", 2, 2)

Ici, deux remplacements seront effectués (le dernier paramètre) et la chaîne retournée commencera au deuxième caractère de la chaîne originale. Voici le résultat :

Conversions et mises en forme

Plusieurs fonctions permettent de convertir une donnée d’un certain type dans un autre type :

Par exemple, pour convertir un Single en Integer, vous pourriez utiliser ces instructions :

Dim pi As Single

pi = 3.14158265

MsgBox CInt(pi)

Voici le résultat :

Mise en forme

La fonction Format() permet de mettre en forme des nombres, des dates et des chaînes. Elle retourne une valeur de type String. Voici sa syntaxe :

Format(expression, format)

Où expression est l’expression à mettre en forme et format est le format de mise en forme à appliquer à l’expression.

Voici quelques exemples :

Dim s As Single

s = 1234.456

MsgBox Format(s, "0 000.00 €")

Dim d As Date

d = Date

MsgBox Format(d, "dddd dd/mm/yyyy")

MsgBox Format(d, "dd mmmm yyyy")

Et voici le résultat de l’exécution :

  

Pour aller plus loin avec la fonction Format(), je vous suggère de consulter la page dédiée à cette fonction sur MSDN.

Lorsqu’on commence à écrire du code VBA, il est fréquent de vouloir afficher des données textuelles ou numériques dans une boîte de message. La méthode MsgBox() est là pour ça. Comme vous le verrez dans cet article, vous pouvez également utiliser MsgBox() en tant que fonction pour poser une question à l’utilisateur. En fonction de son choix, vous pourrez alors exécuter un bloc d’instructions ou un autre.

La méthode MsgBox()

Pour afficher une boîte de message, vous utiliserez MsgBox() en tant que méthode. Voici quelques exemples de code :

Affichage d’un message texte

MsgBox "Un message texte"

Affichage du contenu d’une cellule avec la fonction Cells()

MsgBox Cells(1, 2)

Affichage du contenu d’une cellule avec la fonction Range()

MsgBox Range("B2")

Affichage d’un texte et de la valeur d’une variable

Remarquez qu’ici, la variable est numérique, et que VBA accepte qu’on la concatène à une chaîne avec l’opérateur de concaténation « & » :

Dim n as Integer

n = 12

MsgBox "n vaut " & n

Affichage d’un texte sur plusieurs lignes

Le passage à la ligne se fait grâce au caractère Chr(10) ou à la constante vbLf. Remarquez également le caractère de soulignement (_) en fin de ligne qui permet de répartir l’instruction sur plusieurs lignes pour améliorer sa lisibilité :

MsgBox "Un message sur plusieurs lignes." & Chr(10) & _

       "Le passage à la ligne se fait avec un Chr(10)" & vbLf & _

       "ou avec la constante vbLf"

Définition du titre de la MsgBox()

Pour modifier le texte qui apparait dans la barre de titre d’une MsgBox(), vous devez passer trois paramètres à la fonction. Voici un exemple :

MsgBox "Texte dans la MsgBox()", , "Titre de la MsgBox()"

Ici, le deuxième paramètre est vide, ce qui provoque l’affichage d’une boîte de dialogue standard. Vous pourriez également utiliser les constantes suivantes :

Constante	Effet
vbCritical
vbQuestion
vbExclamation
vbInformation

Voici un exemple :

MsgBox "Texte dans la MsgBox()", vbExclamation, "Titre de la MsgBox()"

La fonction MsgBox()

MsgBox() peut également être utilisé en tant que fonction. Dans ce cas, le deuxième argument indique le nombre et la nature des boutons affichés dans la boîte de dialogue :

Constante	Effet
vbOKOnly
vbOKCancel
vbAbortRetryIgnore
vbYesNoCancel
vbYesNo
vbRetryCancel

Lorsqu’une boîte MsgBox() comporte plusieurs boutons, vous pouvez choisir celui qui est actif par défaut. L’utilisateur pourra appuyer sur la touche Entrée du clavier pour simuler un clic sur ce bouton. Pour cela, vous utiliserez les constantes du tableau suivant :

Vous pouvez également utiliser les constantes vbCritical, vbQuestion, vbExclamation et vbInformation pour ajouter une icône dans la boîte de dialogue.

Mais alors, si vous choisissez (par exemple) les boutons de la boîte de dialogue, comment indiquer en plus quel bouton sera utilisé par défaut et quelle icône vous voulez utiliser ? Eh bien tout simplement en additionnant les constantes correspondantes.

Par exemple, pour afficher une boîte de dialogue qui :

• affiche le texte « Voulez-vous continuer » ;
• contient les boutons Oui (sélectionné par défaut) et Non ;
• affiche une icône vbQuestion.

Vous utiliserez ces instructions :

MsgBox("Voulez-vous continuer ?", vbYesNo + vbDefaultButton1 + vbQuestion, "Important")

Mais attention : ici, MsgBox() est utilisé en tant que fonction et non en tant que méthode. Elle retourne donc une valeur qui doit être affectée à une variable ou directement testée. Les valeurs retournées par la fonction MsgBox() peuvent être les suivantes :

Voici un exemple de code :

If MsgBox("Voulez-vous continuer ?", vbYesNo + vbDefaultButton1 + vbQuestion, "Important") = vbYes Then

    MsgBox ("OK, on continue")

Else

    MsgBox ("C'est d'accord, on s'arrête ")

End If

Voici le résultat :

Le bouton Oui est sélectionné par défaut. Si l’utilisateur appuie sur la touche Entrée du clavier ou clique sur le bouton Oui, une boîte de dialogue s’affiche :

S’il clique sur Non, une autre boîte de dialogue s’affiche :

La fonction InputBox()

Cet article ne serait pas complet si la fonction InputBox() n’était pas citée. Cette fonction demande à l’utilisateur de saisir une information textuelle ou numérique. Voici sa syntaxe :

InputBox ( message [, titre ] [, défaut ] [, xpos ] [, ypos ])

Où :

• message représente le message à afficher dans la boîte de dialogue ;
• titre représente le texte affiché dans la barre de titre (ce paramètre est optionnel) ;
• défaut représente la valeur par défaut (ce paramètre est optionnel) ;
• xpos et ypos représentent les coordonnées x et y de l’angle supérieur gauche de l’InputBox par rapport à l’angle supérieur gauche de l’écran (ces paramètres sont optionnels).

Voici un exemple de code :

Dim prenom As String

While Len(prenom) = 0

  prenom = InputBox("Quel est votre prénom", "J'ai besoin de savoir)

Wend

MsgBox "Bonjour " & prenom

Ici, une boucle While Wend répète autant de fois que nécessaire la saisie du prénom jusqu’à ce que l’utilisateur entre son prénom (c’est-à-dire une chaîne non vide).

Voici un exemple d’exécution :

Remarque

Si vous affectez le résultat de la fonction InputBox() à une variable numérique et que l’utilisateur saisit du texte, une erreur est générée et le programme prend fin, à moins que vous ne mettiez en place un gestionnaire d’erreurs, comme indiqué dans l’article Gestion d’erreurs en VBA.

Des erreurs peuvent parfois apparaître lors de l’exécution d’un code VBA. Si vous ne mettez pas en place un gestionnaire d’erreur, une boîte de dialogue peu esthétique stoppera net le fonctionnement du programme.

Mise en place d’un gestionnaire d’erreurs

Pour définir un gestionnaire d’erreurs dans une procédure, vous utiliserez une instruction On Error GoTo. Voici un exemple de code :

Sub uneProcedure()

  On Error GoTo gestionErreurs

  Dim a As Integer

  a = 1 / 0

gestionErreurs:

  MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description

End Sub

Et voici le résultat :

Ici, on tente d’affecter la valeur 1/0 à la variable Integer a. Ce qui produit une erreur 11 : Division par zéro.

Annulation du gestionnaire d’erreurs

A tout moment, vous pouvez désactiver le gestionnaire d’erreur mis en place par une instruction On Error GoTo. Pour cela, il suffit d’utiliser cette instruction :

On Error GoTo 0

Examinez ce code :

Sub uneProcedure()

  On Error GoTo gestionErreurs

  Dim a As Integer

  a = 1 / 0

gestionErreurs:

  MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description

  On Error GoTo 0

  a = 2 / 0

End Sub

La première division par zéro provoque l’exécution du gestionnaire d’erreurs. Ce dernier :

• affiche un message d’erreur ;
• désactive le gestionnaire d’erreur ;
• lance intentionnellement une instruction qui provoque une erreur.

Voici le résultat :

Le deuxième message d’erreur est beaucoup moins engageant que le premier !

Reprise de l’exécution

Placée dans le gestionnaire d’erreurs, l’instruction Resume reprend l’exécution sur l’instruction qui a provoqué une erreur.

Examinez ce code :

Sub uneProcedure()

  On Error GoTo gestionErreurs

  Dim a As Integer

  a = InputBox("Entrez un entier")

  End

gestionErreurs:

  MsgBox "Vous devez saisir un entier"

  Resume

End Sub

Ici, la saisie de l’utilisateur est affectée à une variable de type Integer. Si l’utilisateur n’entre pas un nombre entier, une erreur se produira, et le gestionnaire d’erreurs sera exécuté.

Dans ce cas, un message d’erreur demandant à l’utilisateur de saisir un entier sera affiché et l’instruction Resume provoquera la réexécution de la ligne :

a = InputBox("Entrez un entier")

Si l’utilisateur entre un nombre entier, l’instruction qui suit le InputBox() sera exécutée et la procédure se terminera.

Poursuite de l’exécution

Plutôt que d’indiquer un gestionnaire d’erreurs dans l’instruction On Error GoTo, vous pouvez demander d’ignorer l’erreur et d’exécuter la ligne suivante :

On Error Resume Next

Par exemple, pour tester si la feuille « F3 » existe dans le classeur courant, vous pourriez utiliser ce code :

Sub uneProcedure()

  Dim feuille As Worksheet

  On Error Resume Next

  Set feuille = Worksheets("F3")

  If feuille Is Nothing Then

    MsgBox "La feuille ""F3"" n'existe pas"

  Else

    MsgBox "La feuille ""F3"" existe"

  End If

End Sub

L’instruction

On Error Resume Next

fait en sorte que le code ne s’arrête pas sur une erreur d’exécution. L’erreur est purement et simplement ignorée et l’exécution se poursuit sur la ligne suivante.

L’instruction

Set feuille = Worksheets("F3")

tente d’affecter à la variable feuille la feuille F3. Si cette feuille n’existe pas, l’erreur est ignorée (grâce à l’instruction On Error Resume Next) et l’instructions suivante s’exécute.

Ici, on teste si la variable feuille fait ou ne fait pas référence à une feuille :

If feuille Is Nothing Then

Else

End If

Selon le résultat du test, un message ou un autre est affiché avec une instruction MsgBox().

En programmation, il est souvent nécessaire d’exécuter une portion de code plusieurs fois de suite. La répétition des instructions peut se produire un certain nombre de fois connu à l’avance ou tant qu’une condition est vérifiée. Cet article passe en revue les principales instructions de bouclage du langage VBA.

La boucle For … Next

Lorsque le nombre de répétitions est connu à l’avance, le plus simple est d’utiliser une boucle For … Next. Voici sa syntaxe :

For compteur = début To Fin Step pas

  ' Une ou plusieurs instructions

Next compteur

Où Compteur est une variable qui commence à début et finit à fin en progressant de pas à chaque itération de la boucle.

Remarque

Si la partie Step pas n’est pas précisée, la variable compteur va de début à fin par pas de 1.

Quelques exemples pour mieux comprendre.

Supposons que vous vouliez remplir les cellules A1 à H1 avec les valeurs 1 à 8. Voici le code à utiliser :

Dim i As Integer

For i = 1 to 8

  Cells(1, i) = i

Next i

Ici, nous utilisons la méthode Cells() en précisant le numéro de ligne et le numéro de colonne. Voici le résultat :

Supposons maintenant que vous vouliez remplir les cellules C3 à C12 avec les valeurs « a » à « j« . Voici le code à utiliser :

Dim i As Integer

For i = 3 To 12

  Cells(i, 3) = Chr(97 + i - 3)

Next i

Ici, nous utilisons la méthode Cells() en précisant le numéro de ligne (i, qui va de 3 à 12) et le numéro de colonne (fixe et égal à 3). La méthode Chr() convertit le code ASCII passé en argument en un caractère. Pour arriver au résultat souhaité, le code ASCII de la lettre « a » a été cherché dans un tableau de codes ASCII. Par exemple sur la page http://www.table-ascii.com/ :

Etant donné que i varie de 3 à 12, la formule à utiliser est 97 + i – 3. L’argument de la fonction Chr() va donc de 97 (pour i = 3) à 106 (pour i = 12).

Voici le résultat :

Supposons maintenant que vous vouliez remplir le bloc de cellules A3-C9 comme ceci :

Comment feriez-vous ?

Observez la progression numérique dans les cellules. Les valeurs vont de 1 à 21, et on change de ligne toutes les trois cellules.

Pour faire simple, nous allons imbriquer deux boucles : une pour les lignes et une pour les colonnes. Voici le code :

Dim i, j As Integer

For i = 0 To 6

  For j = 1 To 3

    Cells(i + 3, j) = j + i * 3

  Next j

Next i

La boucle la plus extérieure s’intéresse aux lignes et la boucle la plus intérieure aux colonnes. Les index des boucles ont été choisis pour simplifier au maximum la formule.

Lors de la première exécution de la boucle extérieure, i vaut 0 et j va de 1 à 3. La formule :

Cells(i + 3, j) = j + i * 3

Cible donc les cellules A3, B3 puis C3. Et la valeur affectée à ces cellules est 1, 2 et 3. Si vous ne voyez pas ce que je veux dire, simulez le fonctionnement de la boucle interne en remplaçant j par 1, 2 puis 3 et voyez le résultat. Vous obtenez :

• Pour i=0 et j=1 : Cells(3,1) = 1, soit A3 = 1
• Pour i=0 et j=2 : Cells(3,2) = 2, soit B3 = 2
• Pour i=0 et j=3 : Cells(3,3) = 3, soit C3 = 3

Lorsque la boucle interne a fini de s’exécuter, la boucle externe incrémente la valeur de i, qui passe de 0 à 1. La formule :

Cells(i + 3, j) = j + i * 3

Cible alors les cellules A4, B4 et C4 et leur affecte (respectivement) les valeurs 4, 5 et 6.

La progression de i et de j se poursuit jusqu’à ce que ces index dépassent les valeurs maximales fixées à 6 et 3. Les deux boucles prennent alors fin et la plage A3-C9 est entièrement remplie.

Un dernier exemple pour illustrer l’utilisation d’un pas de progression. Supposons que vous vouliez obtenir le résultat suivant :

Plusieurs approches sont possibles, mais une des plus simples consiste certainement à utiliser un pas de progression négatif dans la boucle :

Dim i As Integer

For i = 12 To 2 Step -2

  Cells(1, i / 2) = i

Next i

Ici, la variable i passe de 12 à 2 par pas de -2. Elle vaut donc successivement 12, 10, 8, 6, 4, puis 2.

La formule :

Cells(1, i / 2) = i

Cible les cellules situées en ligne 1 et en colonne 6 (12/2), 5 (10/2), 4 (8/2), 3 (6/2), 2 (4/2) puis 1 (2/2). La valeur de i est affectée à ces cellules, ce qui donne bien le résultat attendu.

Dans certaines boucles, on ne sait pas à l’avance combien de fois les instructions seront exécutées. Dans ce cas, la boucle For … Next n’est pas appropriée. Vous utiliserez plutôt une boucle While … Wend, Do While … Loop, Do … Loop While ou Do Until … Loop.

La boucle While … Wend

La boucle While … Wend est exécutée tant qu’une condition est vérifiée. Voici sa syntaxe :

While condition

  ' Une ou plusieurs instructions

Wend

Par exemple, pour remplir les cellules A1 à H1 avec les chiffres 1 à 8 avec une boucle While … Wend, comme dans la copie d’écran suivante :

Vous pourriez utiliser ces instructions :

Dim i As Integer

i = 1

While i < 9

  Cells(1, i) = i

  i = i + 1

Wend

Ces instructions peuvent sembler plus complexes que celles utilisées dans la boucle For … Next équivalente. Examinons-les pas à pas.

• La variable i est déclarée en tant qu’Integer et initialisée à 1.
• La boucle se poursuit tant que i est inférieur à 9.
• Les cellules de la ligne 1 et de la colonne 1 à 8 sont alors ciblées avec la méthode Cells() et on leur affecte la valeur de la variable i, c’est-à-dire 1 à 8.
• La variable i est incrémentée d’un à chaque passage dans la boucle avec l’instruction i = i + 1.

Vous voyez, il n’y a rien de bien compliqué.

La boucle Do While … Loop

Nous allons maintenant utiliser une boucle Do While … Loop pour obtenir le même résultat que dans la boucle While … Wend précédente. Voici le code utilisé :

Dim i As Integer

i = 1

Do While i < 9

  Cells(1, i) = i

  i = i + 1

Loop

Le code est strictement équivalent. Il n’y a que l’écriture qui change.

La boucle Do … Loop While

Voyons maintenant comment utiliser une boucle Do … Loop While pour obtenir le même résultat. Voici le code utilisé :

Dim i As Integer

i = 1

Do

  Cells(1, i) = i

  i = i + 1

Loop While i < 9

Ici, la condition de fin de boucle est testée en fin de boucle. Quelle que soit la valeur de i, la boucle s’exécute donc au moins une fois. Mis à part cette légère différence, le code est très semblable à celui utilisé dans les boucles précédentes.

La boucle Do Until … Loop

Voyons enfin comment utiliser une boucle Do Until … Loop pour arrive au même résultat. Voici le code utilisé :

Dim i As Integer

i = 1

Do Until i > 8

  Cells(1, i) = i

  i = i + 1

Loop

Ici, le test de fin de boucle est effectué en début de boucle. La boucle prend fin quand i est supérieur à 8 (et non tant que i est inférieur à 9 comme dans les autres boucles). Mis à part ce détail, le code est similaire à celui utilisé dans les autres boucles.

Maintenant que vous connaissez les différentes boucles utilisables en VBA, vous devrez trouver celle qui est la plus appropriée à chaque cas que vous devrez traiter…

Quitter une boucle prématurément

Cet article ne serait pas complet si nous ne parlions pas des sorties prématurées des boucles. Rien de bien sorcier rassurez-vous.

Supposons que lorsqu’une condition est vérifiée, vous vouliez terminer la boucle. Dans ce cas, vous utiliserez une des instructions suivantes :

Cet article va vous montrer comment effectuer des tests en VBA.

If Then Else

Il est parfois nécessaire d’exécuter une ou plusieurs instructions lorsqu’une condition est vérifiée, et éventuellement une ou plusieurs autres instructions dans le cas contraire. Pour cela, vous utiliserez une instruction If Then Else.

La syntaxe de l’instruction If Then Else

Voici la syntaxe de l’instruction If Then Else:

If condition Then

  ' Une ou plusieurs instructions

Else

  ' Une ou plusieurs instructions

End If

Le bloc Else ne sera pas nécessaire si une ou plusieurs instructions ne doivent pas être exécutées lorsque la condition n’est pas vérifiée. L’instruction se simplifie :

If condition Then

  ' Une ou plusieurs instructions

End If

Voici un exemple de code :

Dim entier As Integer

entier = 6

If entier = 5 Then

  MsgBox "entier vaut 5"

Else

  MsgBox "entier est différent de 5"

End If

Ici, on définit la variable Integer entier et on lui affecte la valeur 6. Par la suite, on teste si sa valeur est égale à 5 avec une instruction If Then Else. Comme ce n’est pas le cas, une boîte de message s’affiche et indique « entier est différent de 5 ».

Les opérateurs de comparaison utilisables dans un test

L’opérateur « = » n’est pas le seul utilisable dans un test If Then Else. Le tableau dresse la liste des opérateurs utilisables.

Ces opérateurs peuvent être appliqués sur :

• des nombres (Byte, Integer, Long, Currency, Single ou Double) ;
• des chaînes (String) ;
• des dates (Date).

Voici quelques exemples :

Dim n As Integer, s As String, d As Date

n = 1.2545

s = "Un peu de texte"

d = "12/08/2110"

If d > "10/05/2020" Then

  MsgBox "pas tout de suite…"

End If

If n = 1.2545 Then

  MsgBox "n est bien égal à 1.2545"

End If

If s > "Un peu" Then

  MsgBox "Au delà de Un peu"

End If

Si vous exécutez ce code, vous serez peut-être surpris, car il affiche les boîtes de dialogue suivantes :

    

La première boîte de dialogue est facile à comprendre. En effet, la date 12/08/2110 est postérieure à 10/05/2020. Le test If d > « 10/05/2020 » Then est donc vérifié et le message s’affiche.

Par contre, que devrait donner le test If n = 1.2545 Then selon vous ? Etant donné que n a été initialisé à 1.2545 un peu plus haut, le message « n est bien égal à 1.2545 devrait s’afficher. Eh bien non !

Pourquoi d’après vous ?

Tout simplement parce que la variable n a été définie en tant qu’Integer. Lors de son affectation, la valeur décimale a purement et simplement été supprimée.

Remplacez Integer par Single dans cette expression et le test devrait aboutir :

Dim n As Integer, s As String, d As Date

Que pensez-vous du troisième test ? Comment deux chaînes pourraient être comparées avec l’opérateur « > » ? Est-ce que « Un peu de texte » > « Un peu » ?

Selon VBA, oui !

La comparaison de deux chaînes se fait selon un ordre alphabétique. Ainsi :

A < B < E < Z

Mais aussi :

A < Abc < Bcd < Be < Htzert < Huv

Notez que l’instruction Option Compare peut changer les choses lorsque vous comparez des chaînes.

Après cette instruction :

Option Compare Binary

Les caractères sont comparés par rapport à leur représentation binaire. Ainsi :

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

Après cette instruction :

Option Compare Text

Les caractères sont comparés sans distinctions entre les majuscules et les minuscules :

(A=a) < (À= à) < (B=b) < (E=e) < (Ê= ê) < (Ø = ø) < (Z=z)

Comparaison de chaînes avec l’opérateur Like

L’opérateur Like est très puissant. Il permet de comparer une variable String et un modèle. La syntaxe générale de cette instruction est la suivante :

b = s Like modèle

Où b est une variable Boolean, s est une variable String et modèle est le modèle à appliquer à la chaîne s. Si la variable s correspond au modèle, la variable b est initialisée à True. Elle est initialisée à False dans le cas contraire.

Le modèle peut contenir un ou plusieurs caractères quelconques (des lettres, des chiffres ou des signes), mais aussi des caractères de remplacement :

Par exemple, pour tester si la cellule B5 contient un nombre composé de 5 chiffres, vous utiliserez le modèle « #####« . Voici ce que vous pourriez écrire :

Dim n As Integer

n = Range("B5")

MsgBox n

If n Like "#####" Then

  MsgBox "Vous avez bien entré un nombre à 5 chiffres"

Else

  MsgBox "Vous n'avez pas entré un nombre à 5 chiffres"

End If

Opérateurs logiques

Pour compléter vos tests, vous pouvez également utiliser des opérateurs logiques :

Test multiple Select Case

Supposons qu’une variable v puisse prendre plusieurs valeurs et que vous deviez effectuer un traitement spécifique pour chaque valeur. Dans ce cas, l’instruction If Then Else n’est pas appropriée. Vous utiliserez plutôt cette instruction :

If v = valeur1 Then

  ' Traitement 1

ElseIf v = valeur2 Then

  ' Traitement 2

ElseIf v = valeur3 Then

  ' Traitement 3

ElseIf v = valeur4 Then

  ' Traitement 4

ElseIf v = valeur5 Then

  ' Traitement 5

Else

  ' Traitement 6

End If

Si vous devez tester trois ou plus de trois valeurs, je vous conseille d’utiliser une instruction Select Case à la place du If Then ElseIf. Le test précédent devient donc :

Select Case v

  Case valeur1

    ' Traitement1

  Case valeur2

    ' Traitement2

  Case valeur3

    ' Traitement3

  Case valeur4

    ' Traitement4

  Case valeur5

    ' Traitement5

  Case Else

    ' Traitement6

End Select

Voici quelques exemples de l’instruction Case :

Case 5 'La valeur numérique 5

Case 10 To 20 'Entre 10 et 20

Case Is > 20 'Supérieur à 20

Case "a" 'La chaîne "a"

Case "a", "b", "c" ' La chaîne "a", "b" ou "c"

Attention

Avec les opérateurs <, > et <>, vous devez utiliser un Case Is et non un Case.

Les traitements peuvent être des instructions VBA quelconques. Par exemple :

a = 5 ' Affectation de la valeur 5 à la variable a

MsgBox "texte" ' Affichage d'une boîte de message

Sheets("Feuil2").Activate 'Activation de la feuille Feuil2

Vous pouvez également utiliser une instruction pour faire une pause dans l’exécution ou pour l’arrêter :

Stop ' Arrêt sur cette instruction

End ' Fin du code