vba - How to write Excel UDF so that it can handle being passed a single cell, or array of cells - Stack Overflow

admin管理员组

文章数量:1404924

Background

I wrote the below UDF to check whether a cell's contents is valid (i.e. if a cell's data validation is set to a list of A,B,C. Returns TRUE if the cell is "A", "B" or "C" otherwise FALSE)

'Checks whether the contents of a given cell meets the data validation rules
Function IsValid(Cell) As Variant
    IsValid = Cell.Validation.Value
End Function

However, I then wanted to use this to drive a FILTER (which expects an array of values and an array of true/false)

Overrides / Parameters sets in VBA?

Is it possible to adapt this UDF so that it will accept both:

• Cell As Variant and return single True/False
• Cell AS Variant() and return array of True/False

Background

I wrote the below UDF to check whether a cell's contents is valid (i.e. if a cell's data validation is set to a list of A,B,C. Returns TRUE if the cell is "A", "B" or "C" otherwise FALSE)

'Checks whether the contents of a given cell meets the data validation rules
Function IsValid(Cell) As Variant
    IsValid = Cell.Validation.Value
End Function

However, I then wanted to use this to drive a FILTER (which expects an array of values and an array of true/false)

Overrides / Parameters sets in VBA?

Is it possible to adapt this UDF so that it will accept both:

• Cell As Variant and return single True/False
• Cell AS Variant() and return array of True/False

• excel
• vba
• user-defined-functions

Share Improve this question Follow edited Mar 9 at 8:02 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Mar 9 at 4:43 MartinMartin 41644 silver badges1414 bronze badges 4

• You already have a signature that supports that. – GSerg Commented Mar 9 at 8:34
• You could use As Range - that will accept a single cell or multiples... – braX Commented Mar 9 at 8:42
• You also need to specify if the validation is validating all cells or any cells. If it's all cells, then you check until you find a FASLE return. If it's any cell, then you check until you find a TRUE return. – Frank Ball Commented Mar 9 at 17:50
• Would it be easier to use your own validation? eg =NOT(ISERROR(MATCH(TestRange,ValidRange,0))). You could point ValidRange to the same reference as your Data Validation. If this is the only thing you are using VBA for then it would mean you could have an .xlsx rather than a macro-enabled sheet. You could create a LAMBDA if using often. You could NAME the ValidRange which would mean you only had to update it in one place if needed. – DS_London Commented Mar 10 at 12:33

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

If the data is in a range, then you need to check each cell in the loop, for a column like this:

Function IsValid(rng As Range) 
   Dim i As Long, n As Long, v As Variant
   n = rng.Rows.Count
   ReDim v(1 To n, 1 To 1)
   For i = 1 To n
       v(i, 1) = rng(i).Validation.Value
   Next i
   IsValid = v
End Function 

The following function should also cover cases where the range consists of more than one column resp. covers a single cell, a single column, a single row or multi-column, multi-row ranges. It will not cover discontinuous ranges.

PS Changed.CountLarge to .Count. This should be sufficient as mentioned in the comments.

Function IsValid(rng As Range)
    Dim i As Long, j As Long, rDat As Variant
   
    Dim rowCount As Long
    Dim colCount As Long
    rowCount = rng.Rows.Count
    colCount = rng.Columns.Count
   
    ReDim rDat(1 To rowCount, 1 To colCount)
   
    For i = 1 To rowCount
        For j = 1 To colCount
            rDat(i, j) = rng.Cells(i, j).Validation.Value
        Next
    Next
    IsValid = rDat
End Function

本文标签：