CODEWITHSUNDEEP
c#excelexcel-udf
toosensitive
toosensitive

Reputation: 2375

How to pass non-contiguous cells to Excel UDF

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

Upvotes: 1

Views: 768

Answers (1)

Greg Lovern
Greg Lovern

Reputation: 977

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.

Upvotes: 3

Related Questions