How to pass non-contiguous cells to Excel UDF

Question

in myUDF, I can reference a range of cells like "A1:A12", but how can I reference non-contiguous cells like "A1,B4,H3" etc.
I use ExcelDNA, the parameter type is object but it seems it will take string, and a range of cells, not non-contiguous cells

[ExcelArgument(AllowReference = true, Name = "Relations", Description = "a set of relations")]object rels

@Tim, I tried to use object, if I pass as string as "A1;B2" or "A1,B2", when I click formula bar, Excel does not recognize them, simply treat them as a string. I want something like built-in function SUM, when you select different cells and click formula bar, all referenced cells are highlighted and you are able to select different cells. Once you select a cell, formula changes accordingly. — toosensitive, Jan 25 '13 at 17:37

score 3 · Answer 1 · answered Jul 24 '14 at 18:53

It sounds like you're entering the formula onto the worksheet, along with its parameters, from your code, and you want users to then be able to edit the formula normally in Excel's formula bar. Is that correct?

If so, enclose the parameter in parens. For example, for a UDF like this...

Public Function MyUDF(my_param As Range)
    'concatenate all cell values in a non-contiguous range:
    Dim rgCell As Range, rgArea As Range
    For Each rgArea In my_param.Areas
        For Each rgCell In rgArea
            MyUDF = MyUDF & CStr(rgCell.Value)
        Next rgCell
    Next rgArea
End Function

...enter it in the worksheet cell like this:

=MyUDF((A1,A3,A7:A11,C8:E10))

Note the extra set of parens compared to using a built-in function like SUM.

BTW as you may already know, when looping through a non-contiguous range you have to loop through the areas of the range, then loop through the cells in each area; looping through the cells in the range only gives you the cells in the first area.

How to pass non-contiguous cells to Excel UDF

1 Answers1