CODEWITHSUNDEEP
arraysexcelvba
Carlos N
Carlos N

Reputation: 206

Displaying array (of indeterminate length) returned by VBA in Excel

I have an Excel VBA function that compares two ranges and returns the values of cells in range A not found in range B. I would now like to display the list of missing values in a different Excel worksheet. Ranges A and B are hundreds of cells long. The missing values from A in B will usually could be from 0 to a dozen or two. I'm not interested in just highlighting cells with conditional formatting.

The function is called multiple times in one sheet, creating a table. The output looks like this: Example Table

My current solution is to create an output range for each column with an array (Control Shift Enter) formula, that calls my function. {=MyFormula(range1, range2)}. With the parameter being different for each column's array formula.

Within the function I then set the output array to be the same dimension as the array formula range:

    With Application.Caller
        CallerRows = .Rows.Count
        CallerCols = .Columns.Count
    End With
    ReDim Result(1 To CallerRows)

As I fill out Result if only fill it to N-1 spaces with the result, and in the Nth space I put something like '4 more entries...'

Obviously this is somewhat awkward, in that data is lost. I could, of course, just expand the CSE range even more, but as such things go, if I make it 25 rows, tomorrow I'm sure to get an instance where 26 differences are found.

To my question:

I'm guessing the first is likely impossible, but I want to confirm.

So if I go with the second option, what is the best way of doing it while keeping a function call in a cell, rather than a "repopulate" button or the like.

For reasons beyond my control, I'm currently limited to Excel 2013 for this application.

Upvotes: 0

Views: 256

Answers (1)

jclasley
jclasley

Reputation: 678

I'm unfamiliar with your function, but I'm going to assume you've added a new sheet and have it blank and ready to accept values. You've said that your function returns an array so you could do the following. I'm going to assume two things:

  1. Your return array is called myArray
  2. Your new sheet is called mySheet
Dim myR as Range
Dim item as Variant
Set myR = mySheet.Cells(1, UBound(myArray))

For i = 0 to UBound(myArray)
    myR(i + 1).Value = myArray(i)
Next i

It really is that easy.

Upvotes: 1

Related Questions