Médiaforma

All posts in VBAExcel

Depuis la sortie d’Excel 2007, les classeurs peuvent être stockés avec une extension xlsx, xlsm ou xlsb.

  • Les fichiers xlsx ne peuvent pas contenir du code VBA.
  • Les fichiers xlsm peuvent contenir du code VBA.
  • Les fichiers xlsb (classeurs Excel binaire) peuvent contenir ou ne pas contenir du code VBA. Contrairement aux deux autres formats de fichiers, ils sont compressés et prennent donc moins de place sur le disque.

En ce qui concerne cette série d’articles, vous sauvegarderez vos classeurs au format xlsm, sans quoi, le code VBA ne sera pas mémorisé.


Lorsque vous utilisez Excel, vous sélectionnez fréquemment des cellules et des plages de cellules. Eh bien, VBA est en mesure d’effectuer les mêmes sélections.

Sélection d’une cellule

Deux fonctions peuvent être utilisées : Cells() et Range(). Leur syntaxe est très différente. A vous de décider quelle est celle qui vous convient le mieux.

Par exemple, cette instruction sélectionne la cellule à l’intersection de la ligne 4 et de la colonne 1 :

Cells(4,1).Select

Ou encore, cette instruction sélectionne la cellule A4, qui se trouve à l’intersection de la ligne 4 et de la colonne 1 :

Range("A4").Select

Sélection d’une plage de cellules

La fonction Range peut également être utilisée pour sélectionner une plage de cellules. Par exemple, cette instruction sélectionne les cellules A4 à G12 :

Range("A4:G12").Select

Sélection de plusieurs plages de cellules

La fonction Range() permet également de sélectionner plusieurs plages de cellules. Par exemple, pour sélectionner les cellules A4 à B5 et les cellules B9 à D11, vous utiliserez cette instruction :

Range("A4:B5,B9:D11").Select

Sélection d’une plage de cellules nommées

Lorsqu’une plage de cellules est nommée, vous pouvez la sélectionner en précisant son nom dans la fonction Range(). Supposons que la plage de cellules A8:E8 ait pour nom resultats. Pour la sélectionner en VBA, vous utiliserez cette instruction :

Range("resultats").Select

Pour que cette instruction fonctionne, la plage resultats doit avoir été définie, sans quoi, une erreur se produira à l’exécution :

Le nom de la plage n’est pas sensible à la casse des caractères. Les instructions suivantes sont dont tout aussi valables pour sélectionner la plage A8:E8 :

Range("Resultats").Select

Range("RESULTATS").Select

Sélection de lignes et de colonnes

Les fonctions Rows() et Colums() permettent de sélectionner une ou plusieurs lignes et colonnes. Par exemple, pour sélectionner la colonne C, vous écrirez :

Columns("C").Select

Pour sélectionner les colonnes C à G, vous écrirez :

Columns("C:G").Select

D’une façon similaire, pour sélectionner la ligne 3, vous écrirez :

Rows("3").Select

Ou encore, pour sélectionner les lignes 3 à 7, vous écrirez :

Rows("3:7").Select

Sélection de lignes et de colonnes disjointes

Supposons que vous vouliez sélectionner les lignes 4, 9 et 10. Vous pouvez tenter quelque chose comme ceci :

Rows("4,9,10").Select

Pas de chance, cette instruction produit une erreur à l’exécution :

Vous utiliserez plutôt la fonction Range() :

Range("4:4,9:10").Select

De même, supposons que vous vouliez sélectionner les colonnes B, D et F à H, vous utiliserez cette instruction :

Range("B:B,D:D,F:H").Select

Sélection de toute la feuille de calcul

Pour sélectionner toutes les cellules de la feuille de calcul, vous utiliserez cette instruction :

Cells.Select

Pour vous représenter physiquement un objet Excel, imaginez que vous avez devant vous une boîte noire dont vous ne pouvez pas voir l’intérieur. Par contre, vous avez une documentation qui décrit les caractéristiques (on parle de propriétés) de l’objet. A l’aide de cette documentation, vous avez donc une vue globale de l’objet, sans pour autant en connaitre les mécanismes. Mais après tout, c’est bien ce que vous voulez : utiliser Excel et pas comprendre comment il a été programmé !

Certaines propriétés sont accessibles en lecture seulement. D’autres en lecture et en écriture.

Vous utiliserez les premières pour connaitre la valeur de telle ou telle propriété. Vous utiliserez les secondes pour connaitre, mais aussi pour modifier les propriétés de l’objet.

Pour sélectionner l’objet ou les objets sur lesquels vous voulez lire ou modifier une propriété, vous utiliserez des méthodes. Par exemple, la méthode Workbooks(« classeur1.xlsm ») sélectionne le classeur classeur1.xlsm. Ou encore, la méthode Range(B3-B5) sélectionne la plage de cellules B3 à B5.

Pour utiliser une méthode, vous devez la plupart du temps faire référence à son parent. Par exemple, vous ne pouvez pas utiliser la méthode Range() sans préciser la feuille de calcul concernée. De même, vous ne pouvez pas utiliser la méthode Worksheets() sans préciser le classeur concerné. Par contre, lorsque vous sélectionnez un classeur, il n’est pas nécessaire d’indiquer son parent Application : cette liaison est implicite. La liaison parent-enfant se fait à l’aide d’une simple énumération, de la gauche vers la droite, de l’ancêtre le plus éloigné jusqu’à l’objet que vous voulez utiliser. L’expression se termine généralement par une propriété à laquelle vous affectez une valeur ou dont vous voulez connaitre la valeur.

Par exemple, dans cette expression :

Workbooks("classeur1.xlsm").Worksheets("Feuil1").Range("C8") = "J’ai tout compris"
  • Workbooks(« classeur1.xlsm ») sélectionne le classeur classeur1.xlsm ;
  • Worksheets(« Feuil1 ») sélectionne la feuille Feuil1 dans le classeur classeur1.xlsm ;
  • Range(« C8 ») sélectionne la cellule C8 dans la feuille Feuil1 du classeur classeur1.slsm ;
  • = « J’ai tout compris » affecte une valeur texte à la cellule C8 de la feuille Feuil1 du classeur classeur1.slsm.

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

Le tout est de connaitre :

  • la hiérarchie des objets pour pouvoir les interconnecter dans une expression « à points » ;
  • les méthodes et les propriétés des différents objets.

Les articles à venir vont progressivement vous familiariser avec les objets et les propriétés utilisables en VBA Excel, jusqu’à ce que vous vous sentiez « chez vous ». L’objectif sera alors atteint et il ne vous restera plus qu’à manipuler toutes ces entités pour faire des merveilles dans votre tableur !

La propriété Value d’un objet Range

Pour bien comprendre les mécanismes de base du VBA, raisonnons sur un exemple pratique.

La propriété Value d’un objet Range permet d’accéder en lecture et en écriture aux cellules concernées. Par exemple, l’instruction suivante affecte la valeur numérique 35 à la cellule C8 de la première feuille de calcul du classeur Classeur1.xlsm :

Workbooks("Classeur1.xlsm").Worksheets(1).Range("C8").Value = 35

La méthode Range() permet d’accéder à une cellule ou à une plage de cellules. Par exemple, pour affecter la valeur texte « bonjour » à la plage de cellules A1-C3, vous utiliserez cette instruction :

Workbooks("Classeur1.xlsm").Worksheets(1).Range("A1 :C3").Value = "bonjour"

Vous voulez savoir ce qui se trouve dans la cellule A2 ? Utilisez cette instruction :

Dim texte As String

texte = Workbooks("Classeur1.xlsm").Worksheets(1).Range("A2").Value

Vous pourrez par la suite affecter à une autre cellule la valeur lue dans la cellule A2. Par exemple, à la cellule A3 :

Workbooks("Classeur1.xlsm").Worksheets(1).Range("A3").Value = texte

Si vous avez déjà côtoyé un langage de programmation objet, vous aurez certainement compris en lisant l’article précédent que les éléments manipulés dans Excel sont des objets.

Les principaux objets Excel

Si vous n’avez jamais approché de près ou de loin un langage objet, vous n’avez certainement aucune idée de ce qu’est un langage objet, ni comment le fait que le VBA soit un langage objet va impacter votre programmation. Eh bien, disons qu’Excel consiste en un ensemble de briques que nous appellerons « objets ». Par exemple, les classeurs, les feuilles de calcul, les plages et les cellules sont des objets Excel. Allons un peu plus loin :

  • L’application Excel est un objet Application.
  • Le classeur en cours est un objet Workbook.
  • Les feuilles de calcul du classeur en cours sont des objets Worksheet.
  • Une plage de cellule dans une feuille de calcul est un objet Range.
  • Une cellule est un objet Cell.

Prenez une grande inspiration !

Les objets Excel appartiennent souvent à des « collections ». Par exemple :

Dans l’application Excel (objet Application), un ou plusieurs classeurs peuvent être ouverts. Ces classeurs constituent la collection Workbooks, c’est-à-dire l’ensemble des classeurs (objets Workbook) ouverts.

Un classeur (un Workbook) peut contenir une ou plusieurs feuilles de calcul. Ces feuilles de calcul constituent la collection Worksheets, c’est-à-dire l’ensemble des feuilles de calcul (les objets Worksheet) du classeur.

Une feuille de calcul contient un ensemble de cellules accessibles via des objets Range.

Les objets d’Excel – Quelques exemples pour bien comprendre

Un peu de pratique pour bien comprendre ce qui se passe.

Supposons que deux classeurs soient ouverts dans Excel. Le premier classeur s’appelle classeur1.xlsm. Il contient trois feuilles de calcul nommées feuille1, feuille2 et feuille3. Le deuxième classeur s’appelle classeur2.xlsm. Il contient une feuille de calcul nommée principal.

Pour accéder à la feuille feuille2 du classeur classeur1.xlsm, vous pouvez utiliser cette syntaxe :

Application.Workbooks("classeur1.xlsm").Worksheets("feuille2")

Ou plus simplement (l’objet Application étant implicite) :

Workbooks("classeur1.xlsm").Worksheets("feuille2")

Pour faire référence à un objet dans une collection, vous pouvez utiliser son nom, comme dans l’exemple précédent, mais également sa position dans la collection.

Par exemple, pour accéder à la feuille feuille2 (qui occupe la deuxième position dans la collection Worksheets) du classeur classeur1.xlsx, vous pouvez utiliser cette syntaxe :

Application.Workbooks("classeur1.xlsm").Worksheets(2)

Ou plus simplement :

Workbooks("classeur1.xlsm").Worksheets(2)

Voyons si vous avez compris.

Quelle instruction permet d’accéder à la cellule C8 dans la première feuille de calcul de nom Feuil1 du classeur Classeur1.xlsm ?

Deux syntaxes sont possibles :

Workbooks("classeur1.xlsm").Worksheets("Feuil1").Range("C8")

Workbooks("classeur1.xlsm").Worksheets(1).Range("C8")

Dans Excel, il est courant de manipuler des cellules, des plages de cellules, des feuilles de calcul et des classeurs. Dans cet article, vous allez découvrir les instructions utilisables pour les manipuler. Il ne s’agit que d’une introduction : vous irez beaucoup plus loin dans les articles à venir.

Les instructions suivantes permettent de sélectionner des feuilles, cellules, plages nommées, lignes et colonnes :

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

Range("A3").Select 'Sélection de la cellule A3

Range("B2:F6").Select 'Sélection du bloc de cellules B2 à F6

Range("B2,F6").Select 'Sélection des cellules B2 et F6

Range("unePlage").Select 'Sélection de la pahe nommée unePlage

Cells(4,2).select 'Sélection de la cellule à l'intersection de la ligne 4 et de la colonne 2

Range("3:5").Select 'Sélection des lignes 3 à 5

Rows("3:5").Select 'Identique à l'instruction précédente

Range("C:L").Select 'Sélection des colonnes C à L

Columns("C:L").Select 'Identique à l'instruction précédente

Un tableau est une variable qui peut contenir plusieurs valeurs. Les différentes valeurs mémorisées sont accédées par leur indice.

Pour déclarer un tableau, vous utiliserez le mot-clé Dim. Ici par exemple, nous définissons un tableau d’entiers T qui peut contenir 26 valeurs, accessibles par les indices 0 à 25 :

Dim T(25) As Integer

Remarque

Si vous avez du mal avec l’indice des tableaux qui commence à 0, vous pouvez déclarer l’instruction suivante au début du module pour que l’indice du premier élément de tous les tableaux définis dans le module soit toujours 1 :

Option Base 1

Pour affecter la valeur 5 à la première case du tableau, vous utiliserez cette instruction :

T(0) = 5

Pour afficher dans la cellule C2 le contenu de la première case du tableau, vous utiliserez cette instruction :

Cells(2,3) = T(0)

La cellule C2 affichera donc la valeur 5.

Définition et remplissage d’un tableau

Vous pouvez définir le contenu des cases d’un tableau avec la fonction Array(). Pour pouvoir utiliser cette fonction, le tableau doit avoir au préalable été déclaré de type Variant.

Par exemple, pour définir le tableau Prenom et lui affecter les trois prénoms suivants : Lio, Eric et Kévin, vous utiliserez les instructions suivantes :

Dim Prenom As Variant

Prenom = Array("Lio", "Eric", "Kévin")

Redimensionnement d’un tableau

Il est toujours possible de redimensionner un tableau en utilisant le mot-clé Redim.

Supposons que nous voulions ajouter une case au tableau Prenom précédent. Ce tableau comporte 3 cases. L’instruction à utiliser sera donc la suivante :

Redim Prenom(4)

Par défaut, le redimensionnement d’un tableau entraine la perte des données qui y étaient stockées. Pour éviter de perdre les trois prénoms du tableau Prenom, vous utiliserez le mot-clé Preserve :

Redim Preserve Prenom(3)

Vous pourrez alors ajouter un quatrième prénom au tableau :

Prenom(3) = "Michel"

Affichez les quatre prénoms pour vous assurer que le redimensionnement n’a rien écrasé :

Cells(1,1) = Prenom(0)

Cells(1,2) = Prenom(1)

Cells(1,3) = Prenom(2)

Cells(1,4) = Prenom(3)

Voici le résultat :

Tableaux multidimensionnels

Pour en terminer avec les tableaux, sachez qu’il est possible de définir des tableaux multidimensionnels. Pour cela, il suffit de définir les indices maximaux de chaque dimension lors de la définition du tableau. Ici par exemple, nous définissons un tableau à 3 dimensions :

Dim T(2,3,5) As Integer

Ce tableau contient 2*3*5 cases, soit 30 cases. Par exemple, pour affecter la valeur 12 à la première case du tableau, vous utiliserez cette instruction :

T(0,0,0) = 12

Les variables sont généralement déclarées avec le mot-clé Dim. Cependant, dans certains cas, il peut être utile de les déclarer avec le mot-clé Static.

Lorsqu’une variable est déclarée avec le mot-clé Static dans une procédure ou une fonction, sa valeur est conservée à la fin de la procédure ou la fonction. Lorsque la procédure ou la fonction est à nouveau exécutée, la variable Static reprend la valeur qui avait été mémorisée.

Voici un exemple de code pour mieux comprendre le fonctionnement des variables Static :

Sub Incremente()

  Static i As Integer

  i = i + 1

  Cells(i, 2) = i

End Sub

Ici, la variable Static i de type Integer est définie dans la procédure Incremente().

Lors de la première exécution de la procédure :

  • La variable i vaut 0.
  • La deuxième instruction incrémente la variable i. Elle vaut donc 1.
  • La troisième instruction affiche la valeur de i dans la cellule à l’intersection de la ligne i et de la deuxième colonne. La cellule B1 contient donc la valeur 1.

Lors de la deuxième exécution de la procédure :

  • Comme la variable i est statique, elle conserve la valeur qu’elle lors de la première exécution : 1.
  • La deuxième instruction l’incrémente. Elle vaut donc 2.
  • La troisième instruction affiche la valeur de i (2) dans la cellule à l’intersection de la ligne i (2) et de la deuxième colonne. La valeur 2 apparait donc dans la cellule B2.

Ainsi de suite. Ici, le code a été exécuté 6 fois. Voici le résultat :