Is it more efficient to extract an array column if it will be used more than once?

[JenniferMurphy]

I am working on a macro that will analyze data in a sheet table. To do that, it needs to calculate the mean and standard deviation of each of the data columns.

Here is the code I have now. It extracts each column from the overall table into arrNextCol and then calculates the mean and std dev from that.

Dim arrNextCol() As Variant:  ReDim arrNextCol(NumRows)
Dim Mean As Double
Dim StdDev As Double

For iCol = MinCols To NumCols   'Loop through the property columns
  With Application.WorksheetFunction
    arrNextCol = .Index(arrTableData, 0, iCol)  'Load data from the next column
    Mean = .Average(arrNextCol)
    StdDev = .StDev_S(arrNextCol)
  End With
Next iCol

Then it occurred to me that I really don't need that temporary array. I came up with this code. It extracts the column data twice.

Dim Mean As Double
Dim StdDev As Double

For iCol = MinCols To NumCols   'Loop through the property columns
  With Application.WorksheetFunction
    Mean = .Average(.Index(arrTableData, 0, iCol))
    StdDev = .StDev_S(.Index(arrTableData, 0, iCol))
  End With
Next iCol

Is there any significant difference in performance between the two methods?

Thanks

 

[RoryA]

Do you actually need the arrTableData array for something? I would have thought you might as well just refer to the table column range directly rather than using an array for the whole table and then Index to get a particular column.

 

[JenniferMurphy]

Do you actually need the arrTableData array for something? I would have thought you might as well just refer to the table column range directly rather than using an array for the whole table and then Index to get a particular column.

That's what I was doing originally. Then when I posted another question about the code in another thread, I was told that loading the array into memory in one operation is much faster than repeated referenced to sheet ranges. The code is also more compact and a little easier to read, at least for me.

 

[RoryA]

Although that is generally true, I'm not sure it's going to apply in this case.

 

[JenniferMurphy]

Although that is generally true, I'm not sure it's going to apply in this case.

I am pretty sure that it would be difficult to measure the difference in this application. The table could be as large as 50 x 40, but usually will be no larger than 20 x 10 and often a good bit smaller.

In my original code, I pulled each column from the sheet table one at a time, did the caculations, then wrote the result back out. Now I load the entire table all at once. Because the data is all in memory, the statements are simpler and easier for me to read.

So, unless there is a reason not to do it that way, I'll continue as is.

Thanks

 

[RoryA]

With data that size I don't think it will make any noticeable difference which way you approach it, so do whatever makes it easier to maintain.

 

[JenniferMurphy]

With data that size I don't think it will make any noticeable difference which way you approach it, so do whatever makes it easier to maintain.

Sounds like a plan. Thanks.