VBA уже много лет является частью пакета Microsoft Office . Хотя он не обладает полной функциональностью и мощью полноценного приложения VB, VBA предоставляет пользователям Office гибкость для интеграции продуктов Office и автоматизации работы, которую вы в них выполняете.

Одним из самых мощных инструментов, доступных в VBA , является возможность загрузки всего диапазона данных в одну переменную, называемую массивом. Загружая данные таким образом, вы можете манипулировать этим диапазоном данных или выполнять вычисления различными способами.

Так что же такое массив VBA ? В этой статье мы ответим на этот вопрос и покажем вам, как использовать его в вашем собственном скрипте VBA^{(your own VBA script)} .

Что такое массив VBA?

Использовать массив VBA в ^(VBA)Excel очень просто, но понимание концепции массивов может быть немного сложным, если вы никогда их не использовали.

Думайте^(Think) о массиве как о коробке с секциями внутри. Одномерный массив представляет собой коробку с одной строкой секций. Двумерный массив представляет собой коробку с двумя строками секций.

Вы можете помещать данные в каждый раздел этой «коробки» в любом порядке.

В начале вашего скрипта VBA вам нужно определить это «ящик», определив свой массив VBA . Таким образом, чтобы создать массив, который может содержать один набор данных (одномерный массив), вы должны написать следующую строку.

Dim arrMyArray(1 To 6) As String

Позже в вашей программе вы можете поместить данные в любой раздел этого массива, сославшись на раздел внутри круглых скобок.

arrMyArray(1) = "Ryan Dube"

Вы можете создать двумерный массив, используя следующую строку.

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

Первое число представляет строку, а второе — столбец. Таким образом, приведенный выше массив может содержать диапазон с 6 строками и 2 столбцами.

Вы можете загрузить любой элемент этого массива данными следующим образом.

arrMyArray(1,2) = 3

Это загрузит 3 в ячейку B1.

Массив может содержать любой тип данных в виде обычной переменной, например строки, логические значения, целые числа, числа с плавающей запятой и многое другое.

Число в скобках также может быть переменной. Программисты обычно используют цикл^(Loop) For для подсчета всех разделов массива и загрузки ячеек данных электронной таблицы в массив. Позже в этой статье вы увидите, как это сделать.

Как запрограммировать массив VBA в Excel

Давайте рассмотрим простую программу, в которой вы можете захотеть загрузить информацию из электронной таблицы в многомерный массив.

В качестве примера давайте рассмотрим электронную таблицу продаж продуктов, где вы хотите получить имя торгового представителя, товар и общую сумму продаж из электронной таблицы.

Обратите внимание, что в VBA , когда вы ссылаетесь на строки или столбцы, вы считаете строки и столбцы^{(rows and columns)} , начиная с верхнего левого угла с 1. Таким образом, столбец повторов равен 3, столбец элементов равен 4, а итоговый столбец равен 7.

Чтобы загрузить эти три столбца во все 11 строк, вам нужно написать следующий скрипт.

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

Вы заметите, что для того, чтобы пропустить строку заголовка, номер строки в первом поиске For должен начинаться с 2, а не с 1. Это означает, что вам нужно вычесть 1 из значения строки массива при загрузке значения ячейки. в массив с помощью Cells (i, j).Value.

Куда вставить скрипт массива VBA^{(Your VBA Array Script)}

Чтобы поместить программный скрипт VBA в ^(VBA)Excel , вам нужно использовать редактор VBA . Вы можете получить к нему доступ, выбрав меню « Разработчик^(Developer) » и выбрав « Просмотр кода»^{(View Code)} в разделе « Элементы управления^(Controls) » на ленте.

Если вы не видите « Разработчик^(Developer) » в меню, вам нужно добавить его. Для этого выберите « Файл»^(File) и « Параметры^(Options) » , чтобы открыть окно «Параметры Excel».

Измените команды выбора из раскрывающегося списка на Все команды^{(All Commands)} . Выберите « Разработчик^(Developer) » в меню слева и нажмите кнопку « Добавить^(Add) », чтобы переместить его на панель справа. Установите флажок, чтобы включить его, и нажмите OK , чтобы закончить.

Когда откроется окно редактора кода^{(Code Editor)} , убедитесь, что лист, в котором находятся ваши данные, выбран на левой панели. Выберите « Рабочий лист»^(Worksheet) в раскрывающемся списке слева и « Активировать»^(Activate) справа. Это создаст новую подпрограмму с именем Worksheet_Activate ().

Эта функция будет выполняться всякий раз, когда файл электронной таблицы открыт. Вставьте код в панель сценария внутри этой подпрограммы.

Этот скрипт будет работать с 12 строками и загрузит имя представителя из столбца 3, товар из столбца 4 и общую сумму продаж из столбца 7.

После завершения обоих циклов For двумерный массив arrMyArray содержит все указанные вами данные из исходного листа.

Работа с массивами в Excel VBA

Допустим, вы хотите применить налог с продаж в размере 5% ко всем конечным ценам продажи, а затем записать все данные на новый лист.

Вы можете сделать это, добавив еще один цикл For после первого с командой для записи результатов на новый лист.

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

Это «выгрузит» весь массив в Sheet2 с дополнительной строкой, содержащей общую сумму, умноженную на 5% для суммы налога.

Полученный лист будет выглядеть так.

Как видите, массивы VBA в ^(VBA)Excel чрезвычайно полезны и столь же универсальны, как и любой другой прием Excel^{(any other Excel trick)} . 

В приведенном выше примере показано очень простое использование массива. Вы можете создавать гораздо большие массивы и выполнять сортировку, усреднение или множество других функций с данными, которые вы в них храните.

Если вы хотите проявить настоящий творческий подход, вы можете даже создать два массива , содержащих диапазон ячеек^{(containing a range of cells)} из двух разных листов, и выполнять вычисления между элементами каждого массива.

Приложения ограничены только вашей фантазией.

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

VBA has been part of the Microsoft Office suite for many yeаrs. While it doesn’t have the full functionality and power of a full VB aрplication, VBA provides Office users with the flexibility to integrate Office products аnd 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

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• Как вставить лист Excel в документ Word

• Как использовать анализ «что, если» в Excel

• Как исправить строку в Excel

• Быстрое написание чисел в Word и Excel

• Как рассчитать Z-показатель в Excel

• Как объединить данные в нескольких файлах Excel

• Почему вы должны использовать именованные диапазоны в Excel

• Как сделать круговую диаграмму в Excel

• Как сортировать по дате в Excel

• Используйте имена динамических диапазонов в Excel для гибких раскрывающихся списков

• Как написать формулу/оператор ЕСЛИ в Excel

• 10 советов и рекомендаций по Excel на 2019 год

• 4 способа конвертировать Excel в Google Таблицы

• Как исправить ошибки #N/A в формулах Excel, таких как ВПР

• Как отследить зависимых в Excel

• Как объединить ячейки, столбцы и строки в Excel

• Базовая сортировка данных в один столбец и несколько столбцов в электронных таблицах Excel

• Как поделиться файлом Excel для удобной совместной работы