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 la pleine fonctionnalité et la puissance d'une application VB complète, VBA offre aux utilisateurs de bureau la flexibilité d'intégrer les produits de bureau et d'automatiser le travail que vous y faites.

L'un des outils les plus puissants disponibles dans VBA est la possibilité de charger une gamme complète de données en une seule variable appelée un tableau. En chargeant des données de cette manière, vous pouvez manipuler ou effectuer des calculs sur cette gamme de données de diverses manières.

Vous pouvez mettre des données dans chaque section de cette «boîte» dans n'importe quel ordre que vous aimez.

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 écririez la ligne suivante.

Dim ArrmyArray (1 à 6) comme chaîne

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

ArrmyArray (1) = "Ryan Dube"

Vous pouvez créer un tableau bidimensionnel en utilisant la ligne suivante.

Dim Arrmyarray (1 à 6,1 à 2) en tant qu'entier

Le premier numéro représente la ligne, et la seconde est la colonne. Ainsi, le tableau ci-dessus pourrait contenir une portée avec 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 tout type de données en tant que variable régulière, telles que les chaînes, le booléen, les entiers, les flotteurs et plus.

Le nombre à l'intérieur de la parenthèse peut également être une variable. Les programmeurs utilisent généralement une boucle pour compter à travers toutes les sections d'un tableau et de chargement des cellules de données dans la feuille de calcul dans le tableau. Vous verrez comment faire cela plus tard dans cet article.

Comment programmer un tableau VBA dans Excel

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

À titre d'exemple, examinons une feuille de calcul de vente de produits, où vous souhaitez retirer le nom, l'article et la vente totale du représentant de la vente de la feuille de calcul.

Notez que dans VBA lorsque vous référez les lignes ou les colonnes, vous comptez les lignes et les colonnes à partir du haut à gauche en 1. Ainsi, la colonne Rep est 3, la colonne d'article 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 à 11, 1 à 3) comme chaîne
Dim i as Integer, J as Integer

Pour i = 2 à 12
Pour j = 1 à 3
ArrmyArray (i-1, j) = cellules (i, j).Valeur
Suivant J
Ensuite je

Vous remarquerez que pour sauter la ligne d'en-tête, le numéro de ligne dans le premier pour le look doit commencer à 2 plutôt que 1. Cela signifie que vous devez soustraire 1 pour la valeur de la ligne du tableau lorsque vous chargez la valeur cellulaire dans le tableau à l'aide de cellules (i, j).Valeur.

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 des contrôles du ruban.

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

Modifiez les commandes Choisissez de Dropdown à Toutes les commandes. Sélectionner Développeur dans le menu de gauche et sélectionnez le Ajouter bouton pour le déplacer vers le volet à droite. Sélectionnez la case à cocher pour l'activer et sélectionner D'ACCORD pour finir.

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

Cette fonction s'exécutera chaque fois que le fichier de feuille de calcul est ouvert. Collez le code dans le volet de script à l'intérieur de ce sous-programme.

Ce script fonctionnera à travers 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 terminées, le tableau bidimensionnel ArrmyArray contient toutes les données que vous avez spécifiées à partir de la feuille d'origine.

Manipuler les tableaux dans Excel VBA

Disons que vous souhaitez appliquer une taxe de vente de 5% à tous les prix de vente finale, puis rédiger toutes les données à une nouvelle feuille.

Vous pouvez le faire en ajoutant un autre pour Loop après le premier, avec une commande pour écrire les résultats sur une nouvelle feuille.

Pour k = 2 à 12
Feuilles ("sheet2").Cellules (k, 1).Valeur = ArrmyArray (K - 1, 1)
Feuilles ("sheet2").Cellules (k, 2).Valeur = ArrmyArray (K - 1, 2)
Feuilles ("sheet2").Cellules (k, 3).Valeur = ArrmyArray (K - 1, 3)
Feuilles ("sheet2").Cellules (K, 4).Valeur = ArrmyArray (K - 1, 3) * 0.05
Suivant K

Cela «déchargera» l'ensemble du tableau en feuille 2, avec une ligne supplémentaire contenant le total multiplié de 5% pour le montant de l'impôt.

La feuille résultante ressemblera à ceci.

Comme vous pouvez le voir, les tableaux VBA dans Excel sont extrêmement utiles et aussi polyvalents que tout autre truc Excel. 

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

Si vous souhaitez obtenir une réelle créativité, vous pouvez même créer deux tableaux contenant une gamme 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.