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 singleTrue/False
Cell AS Variant()
and return array ofTrue/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 singleTrue/False
Cell AS Variant()
and return array ofTrue/False
2 Answers
Reset to default 3If 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
发布者:admin,转转请注明出处:http://www.yc00.com/questions/1744877794a4598651.html
As Range
- that will accept a single cell or multiples... – braX Commented Mar 9 at 8:42=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