Excel VBA Introduction Part 25 - Arrays
Why Stack Overflow recommends it
Stack Overflow users link it when answering VBA questions where the asker needs to understand arrays, ReDim, multi-dimensional arrays, or reading ranges into arrays for speed.
Recommended by 3 people across 8 different months, 2016 to 2019.
Why Code Review Stack Exchange recommends it
Code Review Stack Exchange answerers point people to it when reviewing slow Excel VBA, since reading worksheet data into an array and writing it back is the main speed fix they suggest.
Recommended by 3 people across 5 different months, 2016 to 2019.
What it is
Part 25 of Wise Owl's Excel VBA Introduction series, presented by Andrew Gould. It covers declaring and using fixed-size arrays, finding lower and upper bounds, then dynamic arrays, multi-dimensional arrays, resizing with ReDim, transposing, and using arrays to speed up calculations.
What people said
Strong rec user109261 · 2016-09 · Code Review Stack Exchange · on “Compare and match results in two two-dimensional arrays in Excel VBA”
@IainSaunders I recommend watching Excel VBA Introduction Part 25 - Arrays [this video] . Even though I've been use the VBA for 15 years now, I learned a lot from watching the whole 45 episode series.
Recommends user6432984 · 2016-07 · Stack Overflow · on “How to create dynamic variable names VBA”
This will get you started. But before you start I recommend watching these WiseOwlTutorials tutorial on Youtube: Selecting Cells (Range, Cells, Activecell, End, Offset) [LINK] Worksheets, Charts and Sheets [LINK] Variables [LINK] Arrays [this video] Dim i, x As Integer x = Range("J4").Value Dim Team() As Integer Dim Manager() As String ReDim Team(1 To x) As Integer ReDim Manager(1 To x) As String Range("A3").Select For i = 1 To x Team(i) = i Next
Recommends user6432984 · 2016-09 · Stack Overflow · on “VBA only - Is there an easier way to concatenate array values based on their position?”
You should watch "The Wise Owl Tutorials - An Introduction to VBA" on Youtube here is the relavent one: Excel VBA Introduction Part 25 - Arrays [this video]
Recommends user6432984 · 2016-11 · Stack Overflow · on “Store range of cells to an Array”
Reference MSDN: ReDim Statement (Visual Basic) [LINK] When you Redim the array you are erasing all the values. Use ReDim Preserve to resize the last dimension of the Array and still preserve the values of the array. After the dynamic array has been allocated(the first time Redim is used) you can only resize the last dimension of a array and you cannot change the number of dimensions. In your code you are trying to convert a 2 Dimensional array to a 1 Dimensional array, you can not do that. You should watch this complete series: Excel VBA Introduction [LINK] . This is the relevant video: Excel VBA Introduction Part 25 - Arrays [this video]
Recommends user6432984 · 2016-11 · Stack Overflow · on “Update table cells based on criteria using an array”
Your should watch this series Excel VBA Introduction [LINK] . This is the relevant video Excel VBA Introduction Part 25 - Arrays [this video]
Recommends user6432984 · 2016-12 · Stack Overflow · on “VBA to correctly loop through an array”
Reading arrays is somewhat faster than reading cell values. The real performance gain is writing the data back to the worksheet. As always I recommend watching Excel VBA Introduction [LINK] on Youtube. This is the relevant video: Part 25 - Arrays [this video] Sub RepeatData() Dim Data As Variant, Data1 As Variant Dim x As Long, x1 As Long, x2 As Long, y As Long Data = Range("A2:G2", Range("B" & Rows.Count).End(xlUp)) ReDim Data1(1 To UBound(Data, 1) * 4, 1 To UBound(Data, 2)) For x = 1 To UBound(Data, 1) For x1 = 1 To 4 x2 = x2 + 1 For y = 1 To UBound(Data, 2) Data1(x2, y) = Data(x, y) Next Next Next Range("A2:G2").Resize(UBound(Data1, 1)).Value = Data1 End Sub
Recommends user6432984 · 2017-07 · Stack Overflow · on “Assigning Ranges to Array”
You should watch Excel VBA Introduction [LINK] . Part 25 - Arrays [this video] and Part 39 - Dictionaries [LINK] are the most relevant.
Recommends user6432984 · 2017-10 · Stack Overflow · on “Change values from - to + and also copy them to a new worksheet”
@OneaboveAll experience is the best teacher. After watching these videos Excel VBA Introduction Part 25 - Arrays - YouTube [this video] and Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) - YouTube [LINK] you will be able to find your own solution.
Recommends user109261 · 2017-12 · Code Review Stack Exchange · on “Splitting date and time”
Turning off Application.ScreenUpdating and Application.Calculation will greatly increase the speed. But reading the data into an Array, modifying it and writing the Array back to the Worksheet is the real key to speed. Recommended viewing: Excel VBA Introduction Part 20.2 - Application Events [LINK] , Excel VBA Introduction Part 25 - Arrays [this video] Sub CompareTime() 'Optimize Setting for faster data writing With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With Dim Target As Range, ws As Worksheet Dim x As Long Dim results As Variant Set ws = ActiveSheet 'Find last data point With ws .Columns("B:C").Insert .Columns("B:B").NumberFormat = "mm
Recommends TinMan · 2018-11 · Stack Overflow · on “Excel VBA - Subscript out of range”
Arr3 is never The error is caused because Arr3 was never initialized as an array. Arr1(i) will also throw an error because Arr1 is a 2 dimensional array; use Arr1(i, 1) instead. ReDim Arr3(LBound(Arr1) To UBound(Arr1)) For i = LBound(Arr1) To UBound(Arr1) Arr3(i) = Arr1(i, 1) + Arr2(i, 1) Next Dim W1, W2, R1, R2, R3, R4, R5 As Double In the declaration above R5 's datatype is Double the rest are Variants. [LINK] Here are a few videos that will help: Excel VBA Introduction Part 3 - What to do When Things Go Wrong (Errors and Debugging) [LINK] , Excel VBA Introduction Part 34 - Type Declarations (User-Defined Types) [LINK] and Excel VBA Introduction Part 25 - Arrays [this video]