VBA on ollut osa Microsoft Office -pakettia useiden vuosien ajan. Vaikka siinä ei ole VB-sovelluksen kaikkia toimintoja ja tehoja, VBA tarjoaa Office -käyttäjille joustavuutta Office - tuotteiden integroimiseen ja niissä tekemäsi työn automatisointiin.

Yksi VBA^(VBA) :n tehokkaimmista työkaluista on kyky ladata koko joukko tietoja yhteen muuttujaan, jota kutsutaan taulukoksi. Lataamalla tietoja tällä tavalla, voit käsitellä tai suorittaa laskelmia kyseiselle tietoalueelle monin eri tavoin.

Joten mikä on VBA - taulukko? Tässä artikkelissa vastaamme tähän kysymykseen ja näytämme, kuinka voit käyttää sitä omassa VBA-skriptissäsi^{(your own VBA script)} .

Mikä on VBA-taulukko?

VBA -taulukon käyttäminen Excelissä on^(Excel) hyvin yksinkertaista, mutta taulukoiden käsitteen ymmärtäminen voi olla hieman monimutkaista, jos et ole koskaan käyttänyt sitä.

Ajattele^(Think) taulukkoa kuin laatikkoa, jonka sisällä on osia. Yksiulotteinen taulukko on laatikko, jossa on yksi rivi osia. Kaksiulotteinen taulukko on laatikko, jossa on kaksi riviä osia.

Voit laittaa tiedot tämän "laatikon" jokaiseen osioon missä tahansa järjestyksessä.

VBA -skriptin alussa sinun on määritettävä tämä "laatikko" määrittämällä VBA - taulukkosi. Joten luodaksesi taulukon, johon mahtuu yksi tietojoukko (yksiulotteinen taulukko), kirjoitat seuraavan rivin.

Dim arrMyArray(1 To 6) As String

Myöhemmin ohjelmassasi voit sijoittaa tietoja mihin tahansa tämän taulukon osioon viittaamalla sulkeiden sisällä olevaan osioon.

arrMyArray(1) = "Ryan Dube"

Voit luoda kaksiulotteisen taulukon käyttämällä seuraavaa riviä.

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

Ensimmäinen numero edustaa riviä ja toinen on sarake. Joten yllä oleva taulukko voisi sisältää 6 rivin ja 2 sarakkeen alueen.

Voit ladata minkä tahansa tämän taulukon elementin tiedoilla seuraavasti.

arrMyArray(1,2) = 3

Tämä lataa 3:n soluun B1.

Taulukko voi sisältää minkä tahansa tyyppistä dataa tavallisena muuttujana, kuten merkkijonoja, loogisia arvoja, kokonaislukuja, kellukkeita ja paljon muuta.

Suluissa oleva luku voi myös olla muuttuja. Ohjelmoijat käyttävät yleensä For Loopia^(Loop) laskeakseen taulukon kaikki osat ja ladatakseen laskentataulukon tietosoluja taulukkoon. Näet kuinka tämä tehdään myöhemmin tässä artikkelissa.

Kuinka ohjelmoida VBA-taulukko Excelissä

Katsotaanpa yksinkertaista ohjelmaa, jossa haluat ehkä ladata tietoja laskentataulukosta moniulotteiseen taulukkoon.

Katsotaanpa esimerkkinä tuotteen myyntilaskentataulukkoa, josta haluat vetää myyntiedustajan nimen, tuotteen ja kokonaismyynnin pois laskentataulukosta.

Huomaa, että VBA :ssa, kun viittaat riveihin tai sarakkeisiin, lasket rivit ja sarakkeet^{(rows and columns)} alkaen vasemmasta yläkulmasta 1:stä. Rep-sarake on siis 3, kohdesarake on 4 ja sarakkeen kokonaismäärä on 7.

Jos haluat ladata nämä kolme saraketta kaikille 11 riville, sinun on kirjoitettava seuraava komentosarja.

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

Huomaat, että otsikkorivin ohittaminen edellyttää, että ensimmäisen For lookin rivin numero alkaa 2:sta 1:n sijaan. Tämä tarkoittaa, että sinun on vähennettävä 1 taulukon rivin arvosta, kun lataat solun arvoa. taulukkoon käyttämällä soluja^(Cells) (i, j). Arvo.

Mihin VBA-taulukon komentosarja^{(Your VBA Array Script)} lisätään

Jotta voit sijoittaa ohjelman VBA -komentosarjan Exceliin^(Excel) , sinun on käytettävä VBA - editoria. Pääset tähän valitsemalla Kehittäjä^(Developer) - valikon ja valitsemalla nauhan Ohjaimet^(Controls) -osiosta Näytä koodi .^{(View Code)}

Jos Kehittäjä^(Developer) ei näy valikossa, sinun on lisättävä se. Voit tehdä tämän valitsemalla Tiedosto^(File) ja asetukset^(Options) avataksesi Excelin asetukset -ikkunan.

Muuta valintakomennot pudotusvalikosta Kaikki komennot^{(All Commands)} . Valitse vasemmanpuoleisesta valikosta Kehittäjä ja valitse ^(Developer)Lisää^(Add) - painike siirtääksesi sen oikeanpuoleiseen ruutuun. Ota se käyttöön valitsemalla valintaruutu ja lopeta valitsemalla OK .

Kun Koodieditori^{(Code Editor)} - ikkuna avautuu, varmista, että taulukko, jossa tietosi ovat, on valittuna vasemmasta ruudusta. Valitse vasemmasta pudotusvalikosta Työtaulukko ja oikealta ^(Worksheet)Aktivoi^(Activate) . Tämä luo uuden aliohjelman nimeltä Worksheet_Activate ().

Tämä toiminto suoritetaan aina, kun laskentataulukkotiedosto avataan. Liitä koodi tämän aliohjelman skriptiruutuun.

Tämä komentosarja käy läpi 12 riviä ja lataa edustajan nimen sarakkeesta 3, tuotteen sarakkeesta 4 ja kokonaismyynnistä sarakkeesta 7.

Kun molemmat For-silmukat ovat valmiit, kaksiulotteinen taulukko arrMyArray sisältää kaikki alkuperäisestä taulukosta määrittämäsi tiedot.

Taulukon manipulointi Excel VBA:ssa

Oletetaan, että haluat soveltaa 5 %:n myyntiveroa kaikkiin lopullisiin myyntihintoihin ja kirjoittaa sitten kaikki tiedot uudelle taulukolle.

Voit tehdä tämän lisäämällä toisen For-silmukan ensimmäisen jälkeen komennolla kirjoittaa tulokset uudelle taulukolle.

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

Tämä "purkaa" koko taulukon taulukkoon2 ja lisärivillä^(Sheet2) , joka sisältää summan kerrottuna 5 %:lla verosummalla.

Tuloksena oleva arkki näyttää tältä.

Kuten näet, VBA -taulukot Excelissä^(Excel) ovat erittäin hyödyllisiä ja yhtä monipuolisia kuin mikä tahansa muu Excel-temppu^{(any other Excel trick)} . 

Yllä oleva esimerkki on hyvin yksinkertainen taulukon käyttö. Voit luoda paljon suurempia taulukoita ja suorittaa lajittelua, keskiarvoa tai monia muita toimintoja niihin tallentamiasi tietoja vastaan.

Jos haluat olla todella luova, voit jopa luoda kaksi taulukkoa , jotka sisältävät solualueen^{(containing a range of cells)} kahdesta eri taulukosta, ja suorittaa laskelmia kunkin taulukon elementtien välillä.

Sovelluksia rajoittaa vain mielikuvituksesi.

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

VBA has been part of the Mіcrosoft Offiсe ѕuіte for many years. Whilе it doesn’t have the fυll funсtionality and power of a full VΒ application, VBA provides Office users with thе flexibility to integrate Office products and 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

• Kuinka poistaa tyhjiä rivejä Excelissä

• Excelin Speak Cells -ominaisuuden käyttäminen

• Kuinka lisätä Excel-laskentataulukko Word-dokumenttiin

• Excelin mitä jos -analyysin käyttäminen

• Kuinka korjata rivi Excelissä

• Miksi sinun pitäisi käyttää nimettyjä alueita Excelissä?

• Kuinka lajitella päivämäärän mukaan Excelissä

• Kuinka lisätä CSV tai TSV Excel-laskentataulukkoon

• Yksisarakkeen ja monisarakkeen tietojen peruslajittelu Excel-laskentataulukoissa

• Excel-tiedoston jakaminen helppoa yhteistyötä varten

• Solujen, sarakkeiden ja rivien yhdistäminen Excelissä

• 3 tapaa jakaa solu Excelissä

• Kirjoita numerot nopeasti Wordissa ja Excelissä

• 2 tapaa käyttää Excelin transponointifunktiota

• Kuinka laskea Z-pisteet Excelissä

• Kuinka käyttää Flash-täyttöä Excelissä

• Kuinka jäljittää riippuvia Excelissä

• Excelin mitä jos -analyysitavoitteenhakutyökalun käyttäminen

• Milloin käyttää Index-Matchia VLOOKUPin sijaan Excelissä

• Kuinka luoda VBA-makro tai komentosarja Excelissä