VBA jest częścią pakietu Microsoft Office^{(Microsoft Office)} od wielu lat. Chociaż nie ma pełnej funkcjonalności i mocy pełnej aplikacji VB, VBA zapewnia użytkownikom pakietu Office^(Office) elastyczność integracji produktów pakietu Office^(Office) i automatyzacji pracy, którą w nich wykonujesz.

Jednym z najpotężniejszych narzędzi dostępnych w VBA jest możliwość załadowania całego zakresu danych do jednej zmiennej zwanej tablicą. Ładując dane w ten sposób, można na różne sposoby manipulować lub wykonywać obliczenia na tym zakresie danych.

Czym więc jest tablica VBA ? W tym artykule odpowiemy na to pytanie i pokażemy, jak używać go we własnym skrypcie VBA^{(your own VBA script)} .

Co to jest macierz VBA?

Korzystanie z tablicy VBA w programie ^(VBA)Excel jest bardzo proste, ale zrozumienie koncepcji tablic może być nieco skomplikowane, jeśli nigdy jej nie używałeś.

Pomyśl^(Think) o tablicy jak o pudełku z sekcjami wewnątrz. Tablica jednowymiarowa to pudełko z jedną linią przekrojów. Tablica dwuwymiarowa to pudełko z dwoma liniami sekcji.

Możesz umieścić dane w każdej sekcji tego „pudełka” w dowolnej kolejności.

Na początku skryptu VBA musisz zdefiniować to „pudełko”, definiując tablicę VBA . Tak więc, aby utworzyć tablicę, która może przechowywać jeden zestaw danych (tablicę jednowymiarową), należy napisać następujący wiersz.

Dim arrMyArray(1 To 6) As String

Później w programie możesz umieścić dane w dowolnej sekcji tej tablicy, odwołując się do sekcji wewnątrz nawiasów.

arrMyArray(1) = "Ryan Dube"

Możesz utworzyć dwuwymiarową tablicę za pomocą następującego wiersza.

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

Pierwsza liczba reprezentuje wiersz, a druga to kolumna. Tak więc powyższa tablica może zawierać zakres z 6 wierszami i 2 kolumnami.

Możesz załadować dowolny element tej tablicy danymi w następujący sposób.

arrMyArray(1,2) = 3

To załaduje 3 do komórki B1.

Tablica może przechowywać dowolny typ danych jako zwykłą zmienną, taką jak ciągi, wartości logiczne, liczby całkowite, zmiennoprzecinkowe i inne.

Liczba w nawiasie również może być zmienną. Programiści często używają pętli^(Loop) For do zliczania wszystkich sekcji tablicy i ładowania komórek danych z arkusza kalkulacyjnego do tablicy. Zobaczysz, jak to zrobić w dalszej części tego artykułu.

Jak zaprogramować macierz VBA w programie Excel

Przyjrzyjmy się prostemu programowi, w którym możesz chcieć załadować informacje z arkusza kalkulacyjnego do wielowymiarowej tablicy.

Jako przykład przyjrzyjmy się arkuszowi kalkulacyjnemu sprzedaży produktów, w którym chcesz wyciągnąć z niego imię i nazwisko przedstawiciela handlowego, przedmiot i całkowitą sprzedaż.

Zwróć uwagę, że w VBA , gdy odwołujesz się do wierszy lub kolumn, liczysz wiersze i kolumny,^{(rows and columns)} zaczynając od lewego górnego rogu od 1. Tak więc kolumna rep to 3, kolumna pozycji to 4, a całkowita kolumna to 7.

Aby załadować te trzy kolumny we wszystkich 11 wierszach, musisz napisać następujący skrypt.

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

Zauważysz, że aby pominąć wiersz nagłówka, numer wiersza w pierwszym wyglądzie For musi zaczynać się od 2 zamiast 1. Oznacza to, że musisz odjąć 1 dla wartości wiersza tablicy podczas ładowania wartości komórki do tablicy za pomocą Cells (i, j).Value.

Gdzie wstawić skrypt VBA Array^{(Your VBA Array Script)}

Aby umieścić program skryptu VBA w ^(VBA)Excelu^(Excel) , musisz użyć edytora VBA . Możesz uzyskać do niego dostęp, wybierając menu Deweloper^(Developer) i wybierając opcję Wyświetl kod^{(View Code)} w sekcji Kontrolki^(Controls) na wstążce.

Jeśli nie widzisz Dewelopera^(Developer) w menu, musisz go dodać. Aby to zrobić, wybierz Plik^(File) i Opcje^(Options) , aby otworzyć okno Opcje programu Excel.

Zmień polecenia wyboru z listy rozwijanej na Wszystkie polecenia^{(All Commands)} . Wybierz Deweloper^(Developer) z lewego menu i wybierz przycisk Dodaj^(Add) , aby przenieść go do okienka po prawej stronie. Zaznacz pole wyboru, aby je włączyć, i wybierz OK , aby zakończyć.

Po otwarciu okna Edytor kodu^{(Code Editor)} upewnij się, że arkusz, w którym znajdują się Twoje dane, jest zaznaczony w lewym okienku. Wybierz Arkusz roboczy^(Worksheet) w lewym menu rozwijanym i Aktywuj^(Activate) po prawej stronie. Spowoduje to utworzenie nowego podprogramu o nazwie Worksheet_Activate ().

Ta funkcja będzie działać po każdym otwarciu pliku arkusza kalkulacyjnego. Wklej kod do okienka skryptu wewnątrz tego podprogramu.

Ten skrypt będzie działał przez 12 wierszy i załaduje nazwę przedstawiciela z kolumny 3, przedmiot z kolumny 4 i całkowitą sprzedaż z kolumny 7.

Po zakończeniu obu pętli For dwuwymiarowa tablica arrMyArray zawiera wszystkie określone dane z oryginalnego arkusza.

Manipulowanie tablicami w Excel VBA

Załóżmy, że chcesz zastosować 5% podatek od sprzedaży do wszystkich ostatecznych cen sprzedaży, a następnie zapisać wszystkie dane w nowym arkuszu.

Możesz to zrobić, dodając kolejną pętlę For po pierwszej z poleceniem zapisania wyników w nowym arkuszu.

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

Spowoduje to „wyładowanie” całej tablicy do arkusza 2^(Sheet2) , z dodatkowym wierszem zawierającym sumę pomnożoną przez 5% dla kwoty podatku.

Wynikowy arkusz będzie wyglądał tak.

Jak widać, tablice VBA w ^(VBA)Excelu^(Excel) są niezwykle przydatne i tak wszechstronne, jak każda inna sztuczka Excela^{(any other Excel trick)} . 

Powyższy przykład jest bardzo prostym zastosowaniem tablicy. Możesz tworzyć znacznie większe tablice i wykonywać sortowanie, uśrednianie lub wiele innych funkcji na przechowywanych w nich danych.

Jeśli chcesz być naprawdę kreatywny, możesz nawet utworzyć dwie tablice zawierające zakres komórek^{(containing a range of cells)} z dwóch różnych arkuszy i wykonać obliczenia między elementami każdej tablicy.

Zastosowania ogranicza tylko Twoja wyobraźnia.

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

VBA has been part of the Microsoft Office suite for many years. While it doesn’t have the full fυnctionality and power of a full VB application, VBA provides Office users with the flexіbility to integrate Office 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

• Jak usunąć puste linie w Excelu?

• Jak korzystać z funkcji Speak Cells programu Excel

• Jak wstawić arkusz programu Excel do dokumentu programu Word?

• Jak korzystać z analizy warunkowej programu Excel

• Jak naprawić wiersz w programie Excel

• 3 sposoby na podzielenie komórki w Excelu

• 10 porad i wskazówek dotyczących programu Excel na rok 2019

• Jak korzystać z funkcji PMT w programie Excel?

• Korzystanie z narzędzia wyszukiwania celów analizy warunkowej w programie Excel

• Szybko przeliteruj liczby w Wordzie i Excelu

• Jak usunąć linie siatki w programie Excel

• Jak sortować według daty w programie Excel

• Jak udostępnić plik Excel w celu łatwej współpracy?

• Jak alfabetycznie w programie Excel

• Jak usunąć zduplikowane wiersze w programie Excel?

• 4 sposoby użycia znacznika wyboru w programie Excel

• Użyj klawiatury, aby zmienić wysokość wiersza i szerokość kolumny w programie Excel

• Jak otworzyć Word i Excel w trybie awaryjnym

• Jak utworzyć wiele połączonych list rozwijanych w programie Excel?

• Arkusze Google a Microsoft Excel – jakie są różnice?