VBA je súčasťou balíka Microsoft Office už mnoho rokov. Aj keď VBA^(VBA) nemá plnú funkčnosť a výkon úplnej aplikácie VB, poskytuje používateľom balíka Office^(Office) flexibilitu na integráciu produktov balíka Office^(Office) a automatizáciu práce, ktorú v nich robíte.

Jedným z najvýkonnejších nástrojov dostupných vo VBA je možnosť načítať celý rad údajov do jednej premennej nazývanej pole. Načítaním údajov týmto spôsobom môžete manipulovať alebo vykonávať výpočty s týmto rozsahom údajov rôznymi spôsobmi.

Čo je teda pole VBA ? V tomto článku odpovieme na túto otázku a ukážeme vám, ako ju použiť vo vašom vlastnom skripte VBA^{(your own VBA script)} .

Čo je pole VBA?

Používanie poľa VBA v ^(VBA)Exceli^(Excel) je veľmi jednoduché, ale pochopenie konceptu polí môže byť trochu zložité, ak ste žiadne ešte nepoužívali.

Predstavte si^(Think) pole ako krabicu s časťami vo vnútri. Jednorozmerné pole je pole s jedným riadkom sekcií. Dvojrozmerné pole je krabica s dvoma riadkami sekcií.

Do každej sekcie tohto „boxu“ môžete vkladať údaje v ľubovoľnom poradí.

Na začiatku skriptu VBA musíte definovať tento „box“ definovaním poľa VBA . Ak teda chcete vytvoriť pole, ktoré môže obsahovať jednu množinu údajov (jednorozmerné pole), napíšte nasledujúci riadok.

Dim arrMyArray(1 To 6) As String

Neskôr vo svojom programe môžete umiestniť údaje do ľubovoľnej sekcie tohto poľa odkazom na sekciu v zátvorkách.

arrMyArray(1) = "Ryan Dube"

Pomocou nasledujúceho riadku môžete vytvoriť dvojrozmerné pole.

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

Prvé číslo predstavuje riadok a druhé je stĺpec. Takže vyššie uvedené pole môže obsahovať rozsah so 6 riadkami a 2 stĺpcami.

Každý prvok tohto poľa môžete načítať údajmi nasledovne.

arrMyArray(1,2) = 3

Tým sa nahrá 3 do bunky B1.

Pole môže obsahovať akýkoľvek typ údajov ako regulárnu premennú, ako sú reťazce, booleovské hodnoty, celé čísla, pohyblivé čísla a ďalšie.

Číslo v zátvorke môže byť tiež premenné. Programátori bežne používajú For Loop na počítanie cez všetky sekcie poľa a načítanie dátových buniek v tabuľke do poľa. Ako to urobiť, uvidíte neskôr v tomto článku.

Ako naprogramovať pole VBA v Exceli

Poďme sa pozrieť na jednoduchý program, v ktorom možno budete chcieť načítať informácie z tabuľky do viacrozmerného poľa.

Ako príklad sa pozrime na tabuľku predaja produktov, kde chcete z tabuľky vytiahnuť meno obchodného zástupcu, položku a celkový predaj.

Všimnite si, že keď vo VBA odkazujete na riadky alebo stĺpce, počítate riadky a stĺpce^{(rows and columns)} od 1 vľavo hore. Takže stĺpec rep je 3, stĺpec položky je 4 a celkový stĺpec je 7.

Ak chcete načítať tieto tri stĺpce do všetkých 11 riadkov, budete musieť napísať nasledujúci skript.

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

Všimnete si, že ak chcete preskočiť riadok hlavičky, číslo riadka v prvom vzhľade For musí začínať na 2, a nie na 1. To znamená, že pri načítavaní hodnoty bunky musíte odpočítať 1 od hodnoty riadka poľa. do poľa pomocou buniek^(Cells) (i, j).Value.

Kam vložiť skript poľa VBA^{(Your VBA Array Script)}

Ak chcete naprogramovať skript VBA do ^(VBA)Excelu^(Excel) , musíte použiť editor VBA . Dostanete sa k tomu výberom ponuky Vývojár^(Developer) a výberom položky Zobraziť kód^{(View Code)} v sekcii Ovládacie prvky^(Controls) na páse s nástrojmi.

Ak vývojára^(Developer) v ponuke nevidíte , budete ho musieť pridať. Ak to chcete urobiť, výberom položky Súbor^(File) a možnosti^(Options) otvorte okno Možnosti programu Excel.

Zmeňte výber príkazov z rozbaľovacej ponuky na Všetky príkazy^{(All Commands)} . V ľavej ponuke vyberte položku Vývojár a kliknutím na tlačidlo ^(Developer)Pridať^(Add) ho presuňte na panel vpravo. Začiarknutím políčka ho povolíte a výberom OK dokončite.

Keď sa otvorí okno Editor kódu^{(Code Editor)} , skontrolujte, či je na ľavej table vybratý hárok, v ktorom sa nachádzajú vaše údaje. V ľavom rozbaľovacom zozname vyberte položku Pracovný hárok a v pravej časti ^(Worksheet)Aktivovať^(Activate) . Tým sa vytvorí nový podprogram s názvom Worksheet_Activate ().

Táto funkcia sa spustí vždy, keď sa otvorí tabuľkový súbor. Prilepte kód na tablu skriptov v tomto podprograme.

Tento skript bude fungovať cez 12 riadkov a načíta meno zástupcu zo stĺpca 3, položku zo stĺpca 4 a celkový predaj zo stĺpca 7.

Po dokončení oboch cyklov For bude dvojrozmerné pole arrMyArray obsahovať všetky údaje, ktoré ste zadali z pôvodného listu.

Manipulácia s poliami v Exceli VBA

Povedzme, že chcete uplatniť 5 % daň z obratu na všetky konečné predajné ceny a potom zapísať všetky údaje do nového hárku.

Môžete to urobiť pridaním ďalšej slučky For za prvú s príkazom na zápis výsledkov do nového hárku.

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

Toto „vyloží“ celé pole do Hárok2^(Sheet2) , pričom ďalší riadok bude obsahovať súčet sumy dane vynásobený 5 %.

Výsledný list bude vyzerať takto.

Ako vidíte, polia VBA v ^(VBA)Exceli^(Excel) sú mimoriadne užitočné a rovnako všestranné ako ktorýkoľvek iný trik Excelu^{(any other Excel trick)} . 

Vyššie uvedený príklad predstavuje veľmi jednoduché použitie poľa. Môžete vytvárať oveľa väčšie polia a vykonávať triedenie, priemerovanie alebo mnoho ďalších funkcií s údajmi, ktoré v nich ukladáte.

Ak chcete byť skutočne kreatívni, môžete dokonca vytvoriť dve polia obsahujúce rozsah buniek^{(containing a range of cells)} z dvoch rôznych hárkov a vykonať výpočty medzi prvkami každého poľa.

Aplikácie sú obmedzené len vašou fantáziou.

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 functionality and power of a full VB application, VBA provides Office users with the flexibility to integrate Office products and automate the work yоu 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

• Ako odstrániť prázdne riadky v Exceli

• Ako používať funkciu Speak Cells v Exceli

• Ako vložiť pracovný hárok programu Excel do dokumentu programu Word

• Ako používať analýzu What-If v Exceli

• Ako opraviť riadok v Exceli

• Kedy použiť Index-Match namiesto VLOOKUP v Exceli

• Ako používať funkciu PMT v Exceli

• Rýchlo vyhláskujte čísla vo Worde a Exceli

• Použite názvy dynamických rozsahov v Exceli na flexibilné rozbaľovacie zoznamy

• 2 spôsoby použitia funkcie Transponovať v Exceli

• Ako rýchlo vložiť viac riadkov v Exceli

• Ako vložiť CSV alebo TSV do pracovného hárka programu Excel

• Ako otvoriť Word a Excel v núdzovom režime

• Ako používať Flash Fill v Exceli

• Ako vytvoriť jednoduchú kontingenčnú tabuľku v Exceli

• Zoskupte riadky a stĺpce v pracovnom hárku programu Excel

• Prečo by ste mali používať pomenované rozsahy v Exceli

• Pripojenie Excelu k MySQL

• Ako abecedne písať v Exceli

• Pokročilá príručka VBA pre MS Excel