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

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 les données de cette manière, vous pouvez manipuler ou effectuer des calculs sur cette plage de données de différentes manières.

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^{(your own VBA script)} .

Qu'est-ce qu'un tableau VBA ?

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

Pensez^(Think) à 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 pouvant contenir un ensemble de données (un tableau à une dimension), vous devez écrire 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 entre 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 peut 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 la forme d'une variable régulière, telle que des chaînes, des booléens, des entiers, des flottants, etc.

Le nombre entre parenthèses peut également être une variable. Les programmeurs utilisent couramment une boucle^(Loop) 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 procéder 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 à partir d'une feuille de calcul dans un tableau multidimensionnel.

À titre d'exemple, examinons une feuille de calcul de vente de produits, dans laquelle vous souhaitez extraire le nom, l'article et la vente totale du représentant commercial 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^{(rows and columns)} en partant du haut à gauche à 1. Ainsi, la colonne rep est 3, la colonne élément est 4 et la colonne total 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 dans le premier regard For doit commencer à 2 plutôt qu'à 1. Cela signifie que vous devez soustraire 1 pour la valeur de la 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^{(Your VBA Array Script)}

Pour placer programmer un script VBA dans ^(VBA)Excel , vous devez utiliser l' éditeur VBA . Vous pouvez y accéder en sélectionnant le menu Développeur et en sélectionnant ^(Developer)Afficher le code^{(View Code)} dans la section Contrôles^(Controls) du ruban.

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

Modifiez les commandes de choix de la liste déroulante à Toutes les commandes^{(All Commands)} . Sélectionnez Développeur^(Developer) dans le menu de gauche et sélectionnez le bouton Ajouter^(Add) pour le déplacer vers le volet de droite. Cochez la case pour l'activer et sélectionnez OK pour terminer.

Lorsque la fenêtre de l' éditeur de code^{(Code Editor)} s'ouvre, assurez-vous que la feuille dans laquelle se trouvent vos données est sélectionnée dans le volet de gauche. Sélectionnez Feuille^(Worksheet) de calcul dans le menu déroulant de gauche et Activer^(Activate) à droite. Cela créera une nouvelle sous-routine appelée Worksheet_Activate ().

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

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 à deux dimensions 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 sur 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 ^(VBA)Excel sont extrêmement utiles et aussi polyvalents que n'importe quel autre truc Excel^{(any other Excel trick)} . 

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 sur les données que vous y stockez.

Si vous voulez être vraiment créatif, vous pouvez même créer deux tableaux contenant une plage de cellules^{(containing a range of cells)} à 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.

What Is a VBA Array in Excel and How to Program One

VBA has been part of the Microsоft Office suite for many years. While it doesn’t have the full functionality and power of a full VB application, VBA proνideѕ Office users with the flexibility to integrate Office products and automate the work you do in them.

One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that range of data in a variety of ways.

So what is a VBA array? In this article we’ll answer that question, and show you how to use one in your own VBA script.

What Is a VBA Array?

Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a little bit complex if you’ve never used one.

Think of an array like a box with sections inside it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a box with two lines of sections.

You can put data into each section of this “box” in any order you like.

At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimension array), you would write the following line.

Dim arrMyArray(1 To 6) As String

Later in your program, you can place data into any section of this array by referencing the section inside of parentheses.

arrMyArray(1) = "Ryan Dube"

You can create a two-dimensional array using the following line.

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

The first number represents the row, and the second is the column. So the above array could hold a range with 6 rows and 2 columns.

You can load any element of this array with data as follows.

arrMyArray(1,2) = 3

This will load a 3 into the cell B1.

An array can hold any type of data as a regular variable, such as strings, boolean, integers, floats and more.

The number inside the parenthesis can also be a variable. Programmers commonly use a For Loop to count through all sections of an array and load data cells in the spreadsheet into the array. You’ll see how to do this later in this article.

How to Program a VBA Array in Excel

Let’s take a look at a simple program where you may want to load information from a spreadsheet into a multidimensional array.

As an example, let’s look at a product sales spreadsheet, where you want to pull the sales rep’s name, item, and total sale out of the spreadsheet.

Note that in VBA when you reference rows or columns, you count rows and columns starting from the top left at 1. So the rep column is 3, the item column is 4, and the total column is 7.

To load these three columns over all 11 rows, you’ll need to write the following script.

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

You’ll notice that in order to skip the header row, the row number in the first For look needs to start at 2 rather than 1. This means you need to subtract 1 for the array row value when you’re loading the cell value into the array using Cells(i, j).Value.

Where to Insert Your VBA Array Script

To place program a VBA script in Excel, you need to use the VBA editor. You can access this by selecting the Developer menu and selecting View Code in the Controls section of the ribbon.

If you don’t see the Developer in the menu, you’ll need to add it. To do this, select File and Options to open the Excel Options window.

Change the choose commands from dropdown to All Commands. Select Developer from the left menu and select the Add button to move it to the pane on the right. Select the checkbox to enable it and select OK to finish.

When the Code Editor window opens, make sure the sheet your data is in is selected in the left pane. Select Worksheet in the left dropdown and Activate in the right. This will create a new subroutine called Worksheet_Activate().

This function will run whenever the spreadsheet file is opened. Paste the code into the script pane inside this subroutine.

This script will work through the 12 rows and will load the rep name from column 3, the item from column 4, and the total sale from column 7.

Once both For loops are finished, the two dimensional array arrMyArray contains all of the data you specified from the original sheet.

Manipulating Arrays in Excel VBA

Let’s say you want to apply a 5% sales tax to all of the final sale prices, and then write all of the data to a new sheet.

You can do this by adding another For loop after the first, with a command to write the results to a new sheet.

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

This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.

The resulting sheet will look like this.

As you can see, VBA arrays in Excel are extremely useful and as versatile as any other Excel trick. 

The example above is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions against the data you store in them.

If you want to get real creative, you could even create two arrays containing a range of cells from two different sheets, and perform calculations between elements of each array.

The applications are limited only by your imagination.

Related posts

• Comment supprimer des lignes vides dans Excel

• Comment utiliser la fonctionnalité Speak Cells d'Excel

• Comment insérer une feuille de calcul Excel dans un document Word

• Comment utiliser l'analyse What-If d'Excel

• Comment réparer une ligne dans Excel

• Comment partager un fichier Excel pour une collaboration facile

• Comment effectuer une recherche dans Excel

• Comment faire un graphique à secteurs dans Excel

• Un guide VBA avancé pour MS Excel

• Comment créer une macro ou un script VBA dans Excel

• Comment utiliser les instructions If et If imbriquées dans Excel

• Comment créer plusieurs listes déroulantes liées dans Excel

• Comment fusionner des données dans plusieurs fichiers Excel

• 3 façons de diviser une cellule dans Excel

• Utilisez le clavier pour modifier la hauteur de ligne et la largeur de colonne dans Excel

• 10 trucs et astuces Excel pour 2019

• Pourquoi devriez-vous utiliser des plages nommées dans Excel

• Comment utiliser la fonction PMT dans Excel

• Comment utiliser le remplissage Flash dans Excel

• Comment trier par date dans Excel