Excel experts,
I'm trying to turn the value of a named formula into a VBA array.
The defined name, data_filtered, holds all unique values appearing within a range:
data_filtered is read using this VBA code:
data_filtered depends on these named formulas:
get_these_rows:
data_numbers:
data_numbers_unique:
data is just a column of strings, with some duplicates.
Only the first item of data_filtered is returned when:
Testing item counts of each named formula with COUNTA shows the problem is probably with data_filtered, and thus with INDEX. It's probably related to how INDEX processes arrays passed to it via the row_num parameter (as briefly discussed here).
How can I get the contents of data_filtered into VBA, without writing its contents to the spreadsheet first? Happy to modify the defined names, but would prefer to avoid more VBA if possible.
I'm trying to turn the value of a named formula into a VBA array.
The defined name, data_filtered, holds all unique values appearing within a range:
Code:
=INDEX(data, get_these_rows)
data_filtered is read using this VBA code:
Code:
Evaluate("data_filtered")
data_filtered depends on these named formulas:
get_these_rows:
Code:
=SMALL(IF(FREQUENCY(MATCH(data, data, 0), data_numbers), data_numbers), data_numbers_unique)
data_numbers:
Code:
=ROW(data)-ROW(INDEX(data, 1))+1
data_numbers_unique:
Code:
=ROW(OFFSET(ws!$A$1, 0, 0, SUM(1/COUNTIF(data, data))))
data is just a column of strings, with some duplicates.
Only the first item of data_filtered is returned when:
- evalated using VBA
- entered into a single cell (Enter or Ctrl-Shift-Enter).
Testing item counts of each named formula with COUNTA shows the problem is probably with data_filtered, and thus with INDEX. It's probably related to how INDEX processes arrays passed to it via the row_num parameter (as briefly discussed here).
How can I get the contents of data_filtered into VBA, without writing its contents to the spreadsheet first? Happy to modify the defined names, but would prefer to avoid more VBA if possible.