You Can Assign the Contents of One Array to Another by Using ________.
This post provides an in-depth look at the VBA array which is a very of import part of the Excel VBA programming language. It covers everything you need to know about the VBA array.
We will offset by seeing what exactly is the VBA Array is and why you demand it.
Beneath you will see a quick reference guide to using the VBA Array. Refer to it anytime you need a quick reminder of the VBA Assortment syntax.
The residuum of the mail provides the most complete guide you volition detect on the VBA assortment.
Contents
- 1 Related Links for the VBA Array
- ii A Quick Guide to the VBA Array
- 3 Download the Source Lawmaking and Data
- 4 What is the VBA Assortment and Why do You lot Need It?
- five Ii Types of VBA Arrays
- 6 VBA Array Initialization
- 7 Assigning Values to VBA Array
- 8 VBA Assortment Length
- ix Using the Assortment and Split function
- x Using Loops With the VBA Array
- ten.1 Using the For Each Loop with the VBA Array
- 11 Using Erase with the VBA Assortment
- 12 Increasing the length of the VBA Array
- 12.1 Using Preserve with Ii-Dimensional Arrays
- xiii Sorting the VBA Array
- 14 Passing the VBA Array to a Sub
- 15 Returning the VBA Array from a Function
- 16 Using a Two-Dimensional VBA Array
- 17 Using the For Each Loop
- 18 Reading from a Range to the VBA Array
- nineteen How To Make Your Macros Run at Super Speed
- 20 Decision
- 21 What's Next?
Loops are used for reading through the VBA Assortment:
For Loop
For Each Loop
Other data structures in VBA:
VBA Collection – Good when you want to keep inserting items as information technology automatically resizes.
VBA ArrayList – This has more functionality than the Drove.
VBA Dictionary – Allows storing a Primal\Value pair. Very useful in many applications.
The Microsoft guide for VBA Arrays tin can be plant here.
A Quick Guide to the VBA Array
| Task | Static Assortment | Dynamic Array |
|---|---|---|
| Declare | Dim arr(0 To 5) Every bit Long | Dim arr() As Long Dim arr Equally Variant |
| Set Size | Encounter Declare in a higher place | ReDim arr(0 To 5)Equally Variant |
| Get Size(number of items) | See ArraySize function below. | See ArraySize function below. |
| Increase size (continue existing information) | Dynamic Simply | ReDim Preserve arr(0 To half-dozen) |
| Fix values | arr(1) = 22 | arr(i) = 22 |
| Receive values | total = arr(1) | total = arr(1) |
| First position | LBound(arr) | LBound(arr) |
| Last position | Ubound(arr) | Ubound(arr) |
| Read all items(1D) | For i = LBound(arr) To UBound(arr) Next i Or For i = LBound(arr,1) To UBound(arr,ane) Side by side i | For i = LBound(arr) To UBound(arr) Side by side i Or For i = LBound(arr,1) To UBound(arr,one) Next i |
| Read all items(2nd) | For i = LBound(arr,1) To UBound(arr,ane) For j = LBound(arr,ii) To UBound(arr,2) Next j Next i | For i = LBound(arr,1) To UBound(arr,1) For j = LBound(arr,2) To UBound(arr,2) Next j Next i |
| Read all items | Dim detail As Variant For Each item In arr Side by side particular | Dim particular Every bit Variant For Each detail In arr Next item |
| Laissez passer to Sub | Sub MySub(ByRef arr() As Cord) | Sub MySub(ByRef arr() As String) |
| Return from Function | Function GetArray() As Long() Dim arr(0 To five) Every bit Long GetArray = arr End Part | Function GetArray() Equally Long() Dim arr() As Long GetArray = arr Terminate Function |
| Receive from Role | Dynamic merely | Dim arr() Every bit Long Arr = GetArray() |
| Erase assortment | Erase arr *Resets all values to default | Erase arr *Deletes array |
| Cord to array | Dynamic simply | Dim arr Equally Variant arr = Split up("James:Earl:Jones",":") |
| Assortment to cord | Dim sName Equally String sName = Join(arr, ":") | Dim sName As String sName = Join(arr, ":") |
| Fill with values | Dynamic only | Dim arr As Variant arr = Array("John", "Hazel", "Fred") |
| Range to Array | Dynamic simply | Dim arr As Variant arr = Range("A1:D2") |
| Assortment to Range | Same as dynamic | Dim arr As Variant Range("A5:D6") = arr |
Download the Source Code and Data
Please click on the push button below to get the fully documented source code for this commodity.
What is the VBA Array and Why do You Demand Information technology?
A VBA array is a blazon of variable. It is used to store lists of information of the same type. An example would be storing a listing of countries or a listing of weekly totals.
In VBA a normal variable can store only one value at a fourth dimension.
In the following example we use a variable to store the marks of a student:
' Can only store 1 value at a time Dim Student1 As Long Student1 = 55
If we wish to shop the marks of another educatee and so we need to create a 2d variable.
In the post-obit example, we have the marks of five students:
Educatee Marks
We are going to read these marks and write them to the Immediate Window.
Note: The function Debug.Impress writes values to the Immediate Window. To view this window select View->Firsthand Window from the menu( Shortcut is Ctrl + G)
Equally you can come across in the following case we are writing the same code five times – once for each student:
' https://excelmacromastery.com/ Public Sub StudentMarks() ' Get the worksheet chosen "Marks" Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("Marks") ' Declare variable for each student Dim Student1 As Long Dim Student2 Every bit Long Dim Student3 As Long Dim Student4 As Long Dim Student5 As Long ' Read student marks from cell Student1 = sh.Range("C" & iii).Value Student2 = sh.Range("C" & 4).Value Student3 = sh.Range("C" & v).Value Student4 = sh.Range("C" & 6).Value Student5 = sh.Range("C" & 7).Value ' Impress pupil marks Debug.Print "Students Marks" Debug.Print Student1 Debug.Print Student2 Debug.Print Student3 Debug.Print Student4 Debug.Impress Student5 End Sub
The following is the output from the case:
Output
The trouble with using one variable per pupil is that you need to add code for each student. Therefore if y'all had a thousand students in the above example you would need three thousand lines of code!
Luckily we have arrays to make our life easier. Arrays allow u.s. to store a list of data items in ane structure.
The following code shows the to a higher place student example using an array:
' ExcelMacroMastery.com ' https://excelmacromastery.com/excel-vba-assortment/ ' Writer: Paul Kelly ' Clarification: Reads marks to an Array and write ' the array to the Immediate Window(Ctrl + G) ' TO RUN: Click in the sub and press F5 Public Sub StudentMarksArr() ' Get the worksheet called "Marks" Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("Marks") ' Declare an array to hold marks for five students Dim Students(ane To five) As Long ' Read pupil marks from cells C3:C7 into array ' Offset counts rows from cell C2. ' e.g. i=1 is C2 plus i row which is C3 ' i=2 is C2 plus 2 rows which is C4 Dim i Every bit Long For i = 1 To five Students(i) = sh.Range("C2").Outset(i).Value Next i ' Print student marks from the array to the Immediate Window Debug.Impress "Students Marks" For i = LBound(Students) To UBound(Students) Debug.Impress Students(i) Next i End Sub
The advantage of this code is that it will piece of work for any number of students. If we take to alter this lawmaking to deal with m students nosotros simply demand to change the (1 To 5) to (one To 1000) in the declaration. In the prior example we would need to add approximately 5 1000 lines of lawmaking.
Permit's accept a quick comparing of variables and arrays. Starting time we compare the declaration:
' Variable Dim Pupil As Long Dim Land As Cord ' Assortment Dim Students(1 To 3) As Long Dim Countries(ane To iii) As String
Adjacent we compare assigning a value:
' assign value to variable Student1 = .Cells(1, 1) ' assign value to kickoff item in array Students(i) = .Cells(i, i)
Finally we wait at writing the values:
' Print variable value Debug.Print Student1 ' Print value of kickoff student in assortment Debug.Print Students(one)
As you can see, using variables and arrays is quite similar.
The fact that arrays use an alphabetize(also called a subscript) to access each item is important. Information technology means we tin easily admission all the items in an array using a For Loop.
At present that you accept some background on why arrays are useful let's get through them pace by step.
Two Types of VBA Arrays
There are two types of VBA arrays:
- Static – an assortment of stock-still length.
- Dynamic(not to be confused with the Excel Dynamic Array) – an array where the length is set at run time.
The difference betwixt these types is by and large in how they are created. Accessing values in both array types is exactly the same. In the following sections we volition comprehend both of these types.
VBA Array Initialization
A static array is initialized as follows:
' https://excelmacromastery.com/ Public Sub DecArrayStatic() ' Create assortment with locations 0,1,2,3 Dim arrMarks1(0 To three) As Long ' Defaults equally 0 to three i.due east. locations 0,1,2,3 Dim arrMarks2(3) As Long ' Create array with locations 1,two,3,4,5 Dim arrMarks3(one To five) As Long ' Create assortment with locations 2,three,iv ' This is rarely used Dim arrMarks4(two To 4) As Long Finish Sub
An Array of 0 to three
As yous can run into the length is specified when you declare a static assortment. The problem with this is that you can never be sure in advance the length y'all need. Each time you run the Macro yous may have different length requirements.
If you lot do non use all the array locations then the resources are beingness wasted. So if you need more locations y'all can use ReDim but this is substantially creating a new static array.
The dynamic assortment does not have such problems. Yous practice non specify the length when you declare it. Therefore you tin can so grow and compress every bit required:
' https://excelmacromastery.com/ Public Sub DecArrayDynamic() ' Declare dynamic array Dim arrMarks() Equally Long ' Gear up the length of the array when you are set up ReDim arrMarks(0 To five) End Sub
The dynamic array is not allocated until you use the ReDim statement. The advantage is you can look until you know the number of items before setting the array length. With a static array y'all take to land the length upfront.
To give an example. Imagine you lot were reading worksheets of pupil marks. With a dynamic array you lot can count the students on the worksheet and set an assortment to that length. With a static array you must set the length to the largest possible number of students.
Assigning Values to VBA Array
To assign values to an assortment you use the number of the location. You assign the value for both array types the same way:
' https://excelmacromastery.com/ Public Sub AssignValue() ' Declare array with locations 0,1,2,iii Dim arrMarks(0 To 3) As Long ' Set the value of position 0 arrMarks(0) = 5 ' Set the value of position 3 arrMarks(3) = 46 ' This is an fault as there is no location 4 arrMarks(4) = 99 End Sub
The array with values assigned
The number of the location is called the subscript or index. The last line in the example volition give a "Subscript out of Range" error equally in that location is no location iv in the array example.
VBA Array Length
There is no native part for getting the number of items in an assortment. I created the ArrayLength office beneath to return the number of items in whatsoever assortment no matter how many dimensions:
' https://excelmacromastery.com/ Function ArrayLength(arr As Variant) As Long On Fault Goto eh ' Loop is used for multidimensional arrays. The Loop volition cease when a ' "Subscript out of Range" error occurs i.east. in that location are no more than dimensions. Dim i As Long, length As Long length = i ' Loop until no more than dimensions Do While Truthful i = i + 1 ' If the array has no items then this line will throw an error Length = Length * (UBound(arr, i) - LBound(arr, i) + 1) ' Prepare ArrayLength here to avoid returing 1 for an empty array ArrayLength = Length Loop Done: Exit Function eh: If Err.Number = 13 And so ' Blazon Mismatch Error Err.Raise vbObjectError, "ArrayLength" _ , "The statement passed to the ArrayLength function is not an array." End If End Role
You lot can use it like this:
' Proper noun: TEST_ArrayLength ' Author: Paul Kelly, ExcelMacroMastery.com ' Description: Tests the ArrayLength functions and writes ' the results to the Immediate Window(Ctrl + Grand) Sub TEST_ArrayLength() ' 0 items Dim arr1() As Long Debug.Print ArrayLength(arr1) ' 10 items Dim arr2(0 To ix) Every bit Long Debug.Print ArrayLength(arr2) ' eighteen items Dim arr3(0 To 5, one To 3) As Long Debug.Impress ArrayLength(arr3) ' Selection base 0: 144 items ' Selection base one: 50 items Dim arr4(i, 5, 5, 0 To ane) As Long Debug.Print ArrayLength(arr4) End Sub
Using the Array and Split function
Y'all tin utilize the Array function to populate an array with a list of items. You lot must declare the assortment equally a blazon Variant. The following code shows you how to use this function.
Dim arr1 As Variant arr1 = Array("Orange", "Peach","Pear") Dim arr2 As Variant arr2 = Array(5, 6, seven, viii, 12)
Contents of arr1 afterward using the Assortment office
The assortment created by the Array Function will start at index zero unless you apply Pick Base ane at the top of your module. Then it will offset at index one. In programming, it is mostly considered poor practice to have your actual information in the lawmaking. However, sometimes it is useful when you need to test some code quickly.
The Separate part is used to split a cord into an array based on a delimiter. A delimiter is a character such as a comma or space that separates the items.
The post-obit code will separate the string into an assortment of three elements:
Dim s As Cord s = "Red,Yellow,Green,Blue" Dim arr() Equally String arr = Separate(s, ",")
The array after using Split up
The Split function is unremarkably used when y'all read from a comma-separated file or some other source that provides a listing of items separated past the same grapheme.
Using Loops With the VBA Array
Using a For Loop allows quick admission to all items in an array. This is where the power of using arrays becomes apparent. We tin read arrays with 10 values or 10 thou values using the same few lines of code. There are two functions in VBA called LBound and UBound. These functions return the smallest and largest subscript in an array. In an array arrMarks(0 to three) the LBound will return 0 and UBound will return iii.
The following case assigns random numbers to an array using a loop. Information technology then prints out these numbers using a second loop.
' https://excelmacromastery.com/ Public Sub ArrayLoops() ' Declare array Dim arrMarks(0 To 5) As Long ' Fill the array with random numbers Dim i Every bit Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = five * Rnd Adjacent i ' Print out the values in the assortment Debug.Impress "Location", "Value" For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i Cease Sub
The functions LBound and UBound are very useful. Using them means our loops will piece of work correctly with any array length. The real benefit is that if the length of the array changes nosotros do not accept to modify the lawmaking for press the values. A loop will work for an array of whatever length as long as yous utilise these functions.
Using the For Each Loop with the VBA Assortment
You tin use the For Each loop with arrays. The important matter to keep in mind is that information technology is Read-Only. This ways that you cannot change the value in the array.
In the following code the value of mark changes just it does non change the value in the array.
For Each mark In arrMarks ' Volition non modify the assortment value mark = 5 * Rnd Adjacent mark
The For Each is loop is fine to utilize for reading an array. It is neater to write especially for a Two-Dimensional array as nosotros volition meet.
Dim mark As Variant For Each mark In arrMarks Debug.Print mark Side by side mark
Using Erase with the VBA Assortment
The Erase function tin be used on arrays but performs differently depending on the assortment type.
For a static Array the Erase function resets all the values to the default. If the array is made up of long integers(i.due east type Long) then all the values are set to zilch. If the array is of strings so all the strings are set to "" and then on.
For a Dynamic Array the Erase function DeAllocates retentivity. That is, it deletes the array. If y'all want to utilise information technology again you must use ReDim to Allocate memory.
Let'southward have a look an example for the static array. This example is the same as the ArrayLoops example in the last section with 1 difference – we use Erase after setting the values. When the value are printed out they will all be zero:
' https://excelmacromastery.com/ Public Sub EraseStatic() ' Declare array Dim arrMarks(0 To iii) Equally Long ' Make full the array with random numbers Dim i As Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = v * Rnd Next i ' ALL VALUES Prepare TO Naught Erase arrMarks ' Print out the values - there are all now zip Debug.Impress "Location", "Value" For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i End Sub
We will now try the same example with a dynamic. Subsequently we apply Erase all the locations in the array take been deleted. We need to apply ReDim if we wish to use the assortment again.
If nosotros try to admission members of this assortment we will go a "Subscript out of Range" error:
' https://excelmacromastery.com/ Public Sub EraseDynamic() ' Declare array Dim arrMarks() As Long ReDim arrMarks(0 To 3) ' Fill up the array with random numbers Dim i Every bit Long For i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Adjacent i ' arrMarks is now deallocated. No locations exist. Erase arrMarks Cease Sub
Increasing the length of the VBA Array
If we use ReDim on an existing array, then the array and its contents will be deleted.
In the following example, the second ReDim argument volition create a completely new array. The original array and its contents will be deleted.
' https://excelmacromastery.com/ Sub UsingRedim() Dim arr() As String ' Set array to be slots 0 to 2 ReDim arr(0 To two) arr(0) = "Apple" ' Array with apple is now deleted ReDim arr(0 To 3) End Sub
If we want to extend the length of an assortment without losing the contents, we can utilise the Preserve keyword.
When we utilize Redim Preserve the new array must start at the same starting dimension due east.thousand.
We cannot Preserve from (0 to ii) to (ane to 3) or to (2 to 10) equally they are dissimilar starting dimensions.
In the following code nosotros create an array using ReDim and then fill the array with types of fruit.
We and then use Preserve to extend the length of the array so we don't lose the original contents:
' https://excelmacromastery.com/ Sub UsingRedimPreserve() Dim arr() As String ' Prepare array to be slots 0 to ane ReDim arr(0 To ii) arr(0) = "Apple tree" arr(1) = "Orangish" arr(2) = "Pear" ' Reset the length and keep original contents ReDim Preserve arr(0 To 5) Terminate Sub
Yous tin can see from the screenshots below, that the original contents of the array have been "Preserved".
Before ReDim Preserve
Later on ReDim Preserve
Word of Caution: In most cases, you shouldn't demand to resize an array like we accept washed in this section. If y'all are resizing an array multiple times then you may want to consider using a Drove.
Using Preserve with Two-Dimensional Arrays
Preserve only works with the upper bound of an array.
For example, if you have a two-dimensional assortment you tin can only preserve the 2d dimension every bit this example shows:
' https://excelmacromastery.com/ Sub Preserve2D() Dim arr() Every bit Long ' Set the starting length ReDim arr(1 To 2, 1 To v) ' Alter the length of the upper dimension ReDim Preserve arr(1 To 2, 1 To x) End Sub
If we try to use Preserve on a lower spring we will become the "Subscript out of range" error.
In the following code we use Preserve on the first dimension. Running this code will requite the "Subscript out of range" error:
' https://excelmacromastery.com/ Sub Preserve2DError() Dim arr() Every bit Long ' Set the starting length ReDim arr(1 To 2, 1 To 5) ' "Subscript out of Range" error ReDim Preserve arr(1 To v, ane To 5) Stop Sub
When nosotros read from a range to an array, information technology automatically creates a two-dimensional array, fifty-fifty if we have only 1 column.
The same Preserve rules apply. We can only utilise Preserve on the upper leap as this example shows:
' https://excelmacromastery.com/ Sub Preserve2DRange() Dim arr As Variant ' Assign a range to an array arr = Sheet1.Range("A1:A5").Value ' Preserve will work on the upper jump simply ReDim Preserve arr(ane To 5, i To 7) End Sub
Sorting the VBA Array
There is no function in VBA for sorting an array. We tin sort the worksheet cells but this could be boring if there is a lot of data.
The QuickSort function below can be used to sort an assortment.
' https://excelmacromastery.com/ Sub QuickSort(arr As Variant, offset As Long, last Equally Long) Dim vCentreVal As Variant, vTemp As Variant Dim lTempLow As Long Dim lTempHi As Long lTempLow = first lTempHi = terminal vCentreVal = arr((starting time + final) \ 2) Do While lTempLow <= lTempHi Do While arr(lTempLow) < vCentreVal And lTempLow < terminal lTempLow = lTempLow + 1 Loop Practise While vCentreVal < arr(lTempHi) And lTempHi > first lTempHi = lTempHi - ane Loop If lTempLow <= lTempHi And then ' Swap values vTemp = arr(lTempLow) arr(lTempLow) = arr(lTempHi) arr(lTempHi) = vTemp ' Motility to adjacent positions lTempLow = lTempLow + 1 lTempHi = lTempHi - 1 Cease If Loop If beginning < lTempHi Then QuickSort arr, first, lTempHi If lTempLow < last So QuickSort arr, lTempLow, concluding Cease Sub
Yous tin use this part like this:
' https://excelmacromastery.com/ Sub TestSort() ' Create temp array Dim arr() Equally Variant arr = Array("Banana", "Melon", "Peach", "Plum", "Apple") ' Sort assortment QuickSort arr, LBound(arr), UBound(arr) ' Print arr to Immediate Window(Ctrl + M) Dim i As Long For i = LBound(arr) To UBound(arr) Debug.Print arr(i) Adjacent i End Sub
Passing the VBA Array to a Sub
Sometimes you lot will demand to pass an array to a procedure. You declare the parameter using parenthesis like to how you declare a dynamic array.
Passing to the procedure using ByRef means you are passing a reference of the array. So if you change the array in the procedure information technology will be inverse when you render.
Note: When you lot employ an array as a parameter information technology cannot use ByVal, it must use ByRef. You can pass the array using ByVal making the parameter a variant.
' https://excelmacromastery.com/ ' Passes array to a Function Public Sub PassToProc() Dim arr(0 To 5) Equally Cord ' Pass the array to part UseArray arr End Sub Public Office UseArray(ByRef arr() As String) ' Use array Debug.Print UBound(arr) End Function
Returning the VBA Array from a Office
It is important to go on the post-obit in mind. If you desire to alter an existing array in a procedure then you should laissez passer it as a parameter using ByRef(see last section). You do non need to return the array from the process.
The main reason for returning an array is when you use the procedure to create a new i. In this instance you lot assign the return array to an array in the caller. This assortment cannot be already allocated. In other words you must employ a dynamic assortment that has not been allocated.
The post-obit examples show this
' https://excelmacromastery.com/ Public Sub TestArray() ' Declare dynamic array - not allocated Dim arr() Equally String ' Return new array arr = GetArray Stop Sub Public Office GetArray() Equally Cord() ' Create and classify new array Dim arr(0 To five) As Cord ' Render array GetArray = arr End Office
Using a Two-Dimensional VBA Array
The arrays we have been looking at and so far have been one-dimensional arrays. This means the arrays are 1 list of items.
A 2-dimensional array is substantially a listing of lists. If you lot retrieve of a single spreadsheet row as a single dimension then more than i column is ii dimensional. In fact a spreadsheet is the equivalent of a ii-dimensional array. It has 2 dimensions – rows and columns.
1 small matter to note is that Excel treats a one-dimensional array as a row if you write it to a spreadsheet. In other words, the array arr(1 to 5) is equivalent to arr(1 to ane, one to 5) when writing values to the spreadsheet.
The following image shows two groups of data. The start is a one-dimensional layout and the 2d is two dimensional.
To admission an detail in the first gear up of data(1 dimensional) all you demand to practice is give the row e.k. 1,ii, iii or 4.
For the second set of data (ii-dimensional), you need to give the row AND the column. So yous can think of 1 dimensional being multiple columns and one row and two-dimensional as being multiple rows and multiple columns.
Note: It is possible to have more than two dimensions in an array. It is rarely required. If you are solving a problem using a iii+ dimensional array and so there probably is a better mode to do it.
You lot declare a 2-dimensional array as follows:
Dim ArrayMarks(0 To two,0 To 3) Equally Long
The following case creates a random value for each item in the array and the prints the values to the Immediate Window:
' https://excelmacromastery.com/ Public Sub TwoDimArray() ' Declare a two dimensional array Dim arrMarks(0 To 3, 0 To two) Equally String ' Fill the array with text made up of i and j values Dim i As Long, j Every bit Long For i = LBound(arrMarks) To UBound(arrMarks) For j = LBound(arrMarks, 2) To UBound(arrMarks, 2) arrMarks(i, j) = CStr(i) & ":" & CStr(j) Next j Side by side i ' Print the values in the array to the Firsthand Window Debug.Print "i", "j", "Value" For i = LBound(arrMarks) To UBound(arrMarks) For j = LBound(arrMarks, 2) To UBound(arrMarks, ii) Debug.Print i, j, arrMarks(i, j) Next j Adjacent i End Sub
Yous tin can run across that we employ a second For loop within the beginning loop to access all the items.
The output of the example looks like this:
How this Macro works is equally follows:
- Enters the i loop
- i is ready to 0
- Entersj loop
- j is set to 0
- j is set to 1
- j is set to ii
- Go out j loop
- i is set to 1
- j is prepare to 0
- j is set to i
- j is set to 2
- And so on until i=3 and j=two
Y'all may notice that LBound and UBound have a 2nd statement with the value 2. This specifies that it is the upper or lower jump of the second dimension. That is the start and end location for j. The default value 1 which is why nosotros exercise not demand to specify it for the i loop.
Using the For Each Loop
Using a For Each is neater to utilise when reading from an array.
Let'due south take the code from in a higher place that writes out the two-dimensional array
' Using For loop needs two loops Debug.Impress "i", "j", "Value" For i = LBound(arrMarks) To UBound(arrMarks) For j = LBound(arrMarks, two) To UBound(arrMarks, ii) Debug.Print i, j, arrMarks(i, j) Adjacent j Side by side i
At present let's rewrite it using a For each loop. You can see we merely demand one loop and so information technology is much easier to write:
' Using For Each requires just one loop Debug.Print "Value" Dim mark Every bit Variant For Each mark In arrMarks Debug.Print mark Next marking
Using the For Each loop gives us the array in one order only – from LBound to UBound. Most of the time this is all you need.
Reading from a Range to the VBA Assortment
If you accept read my previous post on Cells and Ranges then you will know that VBA has an extremely efficient style of reading from a Range of Cells to an Array and vice versa
' https://excelmacromastery.com/ Public Sub ReadToArray() ' Declare dynamic assortment Dim StudentMarks As Variant ' Read values into assortment from first row StudentMarks = Range("A1:Z1").Value ' Write the values dorsum to the third row Range("A3:Z3").Value = StudentMarks Cease Sub
The dynamic array created in this example will be a ii dimensional array. Every bit you lot can see we can read from an unabridged range of cells to an array in merely one line.
The next instance volition read the sample student data below from C3:E6 of Sheet1 and print them to the Immediate Window:
' https://excelmacromastery.com/ Public Sub ReadAndDisplay() ' Get Range Dim rg As Range Set rg = ThisWorkbook.Worksheets("Sheet1").Range("C3:E6") ' Create dynamic assortment Dim StudentMarks As Variant ' Read values into array from sheet1 StudentMarks = rg.Value ' Print the array values Debug.Print "i", "j", "Value" Dim i As Long, j As Long For i = LBound(StudentMarks) To UBound(StudentMarks) For j = LBound(StudentMarks, two) To UBound(StudentMarks, 2) Debug.Print i, j, StudentMarks(i, j) Next j Side by side i End Sub
Sample Student data
Output from sample data
As you can come across the first dimension(accessed using i) of the array is a row and the second is a column. To demonstrate this have a look at the value 44 in E4 of the sample data. This value is in row ii column 3 of our information. You tin can see that 44 is stored in the array at StudentMarks(2,3).
You lot can meet more than well-nigh using arrays with ranges in this YouTube video
How To Make Your Macros Run at Super Speed
If your macros are running very slow then you may find this department very helpful. Especially if you are dealing with big amounts of data. The following is a very well-kept secret in VBA
Updating values in arrays is exponentially faster than updating values in cells.
In the last section, you saw how we can easily read from a grouping of cells to an array and vice versa. If nosotros are updating a lot of values then nosotros tin can practise the following:
ane. Copy the data from the cells to an array.
two. Change the data in the assortment.
3. Copy the updated data from the array back to the cells.
For example, the following lawmaking would be much faster than the code beneath it:
' https://excelmacromastery.com/ Public Sub ReadToArray() ' Read values into array from showtime row Dim StudentMarks Every bit Variant StudentMarks = Range("A1:Z20000").Value Dim i As Long For i = LBound(StudentMarks) To UBound(StudentMarks) ' Update marks here StudentMarks(i, 1) = StudentMarks(i, ane) * 2 '... Next i ' Write the new values back to the worksheet Range("A1:Z20000").Value = StudentMarks End Sub
' https://excelmacromastery.com/ Sub UsingCellsToUpdate() Dim c As Variant For Each c In Range("A1:Z20000") c.Value = ' Update values here Adjacent c End Sub
Assigning from one set of cells to another is also much faster than using Copy and Paste:
' Assigning - this is faster Range("A1:A10").Value = Range("B1:B10").Value ' Copy Paste - this is slower Range("B1:B1").Re-create Destination:=Range("A1:A10")
The following comments are from two readers who used arrays to speed upwardly their macros
"A couple of my projects have gone from most impossible and long to run into almost as well easy and a reduction in time to run from 10:1." – Dane
"I study I did took near 3 hours to run when accessing the cells straight — 5 minutes with arrays" – Jim
You can encounter more about the speed of Arrays compared to other methods in this YouTube video.
To see a comparison between Find, Friction match and Arrays it is worth checking out this post past Charles Williams.
Conclusion
The following is a summary of the main points of this post
- Arrays are an efficient fashion of storing a list of items of the same type.
- You can access an array item directly using the number of the location which is known equally the subscript or alphabetize.
- The mutual mistake "Subscript out of Range" is caused by accessing a location that does non be.
- In that location are 2 types of arrays: Static and Dynamic.
- Static is used when the length of the array is always the aforementioned.
- Dynamic arrays allow y'all to determine the length of an assortment at run time.
- LBound and UBound provide a prophylactic way of find the smallest and largest subscripts of the array.
- The basic array is one dimensional. You can also take multidimensional arrays.
- You tin simply laissez passer an array to a process using ByRef. Yous practice this like this: ByRef arr() as long.
- You can render an assortment from a office but the array, it is assigned to, must not be currently allocated.
- A worksheet with its rows and columns is essentially a two-dimensional array.
- You can read directly from a worksheet range into a two-dimensional array in just one line of lawmaking.
- You tin also write from a two-dimensional array to a range in merely ane line of code.
What's Next?
Complimentary VBA Tutorial If y'all are new to VBA or you want to acuminate your existing VBA skills and then why not effort The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA grooming webinars.
(NOTE: Planning to build or manage a VBA Application? Acquire how to build x Excel VBA applications from scratch.)
Source: https://excelmacromastery.com/excel-vba-array/
Post a Comment for "You Can Assign the Contents of One Array to Another by Using ________."