vba - #NA Error Copy/Paste Excel In-cell Function

Question

I am randomly getting the #N/A error in Excel 2016 when I am manually copying and pasting individual cells (in the same column) that contain an in-cell custom function.

Cells A1, A3 and A10 contain an identical in-cell function (that works fine).

I want to replace cells A1, A3 and A10 with the value (results) of the in-cell function.

I use the standard copy and paste values operation on each individual cell, i.e. not a range selection. Should be easy, right?

The copy/paste values may work on cell A3, but not on A1 or A10.

Cell formatting is identical for all the cells, i.e. the ones that work as well as the ones that give the #N/A error. I am doing everything the same way on each of the cells.

If I repeat the operation on cells A1 and A10, sometimes it will work and other times it doesn't.

This is frustrating and a time killer. I can't afford to try to repeat the operation until it decides to work.

Any ideas on what I should do differently? Here is the UDF code that is referenced in the cell formula:

Function example(Myrange As Range) As String


Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String

strPattern = "^(d{6} )(- )(Smith)(D*?)s*(d{4}D)"

If strPattern <> "" Then
    strInput = Myrange.Value
    strReplace = "$1(ZT$5) $2$3 $5$4"

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        example = regEx.Replace(strInput, strReplace)

    Else
        example = Myrange.Value
    End If
End If
End Function

Answer 1

I changed the formula calculation option in Excel from Automatic to Manual and the copy and paste values operation works consistently now.