Search code examples
arraysvbaexcellistobject

Loop through table columns and store max or min values in array variable

I am trying to loop through a table in excel using VBA and store the maximum value of each column in an array variable for later use in the program.

I have taken a number of approaches similar to the code snippet below without success. For reference: mainTable is a Public ListObject variable defined earlier in the script and mainCols is a Pubic Long variable, also previously defined, which stores the width of mainTable:

Dim maxVals() As Long
ReDim maxVals(mainCols)

For x = 0 To mainCols - 1
maxVals(x) = mainTable.ListColumns(x + 1).TotalsCalculation = xlTotalsCalculationMax
Next x

The code above executes without error, but always returns 0

It is important that I don't hard code the columns/table location so that users can copy/paste a dataset of varying dimensions and run the script without errors. Also, assume that the user could run this on a dataset with whatever column headers they want.

Solution

  • Your code simply changes the calculation used in the Totals row of the table - it doesn't actually return the max value. You could use:

    Dim maxVals() As Long
ReDim maxVals(mainCols)

For x = 0 To mainCols - 1
maxVals(x) = WorksheetFunction.Max(mainTable.ListColumns(x + 1).Range)
Next x