Replacing Some Formulas with the Formula Results

Brian's requirement described in this Tip is to replace a formula like =A1 but not one like =Sheet1!A1 in "a worksheet before it can be handed out to other people." But what about a formula like =A1+Sheet1!A1? And what about the other issues discussed in my most recent comment below? Why not replace all of the formulas with their values?
At least this Tip provokes consideration of two useful points:
1. Be careful with your requirment specification; consider all possibilities.
2. Factoring an arbitrary Excel formula for analysis of its parts is difficult.

Brian wants to replace a formula with its value except a formula like
=...Sheet1!A1...
unless that formula is like
=...[Book1.xlsx]Sheet1!A1...
The Tip's macros ignore the fact that a formula might include an exclamation point or bracket as part of a text string like "Hello World!" or "[a-z]". Here are three examples:
=SEARCH("[*]",$A$1)
=SUBSTITUTE($A$1,"!","?")
=SUBSTITUTE(SUBSTITUTE($A$1,"[","("),"]",")")
If $A$1 contains the following text
[...]...!
the 1st example returns 1
the 2nd returns [...]...?
the 3rd returns (...)...!
Besides the issues raised by Philip and Willy, the Tip does not address these questions:
1. What if a formula's value is text that begins with an equal sign (=)?
2. What if a cell includes a legacy array formula like {=B2:B6*C2:C6} which was entered into D2:D6 using Ctrl+Shift+Enter (CSE)?
3. What if a cell is part of a dynamic array formula like =B2:B6*C2:C6 which was entered into D2 using Excel 2021+?
Here's my version to address all these points:

Sub ConvertFormulas3() 'consider only the currently selected range
'replace formula with value unless it refers to a sheet in this workbook
    Dim rSele As Range, rCell As Range
    Dim sForm As String, sTemp As String
    Dim bEnEv As Boolean, bKeep As Boolean
    Dim nCalc As Integer, nEx As Integer, nR As Long, nC As Long
    Dim WS As Worksheet, A As Variant, v As Variant
    Set rSele = Selection.SpecialCells(xlCellTypeFormulas)
    'for single cell Selection, confirm it is Special
    Set rSele = Application.Intersect(Selection, rSele)
    If rSele Is Nothing Then MsgBox "No formulas in Selection": Exit Sub
    With Application
        bEnEv = .EnableEvents
        .EnableEvents = False
        nCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    On Error Resume Next 'to insure events and calculations are restored
    rSele.Calculate 'update
    For Each rCell In rSele 'cells that had formulas
        If rCell.HasFormula Then 'might have been replaced already
            sForm = rCell.Formula
            bKeep = False
            nEx = 0
            Do While True 'there might be several "!"
                nEx = InStr((nEx + 1), sForm, "!") 'proceed left-to-right
                If nEx = 0 Then Exit Do
                sTemp = Replace(Left(sForm, nEx), "'", "") 'remove any "'"
                For Each WS In Worksheets
                    bKeep = (InStr(1, sTemp, (WS.Name & "!")) > 0) And _
                        (InStr(1, sTemp, ("]" & WS.Name & "!")) = 0)
                    If bKeep Then Exit For
                Next WS
                If bKeep Then Exit Do 'found ...Sheet!... not ...]Sheet!...
            Loop
            If Not bKeep Then 'replace formula with value
                If rCell.HasSpill Then 'dynamic array (Excel 2021+)
                    A = rCell.SpillingToRange.Value
                ElseIf rCell.HasArray Then 'legacy CSE array
                    A = rCell.CurrentArray.Value
                Else
                    A = rCell.Value
                End If
                If IsArray(A) Then 'array-from-range is base-1 2D
                    For nR = 1 To UBound(A, 1)
                        For nC = 1 To UBound(A, 2)
                            v = A(nR, nC)
                            If Left(v, 1) = "=" Then A(nR, nC) = "'" & v
                        Next nC
                    Next nR
                    rCell.Resize(UBound(A, 1), UBound(A, 2)) = A
                ElseIf Left(A, 1) = "=" Then
                    rCell = "'" & A
                Else
                    rCell = A
                End If
            End If
        End If
    Next rCell
    On Error GoTo 0 'not necessary
    With Application
        .EnableEvents = bEnEv
        .Calculation = nCalc
    End With
    Beep
End Sub

This macro uses the currently undocumented Range.SpillingToRange property.
Notice this macro does not replace a formula like
=...Sheet1!A1...[Book1.xlsx]Sheet1!A1...
nor a formula like
=...[Book1.xlsx]Sheet1!A1...Sheet1!A1...
Perhaps these should be highlighted for user consideration.
There might be other issues this macro does not address.

@J. Woolley
My comment is indeed meant for when in a workbook event handler macros are used (I use them a lot).

@J. Woolley, I think the crux is in “unless the workbook contains other event handlers”. That’s something that may not be known at time of inserting the code from this tip. Or event handlers may even be added later. Replacing cell contents would (I believe) at least trigger recalculation of the workbook, so interrupting events and calculations is in my opinion “best practice” in cases as these (of course including re-enabling them at the end)

The Tip includes the following sentence: "If you truly want to only replace formulas to other worksheets in the current workbook but ignore formulas that reference sheets on other workbooks, then you need to add some additional logic."
I believe the Tip has reversed the logic in that sentence and it should be: "If you truly want to IGNORE formulas to other worksheets in the current workbook but REPLACE formulas that reference sheets on other workbooks, then you need to add some additional logic."

@Willy Vanhaelen, Philip
Unless your workbook has event handler macros, I don't see why it is necessary to disable events. And since the Tip's macros replace a formula with its current value, I don't see why automatic calculation matters. Please explain.

@Philip
Exactly, you got that right ! especially disabling application events. But don't forget to enable them again when finished .

Haven’t tested this, but it would seem to me that the proposed macro is missing a step or two: disabling automatic calculation and disabling application events before replacing formulas with values … ?