Search code examples
excelvbaspill-range

Trouble passing spilled range to VBA - Only getting Application-defined or object-defined error

I'm having an issue passing a Spill array to VBA. The range is defined by as name "ListCurJobs" and the equation

=UNIQUE(FILTER(CtrlSht!$B:$B,CtrlSht!$B:$B<>""))

Testing the named range on the worksheet using "=ListCurJobs" returns the spilled range as expected.

I am trying to pass this into VBA using

Sub ListJobs
Dim listCurJobs() As Variant

n = Worksheets("CtrlSht").Range("listCurJobs").Rows.Count

ReDim listCurJobs(n, 1)

listCurJobs = Worksheets("CtrlSht").Range("listCurJobs")

End Sub

This works fine for standard arrays, but with the spill array I get the error : Application-defined or object-defined error.

EDIT: Follow up - I managed to fix this by changing the named range listCurJobs to use an Offset function

=OFFSET(CtrlSht!$E$1,0,0,CtrlSht!$D$1)

where CtrlSht!$D$1 is counting all items in E via

=COUNTA(E1#)

and E1 has the spill function that I was trying to use in the named range i.e.

=UNIQUE(FILTER(CtrlSht!$B:$B,CtrlSht!$B:$B<>""))

Whatever is causing the original error, these additional steps seem to avoid it.

Solution

  • I managed to fix this by changing the named range listCurJobs to use an Offset function:

    =OFFSET(CtrlSht!$E$1,0,0,CtrlSht!$D$1)

    Where CtrlSht!$D$1 is counting all items in E via:

    =COUNTA(E1#)

    And E1 has the spill function that I was trying to use in the named range; i.e.,

    =UNIQUE(FILTER(CtrlSht!$B:$B,CtrlSht!$B:$B<>""))

    Whatever is causing the original error, these additional steps seem to avoid it.