Visual Basic pour Excel : compléments

Les plages de cellules

Nous avons exposé au chapitre précédent comment travailler avec des plages de cellules simples. Il est souvent nécessaire d'aller plus loin car la sélection de plages de cellules est souvent au cœur des programmes Visual Basic.

Les feuilles de calcul

Commençons par désigner la feuille de calcul dans laquelle nous voulons sélectionner des plages de cellules.

Il y a deux manières principales pour désigner une feuille de classeur : soit par son nom, soit par son numéro. Pour sélectionner une feuille on pourra donc écrire :

Sheets("Feuil1").Select

Ou :

Sheets(1). Select

Lorsque le classeur vient d'être créé, les deux écritures sont équivalentes car Feuil1 correspond à la première feuille mais ce n'est plus le cas si l'on crée d'autres feuilles et qu'on les déplace.

Le numéro désigne la position de la feuille dans la liste des onglets, si on la déplace elle change de numéro. Ainsi, 1 est le numéro de la feuille la plus à gauche, 2 est le numéro de la feuille dont l'onglet est immédiatement à sa droite et ainsi de suite. À l'inverse une feuille ne change pas de nom quand on la déplace.

On peut déplacer une feuille avec Visual Basic en utilisant la méthode Move. Par exemple :

Sheets(4).Move before:=Sheets(1)

On aurait pu utiliser after au lieu de before pour déplacer la feuille après plutôt qu'avant. On peut utiliser la méthode Copy pour copier la feuille et non la déplacer.

Notons que nous avons utilisé ici l'opérateur ":=" et non l'opérateur "=". En effet, Visual Basic utilise l'opérateur = pour affecter une valeur ou un objet à une variable et l'opérateur := pour affecter une valeur au paramètre d'une propriété ou d'une méthode.

On peut également changer le nom d'une feuille. Par exemple :

Sheets("Feuil1").Name = "Bonjour"

Lorsque dans un programme on fait plusieurs fois référence à la même feuille, il est plus pratique de l'affecter à une variable. Par exemple :

Set f = Sheets("Feuil1")

Lorsque plusieurs classeurs sont ouverts en même temps, il est préférable de préciser auquel on se réfère. Si l'on se réfère au classeur contenant le programme on peut écrire :

Set f = ThisWorkBook.Sheets("Feuil1")

On peut également se référer à un autre classeur ouvert, par exemple :

Set f = WorkBooks("Essai.xlsx").Sheets("Feuil1")

Les plages de cellules

On peut désigner une plage par ses références ou par son nom, par exemple Range("B2:C6") ou Range("Plage"). On peut également utiliser une variable texte désignant les références de la plage ou son nom. Par exemple :

Set f = ThisWorkbook.Sheets(1)
a = "A2:C8"
b = "Plage"
f.Range(a).Select
f.Range(b).Select

Pour utiliser la référence ou le nom d'un champ, il faut les mettre entre guillemets, pour utiliser une variable il ne faut pas de guillemets.

Cells

On peut également utiliser la propriété Cells pour désigner une plage de cellules. Par exemple :

Set f = ThisWorkbook.Sheets("Feuil1")
f.Range("A2").Cells(2,5) = "Essai"

Le mot Essai apparaît à l'intersection de la deuxième ligne et de la cinquième colonne de la feuille Feuil1.

Notons que la propriété Cells compte les lignes et les colonnes à partir du coin supérieur gauche de la plage à laquelle elle s'applique. Dans notre exemple de référence, la plage à laquelle s'appliquait la propriété Cells était la feuille entière mais elle pourrait également être une autre plage définie par la propriété Range. Par exemple :

Set f = ThisWorkbook.Sheets("Feuil1")
f.Range("C2").Cells(2,5) = "Essai"

Dans ce cas, Essai apparaît à l'intersection de la ligne 3 et de la colonne 7.

On peut également utiliser la propriété Cells pour désigner une plage de plusieurs cellules, par exemple :

f.Range(Cells(2, 5), Cells(6, 8)).ClearContents efface le contenu de la plage définie par les coordonnées de ses deux extrémités.

Lorsque l'on veut définir une plage avec la propriété Cells dans un grand tableau, il est souvent pratique de sélectionner dans le menu de la feuille de calcul Fichier puis Options, Formules et de cliquer sur Style de référence L1C1, ce qui a pour effet d'afficher le numéro des colonnes dans les feuilles de calcul.

Les noms

On peut définir le nom d'une cellule ou d'une plage en utilisant la propriété Name. Par exemple :

Set f = ThisWorkbook.Sheets("Feuil1")
f.Range("A2").Name = "Essai"

Dans ce cas, le nom est défini au niveau du classeur. Pour définir un nom au niveau d'une feuille, il faut procéder autrement en utilisant la propriété RefersToR1C1, par exemple :

Set f = ThisWorkbook.Sheets("Feuil1")
f.Names.Add Name:="Essai", RefersToR1C1:="=R4C5:R7C8"

Ici, R fait référence au numéro de ligne et C au numéro de colonne. Notons qu'il faut un signe égal à l'intérieur des guillemets.

On aurait obtenu le même résultat en utilisant la propriété RefersTo de la manière suivante :

f.Names.Add Name:="Essai", RefersTo:="=$E$4:$H$7"

Ici, il faut utiliser la notation absolue avec le signe $.

On aurait pu utiliser la même procédure pour définir un nom au niveau d'une cellule en faisant référence au classeur et en spécifiant la feuille dans la définition de la plage. Par exemple :

ThisWorkbook.Names.Add Name:="Essai", RefersToR1C1:="=Feuil1!R4C5:R7C8"

On peut modifier la cellule ou la plage à laquelle le nom fait référence en utilisant les propriétés RefersTo ou RefersToR1C1, par exemple :

f.Names("Essai").RefersTo = "=Feuil1!$C$4:$E$10"

Décaler ou redimensionner une plage

Pour sélectionner une plage décalée par rapport à une plage donnée on peut utiliser la méthode Offset. Par exemple :

f.Range("A2:C8").Offset(2, 4).Select
sélectionne "E4:G10" car elle a décalé la plage de 2 lignes vers le bas et de 4 colonnes à droite.

La méthode Resize permet de redimensionner une plage. Par exemple :

f.Range("A2:C8").Resize(20, 10).Select
sélectionne la plage "A2:J21" car elle a redimensionné la plage "A2:C8" à partir de son coin supérieur gauche en portant son nombre de lignes à 20 et son nombre de colonnes à 10.

Lignes et colonnes

Il est possible de sélectionner une ligne ou une colonne en utilisant les propriétés Rows et Columns. Par exemple Rows(4).Select sélectionne la troisième ligne et Columns(4).Select sélectionne la quatrième colonne.

Il est souvent utile de connaître le nombre de lignes et de colonnes d'une plage. On utilise pour cela la propriété Count. Par exemple, f.Range("A2:C8").Rows.Count renvoie 7, c'est-à-dire le nombre de lignes de la plage. f.Range("A2:C8").Columns.Count renvoie 3, c'est-à-dire le nombre de colonnes de la plage.

Attention : Row renvoie le numéro de la première ligne de la plage et Column renvoie le numéro de sa première colonne. Par exemple f.Range("A2:C8").Row renvoie 2 et f.Range("A2:C8").Column renvoie 1.

Pour sélectionner une plage rectangulaire autour d'une cellule, on utilise la méthode CurrentRegion, elle sélectionne une zone rectangulaire délimitée par une ligne et une colonne vides.

Travailler avec des plages

On peut faire des calculs avec les cellules d'une feuille, soit simplement, soit en les combinant avec les fonctions de visual basic. Par exemple :

f.Range("A1")=f.Range("A2")*34 + f.Range("B1")*66
f.Range("A1") = Mid(Range("B1"), 3, 4)

On peut aussi utiliser des fonctions de la feuille de calcul dans leur version anglaise en les précédant de Application. Par exemple 

f.Range("A2") = Application.Average(Range("B2:E2"))
f.Range("A3") = Application.Sum(10.4, 22.6, 40)

On peut aussi faire directement des calculs sur des plages en utilisant des formules matricielles grâce à la propriété FormulaArray. Par exemple :

f.Range("G15:J20").FormulaArray = "=G4:G9*G11:J11/G13:J13"

Elle peut encore s'écrire :

f.Range("G15:J20").FormulaArray = "=R[-11]C:R[-6]C[3]*R[-4]C:R[-4]C[3]/R[-2]C:R[-2]C[3]"

Réduire les temps de traitement

Les opérations de lecture et d'écriture dans des cellules prennent beaucoup de temps en comparaison des opérations réalisées directement dans la mémoire. Lorsque l'on doit travailler sur de grandes plages de cellules, la durée de traitement des opérations peut être non négligeable et il peut être utile de la réduire.

Un premier moyen pour cela est de figer l'écran pendant les opérations de lecture et d'écriture dans les plages. Ce sera fait grâce à la propriété ScreenUpdating. Au début du programme, on figera l'écran en écrivant :

ScreenUpdating = False

A la fin du programme, il ne faut surtout pas oublier de réactiver l'écran en écrivant :

ScreenUpdating = True

Le deuxième moyen pour accélérer les programmes est de copier les plages dans des variables, de travailler sur ces variables et de les recopier ensuite dans des plages.

Par exemple, supposons que l'on veuille multiplier par 2 toutes les cellules de la plage "A1:BA50000". Une première méthode consiste à multiplier directement par 2 chaque cellule de la plage, par exemple avec le programme suivant :

Sub MultiplicationPlage()
Set f = ThisWorkbook.Sheets(1)
f.Range("A1:BA50000") = 100
For i = 1 To 50000
     For j = 1 To 53
         f.Cells(i, j) = 2 * f.Cells(i, j)
     Next j
Next i
End Sub

Ce programme est extrêmement long à s'exécuter et, si vous êtes pressé, je vous conseille de tester directement le programme suivant qui s'exécute très rapidement :

Sub MultiplicationPlage()
Dim a As Variant
Set f = ThisWorkbook.Sheets(1)
f.Range("A1:BA50000") = 100
a = f.Range("A1:BA50000")
For i = 1 To 50000
     For j = 1 To 53
         a(i, j) = 2 * a(i, j)
     Next j
Next i
f.Range("A1:BA50000") = a
End Sub

Variables et constantes

Les variables

Il est toujours préférable de déclarer les variables car cela permet une exécution plus rapide des programmes. Lorsqu'une variable n'est pas déclarée, Visual Basic lui attribue automatiquement le type Variant, c'est-à-dire qu'il est possible de lui attribuer n'importe quel type de valeur ou d'objet.

Il existe plusieurs manières de déclarer des variables. La plus habituelle est d'utiliser l'instruction Dim à l'intérieur d'une procédure, c'est-à-dire entre les instructions Sub et End Sub. Généralement, l'instruction Dim est placée directement après l'instruction Sub. Il y a cependant d'autres manières de déclarer une variable.

Visual Basic dispose de modules dans lesquels sont écrites les procédures, il existe également des procédures rattachées directement à des feuilles de calcul ou au classeur. Aussi, lorsque l'on veut définir une variable, on doit se demander quelle doit être sa portée, c'est-à-dire se demander si cette variable doit pouvoir être utilisée uniquement à l'intérieur d'une procédure spécifique, dans toutes les procédures d'un module ou dans toutes les procédures de l'ensemble des modules.

• Lorsqu'une variable ne doit être utilisée qu'au sein d'une procédure, elle doit être déclarée à l'intérieur de cette procédure.
• Pour qu'une variable puisse être utilisée par toutes les procédures d'un même module, elle doit être déclarée au tout début du module, avant la première procédure.
• Pour qu'une variable puisse être utilisée par toutes les procédures de l'ensemble des modules, elle doit être déclarée au tout début d'un module en utilisant l'instruction Public au lieu de Dim.

Les constantes

Il est souvent intéressant de travailler avec des constantes, c'est à-dire des variables dont la valeur ne changera pas tout au long de l'exécution du programme. C'est par exemple le cas lorsque l'on veut définir une codification qui sera reprise sans changement dans tout le programme.

Pour définir une constante, il faut utiliser l'instruction Const et fixer sa valeur, par exemple :

Const a=10.4
Const b as Integer=2
Const c as String="Bonjour"

Comme pour les variables, la portée des constantes dépend de leur mode de déclaration :

• une constante déclarée à l'intérieur d'une procédure n'est utilisable qu'à l'intérieur de cette procédure ;
• une constante déclarée au tout début d'un module est utilisable par toutes les procédures de ce module ;
• pour qu'une constante soit utilisable dans toutes les procédures de l'ensemble des modules, il faut la déclarer au tout début d'un module et faire précéder l'instruction Const de Public.

Il n'est pas possible de déclarer directement un tableau comme constante. Si on veut le faire, il faut utiliser une chaîne de caractères et la fonction Split qui décompose une chaîne en matrice à partir d'un séparateur. Le programme ci-dessous utilise le séparateur ; et transforme la chaîne des constantes en un tableau à une dimension :

Public Const tabl As String = "56;87;98"
-----------------------------------------
Sub constantes()
Dim tableau(2)
Set f = ThisWorkbook.Sheets(1)
a = Split(tabl, ";")
For i = 0 To 2
     b = Split(a(i), ";")
     tableau(i) = Split(a(i), ";")
     f.Cells(i + 1, 10) = tableau(i)
     f.Cells(i + 1, 11) = a(i)
Next i
End Sub

Notons que la variable a est déjà une matrice de dimension 1 et qu'il n'est donc pas nécessaire de la recopier dans une autre matrice.

Si l'on veut définir un tableau de constantes à 2 dimensions, on peut utiliser une chaîne de caractères avec deux séparateurs différents, par exemple ; et > comme dans le programme ci-dessous :

Public Const tabl As String = "1;11;12;13>2;21;22;23>3;31;32;33>4;41;42;43>5;51;52;53"
--------------------------------------------------------------------------------------
Sub constantes()
Dim tableau(4, 3)
Set f = ThisWorkbook.Sheets(3)
a = Split(tabl, ">")
For i = 0 To 4
     b = Split(a(i), ";")
     For j = 0 To 3
         tableau(i, j) = b(j)
         f.Cells(i + 1, j + 1) = tableau(i, j)
     Next j
Next i
End Sub

Les messages

Il est souvent utile de communiquer ou de demander des informations à l'utilisateur au cours de l'exécution d'un programme Visual Basic. Pour cela, Excel dispose de boîtes de dialogue qu'il est possible de définir à partir de Visual Basic. Nous ne présenterons ici que les plus courantes.

La fonction MsgBox pour afficher des messages

La fonction MsgBox permet soit d'afficher des messages simples, soit de demander une réponse à l'utilisateur.

Pour écrire un message simple, la fonction MsgBox doit être suivie du message placé entre guillemets, comme dans l'exemple suivant  :

Sub Message()
MsgBox "Bonjour, je suis heureux de travailler avec vous"
End Sub

Ce programme fait apparaître le message suivant :

En cliquant sur OK, on fait disparaître le message et le programme poursuit son exécution.

On peut également rajouter un bouton et un titre. Par exemple, le programme suivant fait apparaître le message avec le titre Bienvenue et l'icône d'information :

Sub Message()
MsgBox "Bonjour, je suis heureux de travailler avec vous", vbInformation, "Bienvenue"
End Sub

Si l'on veut afficher le titre mais pas l'icône, il faudra écrire :

MsgBox "Bonjour, je suis heureux de travailler avec vous", ,"Bienvenue"

D'une manière plus générale, la fonction MsgBox comprend trois arguments séparés par des virgules, le premier est le texte du message placé entre guillemets, le deuxième définit le type de bouton à afficher et le troisième est le titre du message placé entre guillemets. Les différents types de boutons sont les suivants ;

vbOKOnly	Affiche le boutonOK uniquement.
vbOKCancel	Affiche les boutons OK et Annuler.
vbAbortRetryIgnore	Affiche les boutonsAbandonner, Réessayer, et Ignorer.
vbYesNoCancel	Affiche les boutons Oui, Non et Annuler.
vbYesNo	Affiche les boutons Oui et Non.
vbRetryCancel	Affiche les boutons Réessayer et Annuler.
vbCritical	Affiche l’icôneMessage critique.
vbQuestion	Affiche l’icôneRequête d’avertissement.
vbExclamation	Affiche l’icôneMessage d’avertissement.
vbInformation	Affiche l’icôneMessage d’information.

Par défaut, le titre du message est "Microsoft Excel".

La fonction MsgBox pour poser des questions

Si l'on veut poser une question à l'utilisateur, il faut utiliser la fonction MsgBox en la faisant suivre de parenthèses comprenant la question, le type de bouton et le titre.

Prenons l'exemple du programme suivant :

Sub Message()
Dim Reponse As Integer
Reponse = MsgBox("Bonjour, êtes vous content de travailler avec moi ?", vbYesNo + vbQuestion, "Bienvenue")
If Reponse = vbYes Then MsgBox "Continuons donc de travailler ensemble !", vbExclamation, "C'est un plaisir pour moi aussi."
If Reponse = vbNo Then MsgBox "Dans ces conditions, il vaut mieux arrêter !", vbCritical, "J'en suis vraiment désolé !"
End Sub

Ce programme fait apparaître le message suivant qui demande une réponse :

Si la réponse est Oui, il fait apparaître :

Si la réponse est non, il fait apparaître le message suivant :

Dans le programme, la première fonction MsgBox renvoie une valeur que nous plaçons dans la variable Reponse. Si cette valeur est vbYes nous affichons le deuxième message, si elle est vbNo c'est le troisième message qui est affiché.

Remarquons que dans le premier message nous avons combiné les boutons Oui - Non avec l'icône interrogation en ajoutant vbYesNo et vbQuestion.

La fonction InputBox

La fonction InputBox permet de communiquer un nombre ou un texte au programme. Par exemple, le programme ci-dessous demande d'introduire son prénom.

Sub Message()
Dim Prenom As String
Prenom = InputBox("Entrez votre prénom", "Présentez-vous")
MsgBox "Bonjour " & Prenom & ", c'est un plaisir de travailler avec vous !", , "Bienvenue " & Prenom
End Sub

La boîte suivante apparaît :

Si le prénom entré est Laure, le message suivant apparaît :

Dans la fonction InputBox, le premier texte correspond au message et le second au titre.

Les codes couleur

Les couleurs peuvent très utiles pour améliorer la visibilité d'une feuille Excel, il est donc important de pouvoir les traiter.

En Visual Basic pour Excel, les couleurs peuvent être codées de deux manières qui toutes deux combinent les trois couleurs primaires que sont le rouge, le vert et le bleu.

Le codage RGB

Le système de codage des couleurs le plus simple à comprendre est le RGB où R correspond à red (rouge), G à green (vert) et B à blue (bleu). Chaque niveau de couleur est représenté par un nombre qui va de 0 à 255. Par exemple RGB(255,0,0) représente le rouge, RGB(0,255,0) représente le vert et RGB(0,0,255) représente le bleu. On peut également mélanger les couleurs, ainsi RGB(0, 255, 255) donne le bleu clair et RGB(255, 255, 0) donne le jaune.

Le codage hexadécimal

Le second système de codage des couleurs est le système hexadécimal. Les nombres sont alors écrits en base 16, c'est-à-dire que les chiffres de 0 à 9 sont complétés par les lettres A, B, C, D, E et F. Le système correspond au système RGB avec deux différences. La première est que les nombres sont écrits en hexadécimal, c'est-à-dire, par exemple, que 255 s'écrit FF. La seconde est que les couleurs sont inversées par rapport au code RGB. Ainsi, les deux premiers caractères correspondent au bleu, les deux suivants au vert et les deux derniers au rouge.

Dans un programme Visual Basic, le code hexadécimal doit être précédé de &H et suivi par &. Comme dans le système décimal, les zéros à gauche du nombre ne sont pas écrits. Par exemple, du fait de l'inversion par rapport à RGB, &HFF& est équivalent à RGB(255,0,0), il représente le rouge. Le bleu s'écrira &HFF0000. Notons que quand le code a six caractères, on ne peut pas ajouter le signe &. Là encore, on peut mélanger les couleurs primaires. Ainsi, le jaune s'écrit &HFFFF& en mélangeant le rouge et le vert.

Lancement automatique d'une procédure

Il peut être intéressant de lancer automatiquement une procédure à l'ouverture du classeur Excel. Pour cela, il faut d'abord aller dans l'objet ThisWorkbook de l'explorateur de projets (le plus souvent situé à gauche de la fenêtre de l'éditeur VBA).

En double-cliquant sur l'icône ThisWorkbook on fait apparaître sa page de code dans laquelle on écrira la procédure suivante :

Private Sub Workbook_Open()
 
End Sub

On écrira alors à l'intérieur de cette procédure le code souhaité. Par exemple :

Private Sub Workbook_Open()
MsgBox "Bonjour, c'est un plaisir de travailler avec vous !"
End Sub

Ce message s'affichera alors automatiquement à chaque ouverture du classeur.

Protéger un classeur avec un mot de passe

Il est souvent utile de protéger un classeur avec un mot de passe pour éviter qu'il puisse être modifié de manière accidentelle. Cela peut se faire directement par le menu de la feuille de calcul mais cela peut aussi se faire depuis un programme Visual Basic grâce à la méthode Protect. Cette méthode peut appliquer aussi bien à un classeur qu'à une feuille de calcul. Par exemple :

ThisWorkbook.Sheets("Accueil").Protect Password:="ABCD"

Où ABCD est le mot de passe. On peut également interdire la sélection de cellules protégées de la manière suivante :

ThisWorkbook.Sheets("Accueil").EnableSelection = xlUnlockedCells

On peut rajouter des options à la méthode Protect, par exemple AllowUsingPivotTables qui permet d'utiliser les tableaux croisés dynamiques sans pouvoir toutefois les modifier. On l'écrit ainsi :

ThisWorkbook.Sheets("Accueil").Protect Password:="ABCD", AllowUsingPivotTables:=True

On peut également protéger le classeur de la même manière. Par exemple :

ThisWorkbook.Protect Password:="ABCD", Structure:=True, Windows:=False

Pour ôter la protection, il faut utiliser la méthode Unprotect, par exemple :

ThisWorkbook.Sheets("Accueil").Unprotect Password:="ABCD"

Si l'on veut créer un mot de passe particulièrement difficile à retrouver, on peut utiliser un mot de passe généré de manière aléatoire. Par exemple avec le programme :

a = Int(Rnd * 100000)
pw = "QGa!Ez" & a
ThisWorkbook.Sheets("Accueil").Protect Password:=pw

Bien entendu, dans ce cas il est indispensable de garder une copie non protégée pour pouvoir la modifier ultérieurement.

Protéger les programmes

Il est aussi possible de protéger les programmes Visual Basic. Pour cela, dans la feuille Visual Basic, il faut cliquer avec le bouton droit sur VBAProject. Il faut alors sélectionner Propriétés de VBAProject, dans le cadre qui apparaît il faut aller dans Protection puis entrer le mot de passe. La protection sera effective après avoir enregistré et fermé le classeur. Les programmes ne seront alors pas visibles lors de la prochaine ouverture.

Pour ôter la protection, il faut aller dans le menu Développeur puis sélectionner Visualiser le code. Après avoir entré le code, la feuille Visual Basic apparaît. On peut alors développer VBAProject et choisir le module auquel on veut accéder.

La gestion des erreurs

Lorsqu'il rencontre une erreur, par exemple une division par zéro, le programme Visual Basic s'arrête et affiche un message d'erreur. Cela permet généralement au programmeur de corriger une erreur involontaire mais, parfois, l'erreur provient de circonstances particulières et non véritablement d'une erreur de programmation.

Par exemple, on veut ouvrir un classeur Excel mais il n'existe plus ou a changé de nom. On peut, bien sûr, tester l'existence du classeur avant de tenter de l'ouvrir mais, lorsque les circonstances pouvant générer une erreur sont multiples, on peut préférer gérer toutes les sources d'erreur de la même manière. C'est possible grâce à l'instruction On Error.

L'instruction On Error se décline de trois manières :

• On Error GoTo, qui renvoie à une ligne désignée par une étiquette lorsqu'une erreur est rencontrée ;
• On Error Resume Next qui renvoie à la ligne suivant immédiatement la ligne ayant généré l'erreur ;
• On Error GoTo 0 qui désactive et réinitialise la gestion des erreurs.

Une étiquette est un nom placé au tout début d'une ligne et suivi de : (deux points), par exemple :

Erreur:

La gestion des erreurs n'est pas simple et pour mieux la comprendre nous partirons d'un exemple. Le programme suivant se propose de remplacer les nombres situés dans la plage A1:G1 par leur inverse, cette plage se présentant comme suit :

Sub EssaiErreur()
Dim a As Range
Set f = ThisWorkbook.Sheets("Feuil1")
For Each a In Range("A1:G1")
     a = 1 / a
Next a
End Sub

Quand on le lance, ce programme se bloque sur la cellule D1 et affiche le message "Incompatibilité de type". En effet, la cellule D1 contient la lettre e et on ne peut donc pas calculer son inverse. Pour résoudre le problème, nous allons introduire dans le programme l'instruction On Error Resume Next :

Sub EssaiErreur()
Dim a As Range
Set f = ThisWorkbook.Sheets("Feuil1")
On Error Resume Next
For Each a In Range("A1:G1")
     a = 1 / a
Next a
End Sub

Ce programme fonctionne parfaitement, il ne calcule l'inverse que quand il le peut et ne se bloque pas.

Supposons maintenant que nous voulions introduire la valeur 1000 dans les cellules où l'inversion n'est pas possible. Il nous faut détecter ces cellules dans le programme et pour cela nous allons utiliser l'objet Err dont la propriété est un nombre qui caractérise le type d'erreur rencontré, ce nombre étant initialisé à zéro.

Cela veut dire que tant que le programme n'a pas rencontré d'erreur, Err a une valeur nulle et cette valeur ne change que quand une erreur est rencontrée. Après l'erreur, la valeur de Err ne revient pas à zéro automatiquement, pour le faire il faut utiliser la méthode Err.Clear.

Nous allons utiliser le programme suivant :

Sub EssaiErreur1()
Dim a As Range
Set f = ThisWorkbook.Sheets("Feuil1")
On Error Resume Next
For Each a In Range("A1:G1")
     a = 1 / a
     If Err <> 0 Then
         a = 1000
     End If
Next a
b = 1 / 0
End Sub

Nous avons inséré à la fin de ce programme une division par zéro pour tester le traitement des erreurs involontaires.

Si nous lançons le programme, nous constatons qu'il ne se bloque pas et qu'il affiche le résultat suivant :

Le programme a bien mis 1000 quand il a rencontré la lettre e mais il a continué à mettre 1000 même quand il n'y avait pas d'erreur. De plus, il ne nous a pas signalé notre erreur involontaire, c'est-à-dire la division par zéro.

En fait, notre programme n'a pas fonctionné comme voulu parce que Err ne revient pas automatiquement à zéro, si bien qu'il est resté positif après la première erreur. Pour que le programme fonctionne correctement, il faut donc réinitialiser Err après avoir traité l'erreur.

De plus, si l'on veut pouvoir détecter les erreurs involontaires dans la suite du programme, il nous faut désactiver la gestion des erreurs avant de poursuivre le programme. On aura donc :

Sub EssaiErreur1()
Dim a As Range
Set f = ThisWorkbook.Sheets("Feuil1")
On Error Resume Next
For Each a In Range("A1:G1")
     a = 1 / a
     If Err <> 0 Then
         a = 1000
         Err.Clear
     End If
Next a
On Error GoTo 0
b = 1 / 0
End Sub

Ce programme fonctionne parfaitement car il fait bien l'inversion quand il le peut et se bloque pour nous indiquer la division par zéro involontaire.

D'une manière générale, lorsque nous utilisons l'instruction On Error Resume Next, nous avons deux possibilités :

• nous ignorons l'erreur et nous continuons, la ligne erronée n'est pas exécutée ;
• nous voulons agir différemment selon qu'il y a ou non une erreur ; dans ce cas, il faut indiquer au programme ce qu'il doit faire en cas d'erreur et ce qu'il doit faire quand il n'y a pas d'erreur.

Dans le premier cas, on se contentera de l'instruction On Error Resume Next, dans le second on pourra structurer le programme de la manière suivante :

Nous pouvons également utiliser l'instruction On Error GoTo pour gérer les erreurs. Le programme précédent pourrait alors s'écrire :

Sub EssaiErreur4()
Dim a As Range
Set f = ThisWorkbook.Sheets("Feuil1")
On Error GoTo Erreur
For Each a In Range("A1:G1")
     a = 1 / a
Boucle:
Next a
On Error GoTo 0
Exit Sub
Erreur:
a = 1000
Resume Boucle
End Sub

Dans ce programme, nous avons utilisé des étiquettes et l'instruction Resume. Quand elle est suivie du nom d'une étiquette, cette instruction renvoie à la ligne de l'étiquette, sinon elle renvoie à la ligne qui a généré l'erreur. Dans ce dernier cas, attention aux boucles infinies si l'erreur n'est pas corrigée. L'instruction Resume réinitialise la gestion des erreurs, c'est-à-dire qu'elle remet Err à zéro.

Pour éviter que les lignes gérant les erreurs soient exécutées même quand il n'y a pas d'erreur, nous les avons placées à la fin du programme et nous les avons fait précéder de l'instruction Exit Sub qui provoque l'arrêt du programme.

D'une manière générale, la structure d'un programme utilisant l'instruction On Error GoTo pourrait être la suivante :

Auteur : Francis Malherbe