VBA waxay qayb ka ahayd suuit Microsoft Office^{(Microsoft Office)} sanado badan. In kasta oo ayan lahayn shaqada buuxda iyo awooda codsiga VB buuxa, VBA waxay siisaa isticmaalayaasha Xafiiska^(Office) dabacsanaan ay ku dhexgalaan alaabada Xafiiska^(Office) oo ay otomaatig ugu noqdaan shaqada aad ku qabato.

Mid ka mid ah qalabka ugu awoodda badan ee laga heli karo VBA waa awoodda lagu shubo xogta kala duwan ee hal doorsoome oo loo yaqaan array. Marka aad xogta sidaan u soo shubto, waxa aad ku maamuli kartaa ama aad samayn kartaa xisaabinta tirada xogtaas siyaabo kala duwan.

Haddaba waa maxay diyaarsanaanta VBA^(VBA) ? Maqaalkan waxaan ka jawaabi doonaa su'aashaas, oo ku tusi doonaa sida loo isticmaalo mid ka mid ah farta VBA ee adiga kuu gaar ah^{(your own VBA script)} .

Waa maxay VBA Array?

Isticmaalka qaabka VBA ee Excel waa mid aad u fudud, laakiin fahamka fikradda arraysku waxay noqon kartaa wax yar oo adag haddii aadan waligaa isticmaalin.

Ka fakar^(Think) shax sida sanduuq oo kale qaybo ay ku jiraan. Aarray hal-cabbir ah waa sanduuq leh hal xariiq oo qaybo ah. Qaabka laba-geesoodka ah waa sanduuq leh laba xariiq oo qaybo ah.

Waxaad ku dari kartaa xogta qayb kasta oo ka mid ah "sanduuqan" si kasta oo aad rabto.

Bilawga qoraalkaaga VBA -da , waxaad u baahan tahay inaad qeexdo "sanduuqa"kan adoo qeexaya soo diyaarsankaaga VBA^(VBA) . Haddaba si aad u abuurto array hayn kara hal xog (hal-cabbir array), waxaad qori lahayd xariiqan soo socota.

Dim arrMyArray(1 To 6) As String

Hadhow barnaamijkaaga, waxaad ku dari kartaa xogta qayb kasta oo ka mid ah shaxdan adiga oo tixraacaya qaybta gudaha jaantusyada.

arrMyArray(1) = "Ryan Dube"

Waxaad samayn kartaa array laba-cabbir ah adoo isticmaalaya xariiqan soo socota.

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

Lambarka kowaad wuxuu matalaa safka, ka labaadna waa tiirka. Markaa shaxanka kore wuxuu hayn karaa kala duwanaansho leh 6 saf iyo 2 tiir.

Waxaad ku shuban kartaa wax kasta oo ka mid ah shaxdan xogta sida soo socota.

arrMyArray(1,2) = 3

Tani waxay ku shubi doontaa 3 unugga B1.

Diyaarintu waxay hayn kartaa nooc kasta oo xog ah sida doorsoome caadi ah, sida xargaha, boolean-ka, tiro-koobyada, sabaynta iyo in ka badan.

Nambarka ku jira qawlka wuxuu kaloo noqon karaa doorsoome. Barnaamij-bixiyeyaashu waxay caadi ahaan adeegsadaan For Loop si ay u tiriyaan dhammaan qaybaha array-ga oo ay ku shubaan unugyada xogta ee xaashida safafka. Waxaad arki doontaa sida tan loo sameeyo mar dambe maqaalkan.

Sida loo diyaariyo VBA Array gudaha Excel

Aan eegno barnaamij fudud oo laga yaabo inaad rabto inaad macluumaadka ka soo rogto xaashida xaashida oo aad geliso array dhinacyo badan leh.

Tusaale ahaan, aan eegno xaashida iibka alaabta, meesha aad rabto inaad ka soo saarto magaca iibka, shayga, iyo wadarta iibka ka saar xaashida.

Ogsoonow in VBA -da marka aad tixraacayso safafka ama tiirarka, waxaad tirisaa safafka iyo tiirarka^{(rows and columns)} ka bilaabmaya bidixda sare ee 1. Markaa tiirarka rep waa 3, tiirka shaygu waa 4, tiirka guudna waa 7.

Si aad saddexdan tiir ugu shubto dhammaan 11 saf, waxaad u baahan doontaa inaad qorto qoraalkan soo socda.

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

Waxaad ogaan doontaa in si aad uga gudubto safka madaxa, nambarka safka ee kan ugu horreeya wuxuu u baahan yahay inuu ka bilaabo 2 halkii uu ka bilaabi lahaa 1. Tani waxay ka dhigan tahay inaad u baahan tahay inaad ka jarto 1 qiimaha safafka isku dhejinta marka aad ku shubayso qiimaha unugyada. galay array isticmaalaya Unugyada^(Cells) (i, j).Qiimaha.

Halkee Laga Geli Karaa Qoraalkaaga Array VBA^{(Your VBA Array Script)}

Si aad u dhigto barnaamijka qoraalka VBA gudaha ^(VBA)Excel , waxaad u baahan tahay inaad isticmaasho tifaftiraha VBA . Waxa aad tan ku heli kartaa adiga oo dooranaya menu^(Developer) -ka soo- saare oo doorta View Code ee qaybta Kontroolka^(Controls) ee ribbon.

Haddii aadan ku arag horumariyaha^(Developer) menu-ka, waxaad u baahan doontaa inaad ku darto. Si tan loo sameeyo, dooro File iyo Options si aad u furto daaqada Excel Options.

U beddel amarrada xulashada hoos u dhaca una beddel dhammaan amarrada^{(All Commands)} . Ka dooro horumariye^(Developer) ka menu-ka bidix oo dooro badhanka ku dar^(Add) si aad ugu guurto shayga dhanka midig. Dooro sanduuqa hubinta si aad awood ugu yeelato oo dooro OK si aad u dhammayso.

Marka uu furmo daaqada Code Editor , hubi in xaashida xogtaadu ku jirto ay ku jirto dhinaca bidixda. Ka dooro Xaashida shaqada^(Worksheet) ee hoos u dhaca bidix oo ku dhaqaaq dhanka^(Activate) midig. Tani waxay abuuri doonta subbroutine cusub oo loo yaqaan Worksheet_Activate ().

Shaqadani waxay socon doontaa mar kasta oo la furo faylka xaashida. Ku dheji koodka shayga qoraalka ee gudaha subroutine-kan.

Qoraalkani wuxuu dhex shaqayn doonaa 12 saf wuxuuna ka soo rari doonaa magaca rep ee tiirka 3, shayga tiirka 4, iyo wadarta iibka tiirka 7.

Marka labada siddo la dhammeeyo, labada cabbir ee array arrMyArray ka kooban dhammaan xogta aad ku qeexday xaashida asalka ah.

Ku Maaraynta Arrays ee Excel VBA

Aynu nidhaahno inaad rabto inaad 5% cashuurta iibka ku dalbato dhammaan qiimayaasha iibka ee u dambeeya, ka dibna dhammaan xogta ku qor xaashi cusub.

Waxa aad tan samayn kartaa adiga oo ku dara mid kale oo loop ah ka dib marka hore, oo wata amar aad natiijada ugu qorto xaashi cusub.

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

Tani waxay "soo dejin doontaa" dhammaan shaxanka Sheet2^(Sheet2) , oo leh saf dheeri ah oo ka kooban wadarta guud ee lagu dhufto 5% qaddarka canshuurta.

Xaashida soo baxday waxay u ekaan doontaa sidan.

Sida aad arki karto, qaababka VBA^(VBA) ee Excel waa kuwo aad u faa'iido leh oo la mid ah sida xeelad kasta oo kale oo Excel ah^{(any other Excel trick)} . 

Tusaalaha kor ku xusan waa adeegsi aad u fudud oo habaysan. Waxaad samayn kartaa habab aad u weyn oo waxaad samayn kartaa kala-soocidda, celceliska, ama hawlo kale oo badan oo liddi ku ah xogta aad ku kaydiso iyaga.

Haddii aad rabto inaad hesho hal-abuur dhab ah, waxaad xitaa abuuri kartaa laba hannaan oo ay ku jiraan unugyo kala duwan oo^{(containing a range of cells)} ka socda laba xaashi oo kala duwan, waxaadna samayn kartaa xisaabinta inta u dhaxaysa walxaha shax kasta.

Codsiyada waxaa xaddidaya male-awaalkaaga kaliya.

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

VBA has been part of the Microsoft Officе suite for mаny yearѕ. While it doesn’t have the full functionality and power of a full VB application, VBA provides Office users with the flexibility to intеgratе Office products and аutomate 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

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Waa maxay sababta ay tahay inaad u isticmaasho safafka la magacaabay ee Excel

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Ku xidhida Excel iyo MySQL

• Sida loo Furo Word iyo Excel si Badbaado ah

• Adeegsiga Qalab Raadinta Hadafka ee Excel Maxaa-Haddii Falanqaynta

• Si degdeg ah ugu qor nambarada Word iyo Excel

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• Sida Loo Raadiyo Excel

• Farqiga u dhexeeya Microsoft Excel Online iyo Excel ee Desktop

• Sida Loo Sameeyo Shaxda Pivot Fudud ee Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah

• Sida Loo Wadaago Faylka Excel Si Fudud Layskula Shaqeeyo

• Hagaha VBA Sare ee MS Excel