Arrays Can Make It Easier to Manipulate Data But Is That

Okay, so arrays can make it easier to manipulate data and get information from it—but is that all they're good for? NO! Arrays are so powerful because they can actually make the code run faster!

Typically, if there are columns of data to average, as in the previous example, your first thought might be for the following: Sub SlowAverage()

Dim myCount As Integer, LastRow As Integer

LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row

For myCount = 2 To LastRow With Worksheets("Sheet1")

WorksheetFunction.Average(Cells(myCount, 2), Cells(myCount, 3))

End With Next myCount

End Sub

Although this works fine, the program is having to look at each row of the sheet individually, get the data, do the calculation, and place it in the correct column. Wouldn't it be easier to grab all the data at one time, then do the calculations and place it back on the sheet? Also, with the slower code, you need to know which columns on the sheet to manipulate (Columns 2 and 3 in our example). With an array, you need to know only what element of the array.

To make this even more useful, rather than use an address range to fill the array, you could use a named range. With a named range in an array, it really doesn't matter where on the sheet the range is.

Instead of:

myArray = Range("B2:C17")

myArray = Range("myData")

Whereas with the slow method you need to know where myData is so you can return the correct columns, with an array all you need to know is that you want the first and second columns.

0 0

Post a comment