Qu’est-ce qu’un tableau VBA dans Excel et comment en programmer un

VBA fait partie de la suite Microsoft Office depuis de nombreuses années. Bien qu’il n’ait pas toutes les fonctionnalités et la puissance d’une application VB complète, VBA offre aux utilisateurs d’Office la flexibilité nécessaire pour intégrer les produits Office et automatiser le travail que vous y faites.

L’un des outils les plus puissants disponibles dans VBA est la possibilité de charger une plage entière de données dans une seule variable appelée tableau. En chargeant des données de cette manière, vous pouvez manipuler ou effectuer des calculs sur cette plage de données de différentes manières.

Alors, qu’est-ce qu’un tableau VBA? Dans cet article, nous répondrons à cette question et vous montrerons comment en utiliser un dans votre propre script VBA.

Qu’est-ce qu’une baie VBA?

L’utilisation d’un tableau VBA dans Excel est très simple, mais comprendre le concept de tableaux peut être un peu complexe si vous n’en avez jamais utilisé.

Pensez à un tableau comme une boîte avec des sections à l’intérieur. Un tableau unidimensionnel est une boîte avec une ligne de sections. Un tableau à deux dimensions est une boîte avec deux lignes de sections.

Vous pouvez mettre des données dans chaque section de cette «boîte» dans l’ordre de votre choix.

Au début de votre script VBA, vous devez définir cette «boîte» en définissant votre tableau VBA. Donc, pour créer un tableau qui peut contenir un ensemble de données (un tableau à une dimension), vous écrirez la ligne suivante.

Dim arrMyArray(1 To 6) As String

Plus tard dans votre programme, vous pouvez placer des données dans n’importe quelle section de ce tableau en référençant la section à l’intérieur des parenthèses.

arrMyArray(1) = "Ryan Dube"

Vous pouvez créer un tableau à deux dimensions en utilisant la ligne suivante.

Dim arrMyArray(1 To 6,1 to 2) As Integer

Le premier chiffre représente la ligne et le second la colonne. Ainsi, le tableau ci-dessus pourrait contenir une plage de 6 lignes et 2 colonnes.

Vous pouvez charger n’importe quel élément de ce tableau avec des données comme suit.

arrMyArray(1,2) = 3

Cela chargera un 3 dans la cellule B1.

Un tableau peut contenir n’importe quel type de données sous forme de variable régulière, comme des chaînes, des valeurs booléennes, des entiers, des flottants, etc.

Le nombre entre parenthèses peut également être une variable. Les programmeurs utilisent généralement une boucle For pour compter toutes les sections d’un tableau et charger les cellules de données de la feuille de calcul dans le tableau. Vous verrez comment faire cela plus loin dans cet article.

Comment programmer un tableau VBA dans Excel

Jetons un coup d’œil à un programme simple dans lequel vous souhaiterez peut-être charger des informations d’une feuille de calcul dans un tableau multidimensionnel.

À titre d’exemple, examinons une feuille de calcul des ventes de produits, dans laquelle vous souhaitez extraire le nom, l’article et la vente totale du représentant des ventes de la feuille de calcul.

Notez que dans VBA, lorsque vous référencez des lignes ou des colonnes, vous comptez les lignes et les colonnes en commençant par le coin supérieur gauche à 1. La colonne représentant est donc 3, la colonne d’élément est 4 et la colonne totale est 7.

Pour charger ces trois colonnes sur les 11 lignes, vous devrez écrire le script suivant.

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
For j = 1 To 3
arrMyArray(i-1, j) = Cells(i, j).Value
Next j
Next i

Vous remarquerez que pour ignorer la ligne d’en-tête, le numéro de ligne du premier aspect For doit commencer à 2 plutôt qu’à 1. Cela signifie que vous devez soustraire 1 pour la valeur de ligne du tableau lorsque vous chargez la valeur de la cellule dans le tableau en utilisant Cells (i, j) .Value.

Où insérer votre script de tableau VBA

Pour placer le programme d’un script VBA dans Excel, vous devez utiliser l’éditeur VBA. Vous pouvez y accéder en sélectionnant le Développeur menu et sélection Afficher le code dans la section Contrôles du ruban.

Si vous ne voyez pas le développeur dans le menu, vous devrez l’ajouter. Pour ce faire, sélectionnez Fichier et Options pour ouvrir la fenêtre Options Excel.

Modifiez les commandes de sélection de la liste déroulante à Toutes les commandes. Sélectionner Développeur dans le menu de gauche et sélectionnez le Ajouter pour le déplacer vers le volet de droite. Cochez la case pour l’activer et sélectionnez D’accord pour finir.

Lorsque la fenêtre de l’éditeur de code s’ouvre, assurez-vous que la feuille dans laquelle se trouvent vos données est sélectionnée dans le volet gauche. Sélectionner Feuille de travail dans le menu déroulant de gauche et Activer dans le droit. Cela créera un nouveau sous-programme appelé Worksheet_Activate ().

Cette fonction s’exécutera à chaque ouverture du fichier de feuille de calcul. Collez le code dans le volet de script à l’intérieur de ce sous-programme.

Ce script fonctionnera sur les 12 lignes et chargera le nom du représentant de la colonne 3, l’article de la colonne 4 et la vente totale de la colonne 7.

Une fois les deux boucles For terminées, le tableau bidimensionnel arrMyArray contient toutes les données que vous avez spécifiées à partir de la feuille d’origine.

Manipulation de tableaux dans Excel VBA

Supposons que vous souhaitiez appliquer une taxe de vente de 5% à tous les prix de vente finaux, puis écrire toutes les données sur une nouvelle feuille.

Vous pouvez le faire en ajoutant une autre boucle For après la première, avec une commande pour écrire les résultats dans une nouvelle feuille.

For k = 2 To 12
Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

Cela «déchargera» l’ensemble du tableau dans Sheet2, avec une ligne supplémentaire contenant le total multiplié par 5% pour le montant de la taxe.

La feuille résultante ressemblera à ceci.

Comme vous pouvez le voir, les tableaux VBA dans Excel sont extrêmement utiles et aussi polyvalents que n’importe quelle autre astuce Excel.

L’exemple ci-dessus est une utilisation très simple pour un tableau. Vous pouvez créer des tableaux beaucoup plus volumineux et effectuer un tri, un calcul de moyenne ou de nombreuses autres fonctions par rapport aux données que vous y stockez.

Si vous voulez faire preuve de créativité, vous pouvez même créer deux tableaux contenant une plage de cellules à partir de deux feuilles différentes et effectuer des calculs entre les éléments de chaque tableau.

Les applications ne sont limitées que par votre imagination.