VBA to remove external workbook references on the active sheet

[BigBeachBananas]

How to remove external workbook references on the active sheet using VBA?

For example:

=INDEX('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!Q7:Z7,1,MATCH('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!N34,Drop_Opt10,0))

How to remove the '[COE BENCHMARK - MASTER -AH 5. xlsm] and the apostrophe before the exclamation?

=INDEX(Summary!Q7:Z7,1,MATCH(Summary!N34,Drop_Opt10,0))

 

[Micron]

Maybe something like this in a standard module:

Sub EditFormula()
Dim rng As Range

For Each rng In ActiveSheet.UsedRange
   If rng.HasFormula Then
      If InStr(1, rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'") > 0 Then
         rng.Formula = Replace(rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'", "")
      End If
   End If
Next

End Sub

I suppose that the If test for the string isn't really necessary. IIRC, if Replace function doesn't contain the string being sought then nothing happens.

 

[BigBeachBananas]

I've made a small adjustment, however, when it gets to the first rng.formula it says "Application -defined or object-defined error."

Sub EditFormula()
Dim rng As Range

For Each rng In ActiveSheet.UsedRange
   If rng.HasFormula Then
      If InStr(1, rng.formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'") > 0 Then
         rng.formula = Replace(rng.formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]", "") 'Application-defined error
         rng.formula = Replace(rng.formula, "'!", "!") 'Added
      End If
   End If
Next
End Sub

This is the current formula in the cell.

=INDEX('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!Q7:Z7,1,MATCH('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!N34,Drop_Opt10,0))

Edit: The reason I don't want to include 'Summary' because it might be from some other sheets.

 

[Micron]

Sorry, I couldn't really test since I don't have your formula or references. When it didn't bark at me I figured it was a legit reference to the range object. I guess not. Try removing "formula" and see if it works just on the value of the range object. In the meantime I'll take another poke at it and try to edit one of my own formulas.

EDIT - I see that you're on a MAC. That may be it, because this returns the formula in a range when I halt the code:
?rng.Formula
=(3*10^8)
If your f in formula is lower case, that tells me that is a problem..

 

[Micron]

I just noticed that you have a formula that I suppose you want to remove that string more than once. In that case, a count of the occurrences of a string being sought will be needed. Should not be too hard to get that. First someone has to tell us why rng.Formula works for me and not you.

 

[BigBeachBananas]

Sorry, I couldn't really test since I don't have your formula or references. When it didn't bark at me I figured it was a legit reference to the range object. I guess not. Try removing "formula" and see if it works just on the value of the range object. In the meantime I'll take another poke at it and try to edit one of my own formulas.

EDIT - I see that you're on a MAC. That may be it, because this returns the formula in a range when I halt the code:
?rng.Formula
=(3*10^8)
If your f in formula is lower case, that tells me that is a problem..

I'm using Windows right now, and yes the rng.formula is in lowercase.

 

[Micron]

I'm using Windows right now,

Not a Mac user, so I don't understand how that is relevant. You're using Excel on a Mac, yes? That's what your profile shows. That may be the issue?

All of a sudden I'm getting the same error when I wasn't before. More digging required, I guess.

 

[BigBeachBananas]

Not a Mac user, so I don't understand how that is relevant. You're using Excel on a Mac, yes? That's what your profile shows. That may be the issue?

My profile shows both Windows and MacOS. I do use Mac sometimes, but I tested your code on Windows.

I used your ideas to make some adjustments and I think it's working for me. (I don't know why). With this it was popping up the file browser so I had to turn off the display alerts.

Sub EditFormula()

Application.DisplayAlerts = False

Dim ws As Worksheet
    Dim cell As Range
    Dim newFormula As String

    ' Set the active worksheet
    Set ws = ActiveSheet

    ' Loop through all cells with formulas in the active sheet
    For Each cell In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
            
        newFormula = RemoveExternalReferences(cell.formula)
        cell.formula = newFormula
          
    Next cell
Application.DisplayAlerts = True
End Sub

Function RemoveExternalReferences(ByVal formula As String) As String

    ' Remove '[COE BENCHMARK - MASTER -AH 5.xlsm]'
    formula = Replace(formula, "[COE BENCHMARK - MASTER -AH 5.xlsm]", "")

    ' Remove the single quote before '!'
    formula = Replace(formula, " '!", "!")

    RemoveExternalReferences = formula
    
  
End Function

 

[Micron]

I saw the browser as well and figured that was normal for you. My guess is that the problem is that the error was raised by trying to create an invalid formula. I ended up with =INDEX(!Q7:Z7,1,MATCH(!N34,Drop_Opt10,0)) as a formula, which seems obviously wrong. This enabled me to find that out:

Sub EditFormula()
Dim rng As Range
Dim strFormula As String

strFormula = "=INDEX('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!Q7:Z7,1,MATCH('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!N34,Drop_Opt10,0))"

For Each rng In ActiveSheet.UsedRange
   If rng.HasFormula Then
      If InStr(1, rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'") > 0 Then
          strFormula = Replace(rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'", "")
          Debug.Print strFormula
         'rng.Formula = Replace(rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'", "")
         'rng = strFormula
      End If
   End If
Next

End Sub

RemoveExternalReferences

Is that a UDF?

Correct me if I'm wrong, but Windows will run on a Mac so it's not Windows that would ever be an issue with vba. It's the lack of complete compatibility between Mac and PC when it comes to Office apps.

EDIT - if you alter app settings I advise the use of an error handler lest they get left that way.

 

[BigBeachBananas]

I saw the browser as well and figured that was normal for you. My guess is that the problem is that the error was raised by trying to create an invalid formula. I ended up with =INDEX(!Q7:Z7,1,MATCH(!N34,Drop_Opt10,0)) as a formula, which seems obviously wrong. This enabled me to find that out:

Sub EditFormula()
Dim rng As Range
Dim strFormula As String

strFormula = "=INDEX('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!Q7:Z7,1,MATCH('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!N34,Drop_Opt10,0))"

For Each rng In ActiveSheet.UsedRange
   If rng.HasFormula Then
      If InStr(1, rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'") > 0 Then
          strFormula = Replace(rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'", "")
          Debug.Print strFormula
         'rng.Formula = Replace(rng.Formula, "'[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'", "")
         'rng = strFormula
      End If
   End If
Next

End Sub

Is that a UDF?

Yes, you can see it in post#8 at the bottom.

Correct me if I'm wrong, but Windows will run on a Mac so it's not Windows that would ever be an issue with vba. It's the lack of complete compatibility between Mac and PC when it comes to Office apps.

What I meant is I have 2 computers, one for work (Windows OS) and one for personal (Mac OS). I'm currently using the work computer to test them so MacOS isn't the issue.