Create an array in VBA from an array formula in Excel (From Name Manager)

Question

I am trying to create an array in VBA from a dynamic array formula in Excel. Ideally the Excel formula would be kept in the Name Manager, but I can accept if the formula is stored in a cell.

The ultimate goal is to use this array to match blocks of cells based on certain conditions (with a For loop) to determine which block of rows to delete from the Excel sheet. It has to be dynamic because I don't know how many rows will be in the sheet or which of those rows needs to be deleted until runtime.

A link to the Excel file here: https://www.dropbox.com/s/rz1qej15afht6bx/BRE5560_RFQ.xlsx?dl=0

I've been trying to get this to work for days and have tried many different options but I can't get it to work. What's maddening is it works just fine for another formula which returns an array of Doubles, but this array of Strings defies me.

Function CreateRFQEmail(sRFQFileName As String)
Dim arrRows() As Variant
Dim arrRFQs2() As Variant 

objExcel.Workbooks.Open (sRFQFileName)
Set sht = objExcel.ActiveWorkbook.Worksheets(2)

'This creates the array of Doubles without issue:
   arrRows = sht.Evaluate(sht.Names.Item("Quote!vbaRows").Value)

'The best result I have with this line is to have an array of Strings in VBA with the 
'correct number of elements, but the first element from Excel is repeated for each 
'element in the array:
   arrRFQs2 = sht.Evaluate(sht.Names.Item("Quote!vbaRFQs").Value)

End Function

I have also tried using:

arrRFQs2 = sht.Names.Item("Quote!vbaRFQs").Value   'Error - Can't assign to array
arrRFQs2 = sht.Names("Quote!vbaRFQs").Value        'Error - Can't assign to array
arrRFQs2 = sht.Range("BY5").Value                  'Error - Type mismatch

I have tried several other options, but can't remember them all off the top of my head. (I'll keep the post updated if anyone suggests something I've already tried) The most common error was type mismatch even though I could confirm the formula side of the line (i.e. sht.Range("BY5").Value) was a String or a Range

The Excel formulas stored in the Name Manager are:

'vbaRFQs:
=TOROW(IF(Quote!vbaRows,INDEX(Quote!$A:$A,Quote!vbaRows),""))
'The first IF is needed to get VBA to return the correct number of elements, 
'without it I just get an array with a single element even though in Excel the 
'formula returns an array correctly

'vbaRows:
=IFERROR(TOROW(SMALL(IF(Quote!$A:$A<>"",ROW(Quote!$A:$A),""),ROW(OFFSET(Quote!$A:$A,0,0,COUNTA(Quote!$A:$A),1)))),"")

I also tried putting them in a cell instead of the Name Manager with no change.

I have tried making vbaRFQs one long formula instead of using vbaRows inside of it, but this only works if I remove the first IF (Because it will be too long for Evaluate) and then I only get a single element array in VBA.

If I use an Index formula for vbaRFQs then VBA sees the formula as a Range type (even the the result of the formula is a String) and gives me a type mismatch error.

score 0 · Accepted Answer · answered Dec 19 '22 at 21:23

Populate Array Using Named Formula

I think the formula is too complicated to be used with Evaluate.
Here's a workaround that uses the values from the 1st column and the information (rows) in the Rows array.

Sub CreateRFQEmailTEST()
    CreateRFQEmail "C:\Test\BRE5560_RFQ.xlsx"
End Sub

Sub CreateRFQEmail(ByVal sRFQFilePath As String)
    
    Dim swb As Workbook: Set swb = Workbooks.Open(sRFQFilePath)
    Dim sws As Worksheet: Set sws = swb.Sheets("Quote")
    Dim sData(): sData = sws.UsedRange.Columns(1).Value
    
    ' Get Rows.
    Dim ArrRows(): ArrRows = sws.Evaluate("vbaRows")
    
    ' Get RQFs.
    
    Dim ArrRFQs(): ReDim ArrRFQs(1 To UBound(ArrRows))
    
    Dim rItem, dc As Long
    
    For Each rItem In ArrRows
        dc = dc + 1
        ArrRFQs(dc) = sData(rItem, 1)
    Next rItem
            
    ' Print both.
    Debug.Print Join(ArrRows, ",")
    Debug.Print Join(ArrRFQs, ",")
    
End Sub

Thank you for your help. I ended up coming to the same conclusion and did something very similar: since I had the row numbers in an array I used those to pull the values of the cells and put them in a separate array, all from VBA. It's not ideal, as it complicates some other processes, but it was a usable workaround. — Raymond Evans, Jan 03 '23 at 20:02

Create an array in VBA from an array formula in Excel (From Name Manager)

1 Answers1

Populate Array Using Named Formula