Initiation à VBA pour Excel

Initiation à VBA pour Excel

VBA un langage objet

Depuis Excel 97, un éditeur Visual Basic réside sous Excel et permet d’écrire des programmes complexes. En fait, cette propriété est vraie pour toutes les applications (Word, Excel, Power Point, Access) de Microsoft Of-…ce. VBA, Visual Basic for Applications, le langage utilisé peut être vu comme l’application d’un langage, Visual Basic, à un ensemble d’applications, une collection d’objets, qu’il manie. Il ne faut pas confondre a priori le langage Visual Basic, qui est un langage complexe opéramment indépendamment, de VBA en général, de VBA pour Excel en particulier.

Le VBE

Pour activer l’éditeur de Visual Basic depuis Excel, dans la barre de menu Excel : 

A¢ chage ! Barre d’outils ! Visual Basic et on clique ensuite sur l’icône Visual Basic Editor (VBE). La fenêtre apparaît alors par défaut disposée comme dans la …gure 2.2.
On peut faire apparaître la fenêtre des codes en cliquant bouton droit sur This Workbook, situé dans la fenêtre Explorateur de projets (en haut à gauche), puis en cliquant en choisissant dans les menus Insertion puis Module. Dans la fenêtre de code (en haut à droite), on peut écrire le code mais aussi des commentaires. Ceux-ci sont le texte qui apparaît à droite d’un guillemet simple 0 .
Une organisation classique de la fenêtre de code du VBE sera de spéci…er tout d’abord les options (par exemple ici l’option d’expliciter la nature de toutes les variables utilisées, la spéci…cation du type de fonctions (procédure ou fonction proprement dite), la déclaration des variables à l’aide de la va-riable dim. Ci dessous on construit une fonction simpliste calculant le carré
Option Explicit
Function fnSquare(x As Double) As Double
Dim y As Double
y=x*x
fnSquare=y
End Function
Dim y As Double est simplement la déclaration que la variable est de type Double, c’est-à-dire un réel. De même l’argument de la fonction fnS-quare, x, est également un réel comme le souligne la déclaration x As Double. Le résultat de la fonction est lui aussi déclaré étant un réel par la syntaxe Function fonction As Double. La déclaration de la nature des variables, des arguments des fonctions ou du résultat de celles-ci n’est nullement obligatoire (en l’absence de l’option explicit) mais est généralement conseillé.

Un autre exemple de programme est celui de la procédure représentée sur la …gure 2.4 :
L’option explicit est encore une fois spéci…ée. La première ligne, après le trait horizon (automatiquement introduit par VBA) spéci…e la nature de la fonction (une procédure de type Sub) et son nom (procTitrageAléatoire). La ligne suivante est en vert car elle constitue un commentaire (étant introduite par un guillemet). Puis viennent la déclaration des variables :
– la première variable, appelée nom, est une chaîne de caractères (et donc son type est string) ;
– la seconde variable, appelée i, est un entier naturel.
Puis on fait pointer l’ordinateur vers une feuille appelée tirage que l’on désigne comme étant la feuille active, c’est-à-dire celle que l’ordinateur doit utiliser par défaut. A la variable nom est a¤ectée ensuite la chaîne de carac-tères tirages (la nature de chaîne de caractères étant attestée par les guille-ments). On procède ensuite aux 10 tirages aléatoires (par l’usage de la boucle For … Next, en utilisant la fonction aléatoire Rnd) et en stockant chaque ré-sultat dans la cellule de la ligne i et de la 1ère colonne (Cells(i,1)). Puis à la plage (Range) ainsi dé…nie, on a¤ecte le nom contenu par la variable nom par l’écriture : Range(\A1 : A10″):N ame = nom
En utilisant la fonction Excel (WorksheetFunction) de la moyenne (Average), on calcule la valeur moyenne des tirages aléatoires stockées dans la plage nom, valeur que l’on a¤ecte à la cellule de la ligne 12, colonne 1 : Range(\A12″):V alue = W orksheetF unction:Average(Range(nom))
Pour enclencher la procédure, il su¢ t alors de cliquer sur le premier bouton (triangle) du sous-menu : tandis que le second bouton (double traits) est le bouton d’arrêt, le carré le bouton de réinitialisation.

Les objets de Visual Basic

VBA est un langage orienté objet. Son code ne part donc pas comme dans les langages procéduraux des actions (par exemple l’impression) pour pointer vers les objets (par exemple une équation). Au contraire, ce langage dé…nit d’abord des objets pour ensuite soit pointer vers certaines de leurs propriétés, soit leurs appliquer des actions. Ainsi l’on désigne l’action de conduire une voiture, on n’écrira pas conduire(voiture), comme par exemple dans un langage comme le basic, mais on écrira voiture.conduire où voiture est l’objet auquel s’applique l’action de conduire. De même si l’on désire obtenir la propriété de la voiture qu’est sa couleur on écrira en VBA voiture.couleur.
Objets, propriétés, actions sont les principaux éléments que l’on manie dans ce langage.

Les objets et les collections

Le premier objet est Excel lui-même (appelé Application) puis viennent di¤érents objets souvent organisés en collection et notamment
Remarque 1 Visual Basic ne fait pas de di¤érence entre minuscule et ma-juscule. Les noms précédents peuvent donc être écrits de multiples manières selon que l’on recourt ou non aux majuscules.
Une collection se dé…nit comme un ensemble d’objets possédant les mêmes caractéristiques : la collection des …chiers (workbooks), la collection des feuilles de travail (worksheets), la collection des graphiques (charts), etc. La règle (comportant des exceptions) est que les collections se distinguent de leurs éléments par le s par lequel elles se terminent. L’élément d’une collection peut être notamment identi…ée soit par un nombre ou par un nom. Ainsi les feuilles de travail sont à la fois numérotées et ont un nom. Si l’on veut pointer vers la 10e feuille du classeur dont le nom est “stat”, on peut indi¤éremment taper :
worksheets(10) ou worksheets(“stat”)
les guillemets étant nécessaires pour que stat soit traité comme une chaîne de caractères et non comme une variable.

La hiérarchie des objets

Les objets sont organisés hiérarchiquement :
– au sommet réside Excel elle-même, l’objet Application ;
– puis vient notamment la collection des classeurs (workbooks) ;
– les feuilles de travail des classeurs (worksheets), la collection des gra-phiques (charts) ;
– ….
Les plages de cellules (range) constituent l’exception selon laquelle à chaque objet correspond une collection. Par contre à la cellule (cell) cor-respond la collection des cellules (cells). Le point . permet de spéci…er la hiérarchie des objets. Ainsi si l’on veut faire pointer l’ordinateur vers la plage “A1 :B10”de la feuille appelée “travail”du classeur nommé “essai”, on peut écrire :

Application.Workbooks(“essai”).Worksheets(“travail”).Range(“A1 :B10”)
Par défaut, l’ordinateur a une hiérarchie dé…nie par défaut. Elle correspond au dernier …chier utilisé sous Excel, à la dernière feuille activée, etc. Aussi c’est cette hiérarchie qui sera utilisée si on utilise une spéci…cation incom-plète, par exemple si on ne spéci…e dans notre exemple que la plage en écrivant seulement Range(“A1 :B10”). Si le dernier …chier activé, la dernière feuille utilisée sont bien respectivement “essai”et “travail”, cela n’aura donc pas de conséquence. Par contre si cela n’est pas le cas, le programmeur aura des sur-prises ! Il est donc important d’être prudent dans les racourcis d’écriture. On pourra toujours faire l’économie du premier terme et donc écrire seulement : Workbooks(“essai”).Worksheets(“travail”).Range(“A1 :B10”)

Mais pour les autres niveaux, tout dépend encore une fois des objets actifs sous Excel. Ainsi si …chier “essai” est le …chier par défaut (peut-être parce qu’il a été antérieurement activé par une commande : Workbooks(“essai”).Activate) alors la chaîne se réduira à : Worksheets(“travail”).Range(“A1 :B10”)

Remarque : Le point est également utilisé non seulement pour passer d’un objet vers un de ses subordonnés mais aussi pour pointer vers une propriété ou vers une action. Ainsi, si l’on veut colorier la plage “A1 :B10” en gris, on écrira : Range(“A1 :B10”).Interior.Color=vbGrey
où Interior est l’intérieur des cellules de la plage (un objet donc), Color est évidemment une propriété de l’intérieur, vbGrey est la constante désignant sous Visual Basic le gris (ou plus exactement une manière de dé…nir un gris sous VBA). Si l’on veut aussi supprimer la feuille “travail”, on utilisera aussi la syntaxe suivante : Worksheets(“travail”).Delete
Dans ce dernier exemple, comme dans le précédent, on n’a pas explicité toute la hiérarchie des objets en supposant que les objets supérieurs étaient par défaut correctement dé…nis – ce qui assure par exemple dans notre dernier exemple que notre écriture simpli…ée est équivalente à
Application.Workbooks(“essai”).Worksheets(“travail”).Delete

Le modèle objet d’Excel

A chaque application d’O¢ ce (et O¢ ce lui-même) correspond un modèle objet, hiérarchie de collections d’objets. Comme ce modèle objet di¤ère par exemple entre Excel et Word, maîtriser VBA pour Excel n’assure pas de pouvoir sans di¢ culté programmer VBA sous Word, le langage commun s’appliquant à des objets parfois di¤érents. Pour avoir un aperçu du modèle objet de VBA pour Excel, il su¢ t dans l’éditeur de VBA.