Excel VBA - User Defined Function in Condictional Formatting

Question

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".

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

UDF Codes is as below:

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

Did you put the code in a public module or private worksheet code sheet? — user4039065
Which code were you referring to? For the UDF code, it's in public module, otherwise it won't be visible to Excel users at all. The interesting thing is - Excel allows bogus function name in conditional formatting formula (.e.g "=XXX($A$1)"). However Excel complains about a valid UDF. — Helloguys
Very similar to this one link. My UDF is in a module. The function is declared as public function. But I don't know how to declared the module as public (if there's such as thing called "public module"). :) — Helloguys
module code sheets are public by default; worksheet code sheets are private by nature. — user4039065
The UDF just needs to be in a Regular Module in the Same workbook. And your formula shoud return True or False — Ron Rosenfeld

EvR EvR · Accepted Answer · 2018-05-18T13:19:11

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

Excel VBA - User Defined Function in Condictional Formatting

1 Answers