(엑셀vba)안전한 바꾸기 - 바꾸기 실행시 에러날 경우

엑셀은 복잡한 수식내용중 일부를 바꾸고자 하거나, 한 셀에 찾기-바꾸기할 양이 너무 많을때

뻗어버리는 현상이 종종 있다. ( 수식이 너무 깁니다 또는 잘못된 연산에러 -.- )

아래 구문은 이러한 에러를 방지하면서도 원하는 찾기-바꾸기를 할 수 있게하는 구문이다.

일반모듈에 구문을 넣고나서, 실행하면 된다.

(또는 첨부화일을 열고 난 후 Ctrl+Shift+H 키로 바로 실행할 수 있다.)

Sub safe_replace()
Dim rngDb As Range, rngFind As Range
Dim strFind As String, strFindAd As String, strReplace As String
Dim strAddress As String, strTemp As String, strImsi As String
Dim blnChk As Boolean, i As Long, j As Long, lngcount As Long
    On Error GoTo er
    If Selection.Cells.Count = 1 Then
      Set rngDb = ActiveSheet.UsedRange
    Else
      Set rngDb = Selection
    End If
    strFind = Application.InputBox("찾을 문자를 입력하세요." & vbCr & _
      "(대표문자 입력도 가능합니다.)", "찾기-바꾸기 2단계중 1단계")
    If strFind = "False" Then
      Exit Sub
    Else
      strFindAd = strFind
      If strFind Like "*~[*,?]*" Then
        strFindAd = Application.Substitute(strFindAd, "~*", "[*]")
        strFindAd = Application.Substitute(strFindAd, "~?", "[?]")
      End If
      If strFind Like "*[[,#]*" Then
        strFindAd = Application.Substitute(strFindAd, "[", "[[]")
        strFindAd = Application.Substitute(strFindAd, "#", "[#]")
      End If
    End If
    strReplace = Application.InputBox("바꿀 문자를 입력하세요.", _
      "찾기-바꾸기 2단계중 2단계")
    If strReplace = "False" Then
      Exit Sub
    ElseIf strFind = vbNullString And strReplace = vbNullString Then
      Exit Sub
    End If
    With Application
      .Calculation = xlManual
      .ScreenUpdating = False
      Set rngFind = rngDb.Find(What:=strFind, LookIn:=xlFormulas, _
        LookAt:=xlPart, MatchCase:=False)
      If Not rngFind Is Nothing Then
        strAddress = rngFind.Address
        If strFind = vbNullString Then
          Do
            lngcount = lngcount + 1
            rngFind = strReplace
            Set rngFind = rngDb.FindNext(rngFind)
            If rngFind Is Nothing Then Exit Do
          Loop While strAddress <> rngFind.Address
        Else
          Do
            strImsi = vbNullString
         
            If rngFind.HasFormula Or rngFind.HasArray Then
              strTemp = rngFind.Formula
            Else
              strTemp = rngFind.Value
            End If
            i = 1
            j = 0
            Do
              lngcount = lngcount + 1
              strTemp = Mid(strTemp, i + j)
              i = 0
              j = 0
              Do
                i = i + 1
                blnChk = Mid(strTemp, i) Like strFindAd & "*"
              Loop Until blnChk = True
              Do
                j = j + 1
                blnChk = Mid(strTemp, i, j) Like strFindAd
              Loop Until blnChk = True
              If Right(strFindAd, 1) = "*" Then
                strImsi = Left(strTemp, i - 1) & strReplace
                Exit Do
              Else
                If Mid(strTemp, i + j) Like "*" & strFindAd & "*" Then
                  strImsi = strImsi & Left(strTemp, i - 1) & strReplace
                Else
                  strImsi = strImsi & Left(strTemp, i - 1) & strReplace & Mid(strTemp, i + j)
                  Exit Do
                End If
              End If
            Loop
            If rngFind.HasArray Then
              rngFind.FormulaArray = strImsi
            Else
              rngFind = strImsi
            End If
            Set rngFind = rngDb.FindNext(rngFind)
            If rngFind Is Nothing Then Exit Do
          Loop While strAddress <> rngFind.Address
        End If
      End If
      .Calculation = xlAutomatic
      .ScreenUpdating = True
    End With
    MsgBox "찾기 및 바꾸기가 끝났습니다. " & _
      lngcount & "개의 항목이 바뀌었습니다.", vbInformation
    Exit Sub
er:
    MsgBox Err & " : 에러로 인하여 종료되었습니다.", vbInformation
    With Application
      .Calculation = xlAutomatic
      .ScreenUpdating = True
    End With
End Sub

invalid-file