VBA е част от пакета на Microsoft Office от много години. Въпреки че няма пълната функционалност и мощност на пълно VB приложение, VBA предоставя на потребителите на Office гъвкавостта да интегрират продуктите на Office и да автоматизират работата, която вършите в тях.

Един от най-мощните инструменти, налични във VBA , е възможността за зареждане на цял набор от данни в една променлива, наречена масив. Като зареждате данни по този начин, можете да манипулирате или извършвате изчисления върху този диапазон от данни по различни начини.

И така, какво е VBA масив? В тази статия ще отговорим на този въпрос и ще ви покажем как да използвате такъв във вашия собствен VBA скрипт^{(your own VBA script)} .

Какво е 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.

Масивът може да съдържа всякакъв тип данни като обикновена променлива, като низове, булеви, цели числа, плаващи числа и др.

Числото в скобите също може да бъде променлива. Програмистите обикновено използват For Loop , за да преброят всички секции на масива и да заредят клетки с данни в електронната таблица в масива. Ще видите как да направите това по-късно в тази статия.

Как да програмирам 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 look трябва да започне от 2, а не от 1. Това означава, че трябва да извадите 1 за стойността на реда на масива, когато зареждате стойността на клетката в масива с помощта на клетки^(Cells) (i, j). Стойност.

Къде да вмъкнете вашия VBA масив скрипт^{(Your VBA Array Script)}

За да поставите програма на 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 масивите в 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 beеn part of the Microsoft Office suite for many years. While it doesn’t have the full functionality and power of a full VB application, VBA provides Officе users with the flexibilitу to integrate Officе products and automate the work уou 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 Doc

• Как да използвате анализа какво-ако на Excel

• Как да коригирате ред в Excel

• Как бързо да вмъкнете няколко реда в Excel

• Как да търсите в Excel

• 5 начина за конвертиране на текст в числа в Excel

• Свързване на Excel към MySQL

• Как да изчислим Z-Score в Excel

• 3 начина за разделяне на клетка в Excel

• 4 начина да използвате отметка в Excel

• Как да използвате функцията PMT в Excel

• 2 начина за използване на функцията за транспониране на Excel

• Как да коригирате #N/A грешки във формули на Excel като VLOOKUP

• 4 начина да конвертирате Excel в Google Sheets

• Разлики между Microsoft Excel онлайн и Excel за настолен компютър

• Как да създадете VBA макрос или скрипт в Excel

• Как да използвате изявления If и вложени If в Excel

• Кога да използвате Index-Match вместо VLOOKUP в Excel