Excel VBA - 禁止格式化中的用户定义函数

I built a User Defined Function with VBA code and it works fine in Excel if I use it in a formula to populate cell values. However, I cannot use this function in conditional formatting. I'm getting message "This type of reference cannot be used in a Conditional Formatting formula".

我用VBA代码构建了一个用户自定义函数,如果我在公式中使用它来填充单元格值,它在Excel中工作正常。但是,我无法在条件格式中使用此功能。我收到消息“此类型的引用不能用于条件格式公式”。

I'm curious if any UDF can be used in conditional formatting? Or no UDF is allowed at all? Thanks!

我很好奇是否可以在条件格式中使用任何UDF?或者根本不允许UDF?谢谢!

UDF Codes is as below:

UDF代码如下:

Function isValidMAC(mac As String) As Boolean

Dim regex As New RegExp
Dim strPattern As String

' Exact 12 characters.  Valid characters are a-f or A-F or 0-9
strPattern = "^[a-fA-F0-9]{12}$"

With regex
    .Global = False
    .MultiLine = False
    .IgnoreCase = False
    .Pattern = strPattern
End With

isValidMAC = regex.Test(mac)

End Function

1 个解决方案

#1

As your UDF is not is the same workbook, but in your add-in, you will receive this message; As a workaround: use your UDF-call in a defined name and use this Defined Name in your conditional Formatting Formula

由于您的UDF不是同一个工作簿,但在您的加载项中,您将收到此消息;解决方法:在定义的名称中使用UDF调用,并在条件格式公式中使用此定义的名称