VBA evaluation of defined name (involving INDEX)

chrispsn

New Member
Joined
Jun 23, 2014
Messages
6
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:

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).
All elements can be obtained when entered over multiple cells using 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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi chrispsn,

Understanding that you'd prefer to avoid more VBA if possible, I'd suggest you use a fairly simple User Defined Function (UDF) in lieu of a formula-defined-names-based approach.

Here is an example from the Newton Excel Bach website:
Retrieving unique values from a range or array … | Newton Excel Bach, not (just) an Excel Blog

Paste this code into a Standard Code Module in your workbook....
Code:
Function Unique(DRange As Variant) As Variant
 
 Dim Dict As Object
 Dim i As Long, j As Long, NumRows As Long, NumCols As Long
 
 'Convert range to array and count rows and columns
 If TypeName(DRange) = "Range" Then DRange = DRange.Value2
 NumRows = UBound(DRange)
 NumCols = UBound(DRange, 2)
 
 'put unique data elements in a dictionay
 Set Dict = CreateObject("Scripting.Dictionary")
 For i = 1 To NumCols
    For j = 1 To NumRows
      Dict(DRange(j, i)) = 1
    Next j
 Next i
 
 'Dict.Keys() is a Variant array of the unique values in DRange
 'which can be written directly to the spreadsheet
 'but transpose to a column array first
 
 Unique = WorksheetFunction.Transpose(Dict.keys)
 
End Function

With that in place you can use formulas like this in your workbook.
=INDEX(Unique(data),3)

...or this syntax in VBA code...
Code:
Sub Test()
   Dim vUnique As Variant
   vUnique = Unique(Range("Data"))
   MsgBox "The 3rd Item is: " & vUnique(3, 1)
End Sub

This eliminates the need for the other named ranges (at least for the purpose of getting unique items from your range "Data").
 

chrispsn

New Member
Joined
Jun 23, 2014
Messages
6
Thanks Jerry. I like your use of the Dictionary object to get the unique values in the array.

Some more background:

As a general technique, I want to be able to give INDEX an array of rows to pull via a 'spreadsheet-side' formula. That way, end users can specify the logic for which rows they want without resorting to writing VBA whenever their problem varies from that above.

It's OK - I ended up coding a VBA replacement for INDEX that:

- takes a data array, and an array containing the row numbers to pull
- returns an array containing just the rows specified.

I'll share it once it's fully tested.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,559
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could try:
Code:
Evaluate("INDEX(data_filtered,)")
 

chrispsn

New Member
Joined
Jun 23, 2014
Messages
6

ADVERTISEMENT

You could try:
Code:
Evaluate("INDEX(data_filtered,)")

Hi Rory, thanks for the suggestion - unfortunately that code pulls only the first element of the array.

I also tried evaluating "INDEX(data, get_these_rows)", skipping the data_filtered named formula - that gives the same result.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,559
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How exactly are you using and verifying the Evaluate statement?
 

chrispsn

New Member
Joined
Jun 23, 2014
Messages
6
How exactly are you using and verifying the Evaluate statement?

I'm verifying that only the first element is there in two different ways:

- dragging the relevant Evaluate statement into the Watch window, and
- trying a For Each through the array's elements, printing each using Debug.Print.

For other arrays, these methods (respectively) show each element in the Watch window, and print each element of the array.
 
Last edited:

Forum statistics

Threads
1,143,835
Messages
5,721,067
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top