A VBA^(VBA) évek óta a Microsoft Office programcsomag része. Bár nem rendelkezik a teljes VB-alkalmazások teljes funkcionalitásával és erejével, a VBA rugalmasságot biztosít az Office -felhasználók számára az ^(Office)Office - termékek integrálásához és az azokban végzett munka automatizálásához.

A VBA^(VBA) egyik leghatékonyabb eszköze az adatok teljes tartományának betöltése egyetlen változóba, az úgynevezett tömbbe. Az adatok ilyen módon történő betöltésével többféleképpen manipulálhatja vagy számításokat végezhet ezen az adatokon.

Tehát mi az a VBA tömb? Ebben a cikkben válaszolunk erre a kérdésre, és megmutatjuk, hogyan használhatja ezt a saját VBA-szkriptjében^{(your own VBA script)} .

Mi az a VBA-tömb?

A VBA -tömb használata az Excelben^(Excel) nagyon egyszerű, de a tömbök fogalmának megértése kissé bonyolult lehet, ha még soha nem használt.

Képzeljen^(Think) el egy tömböt, mint egy dobozt, benne szakaszokkal. Az egydimenziós tömb egy olyan doboz, amely egy sor szakaszt tartalmaz. A kétdimenziós tömb egy olyan doboz, amely két sornyi szakaszt tartalmaz.

Ennek a „doboznak” az egyes szakaszaiba tetszőleges sorrendben helyezheti el az adatokat.

A VBA^(VBA) -szkript elején meg kell határoznia ezt a „dobozt” a VBA - tömb meghatározásával. Tehát egy olyan tömb létrehozásához, amely egy adathalmazt tartalmazhat (egy egydimenziós tömb), a következő sort kell írnia.

Dim arrMyArray(1 To 6) As String

A program későbbi szakaszában a tömb bármely szakaszába elhelyezhet adatokat a zárójelben lévő szakaszra hivatkozva.

arrMyArray(1) = "Ryan Dube"

A következő sor segítségével kétdimenziós tömböt hozhat létre.

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

Az első szám a sort, a második az oszlopot jelöli. Tehát a fenti tömb egy 6 sorból és 2 oszlopból álló tartományt tartalmazhat.

Ennek a tömbnek bármely elemét betöltheti adatokkal az alábbiak szerint.

arrMyArray(1,2) = 3

Ez egy 3-ast tölt be a B1 cellába.

Egy tömb bármilyen típusú adatot tartalmazhat normál változóként, például karakterláncokat, logikai értékeket, egész számokat, lebegőpontokat stb.

A zárójelben lévő szám változó is lehet. A programozók általában egy For Loop -ot használnak a tömb összes szakaszának végigszámlálására, és a táblázat adatcelláinak betöltésére a tömbbe. A cikk későbbi részében megtudhatja, hogyan kell ezt megtenni.

Hogyan lehet VBA-tömböt programozni Excelben

Vessünk egy pillantást egy egyszerű programra, ahol érdemes lehet információkat betölteni egy táblázatból egy többdimenziós tömbbe.

Példaként nézzünk meg egy termékértékesítési táblázatot, ahol ki szeretné húzni az értékesítési képviselő nevét, cikkét és a teljes értékesítést a táblázatból.

Vegye figyelembe, hogy a VBA -ban , amikor sorokra vagy oszlopokra hivatkozik, a sorokat és oszlopokat^{(rows and columns)} a bal felső saroktól kezdve 1-től számolja. Tehát a rep oszlop 3, az elem oszlop 4, az összes oszlop pedig 7.

Ahhoz, hogy ezt a három oszlopot mind a 11 sorra betöltse, meg kell írnia a következő szkriptet.

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

Észre fogja venni, hogy a fejlécsor kihagyásához az első For look sorszámának 2-vel kell kezdődnie 1 helyett. Ez azt jelenti, hogy a cellaérték betöltésekor 1-et kell kivonnia a tömbsor értékéből. a tömbbe a Cells (i, j) segítségével.Érték.

Hová illessze be a VBA-tömb szkriptjét^{(Your VBA Array Script)}

A VBA -szkript program Excelben való elhelyezéséhez a ^(Excel)VBA - szerkesztőt kell használnia . Ezt úgy érheti el, hogy kiválasztja a Fejlesztő^(Developer) menüt, és a szalag Vezérlők^(Controls) részében a Kód megtekintése lehetőséget választja.^{(View Code)}

Ha nem látja a Fejlesztőt^(Developer) a menüben, hozzá kell adnia. Ehhez válassza a Fájl^(File) és beállítások lehetőséget^(Options) az Excel beállításai ablak megnyitásához.

Módosítsa a parancsok kijelölését a legördülő menüből Minden parancsra^{(All Commands)} . Válassza a Fejlesztő^(Developer) lehetőséget a bal oldali menüből, majd a Hozzáadás^(Add) gombbal helyezze át a jobb oldali ablaktáblába. Jelölje be a jelölőnégyzetet az engedélyezéséhez, és kattintson az OK gombra^(OK) a befejezéshez.

Amikor megnyílik a Kódszerkesztő^{(Code Editor)} ablak, győződjön meg arról, hogy a bal oldali ablaktáblában az a munkalap van kiválasztva, amelyen az adatai vannak. A bal oldali legördülő menüben válassza a Munkalap lehetőséget, a jobb oldalon pedig az ^(Worksheet)Aktiválás^(Activate) lehetőséget. Ez létrehoz egy új szubrutint Worksheet_Activate () néven.

Ez a funkció a táblázatfájl megnyitásakor fog futni. Illessze be a kódot a szkript ablaktáblájába ezen az alprogramon belül.

Ez a szkript a 12 soron keresztül működik, és a 3. oszlopból tölti be a képviselő nevét, a 4. oszlopból a tételt, a 7. oszlopból pedig a teljes eladást.

Ha mindkét For hurok befejeződött, az arrMyArray kétdimenziós tömb tartalmazza az eredeti lapon megadott összes adatot.

Tömbök kezelése Excel VBA-ban

Tegyük fel, hogy 5%-os forgalmi adót szeretne alkalmazni az összes végső eladási árra, majd az összes adatot egy új lapra szeretné írni.

Ezt úgy teheti meg, hogy az első után egy másik For ciklust ad hozzá, és az eredményeket egy új lapra írja ki.

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

Ez a teljes tömböt „kirakja” a 2. munkalapra^(Sheet2) , egy további sorral, amely az adóösszeg 5%-kal szorozva tartalmazza az összeget.

A kapott lap így fog kinézni.

Amint láthatja, az Excel VBA -tömbjei rendkívül hasznosak és ugyanolyan sokoldalúak, mint bármely más Excel-trükk^{(any other Excel trick)} . 

A fenti példa egy nagyon egyszerű tömb használatára szolgál. Létrehozhat sokkal nagyobb tömböket, és végrehajthat rendezést, átlagolást vagy sok más funkciót a bennük tárolt adatok alapján.

Ha igazán kreatív akar lenni, akkor akár két tömböt is létrehozhat, amelyek két különböző lap cellatartományát tartalmazzák , és számításokat végezhet az egyes tömbök elemei között.^{(containing a range of cells)}

Az alkalmazásoknak csak a képzelet szab határt.

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

VBA has been part of the Microsoft Offiсe sυite for many yearѕ. While it doesn’t have the full functiоnality and power of a full VB applicatiоn, VBA provides Office users with the flexibіlity to іntegrate Office рroducts and automate the work you do in thеm.

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

• Az üres sorok törlése az Excelben

• Az Excel Speak Cells funkciójának használata

• Excel-munkalap beszúrása Word-dokumentumba

• Az Excel „Mi lenne, ha” elemzésének használata

• Hogyan javítsunk ki egy sort az Excelben

• Miért érdemes elnevezett tartományokat használni az Excelben?

• Alapvető egyoszlopos és többoszlopos adatrendezés Excel-táblázatokban

• Az Excel csatlakoztatása a MySQL-hez

• Adatok egyesítése több Excel-fájlban

• Hogyan írjunk IF-képletet/-kimutatást Excelben

• Több linkelt legördülő lista létrehozása az Excelben

• Használja a billentyűzetet a sormagasság és az oszlopszélesség módosításához az Excelben

• Mikor érdemes az Index-Match-et használni a VLOOKUP helyett az Excelben

• 4 módszer az Excel konvertálására Google Táblázatokká

• Az abszolút hivatkozások használata az Excelben

• Hogyan lehet gyorsan beszúrni több sort az Excelbe

• Az Excel „Mi lenne, ha” elemző célkereső eszközének használata

• A cellahivatkozások megőrzése képlet Excelben másolásakor

• 2 módszer az Excel transzponálási funkciójának használatára

• Hogyan távolítsuk el a rácsvonalakat az Excelben