visual basic procedure too large error & ambiguous name detected worksheet_change

Question

I have a large VBA macro which consists of one large Private Sub Worksheet_Change(ByVal Target As Range).

It first gives me the procedure too large error as it is really big.

When I tried to break it into 3 Private Sub Worksheet_Change(ByVal Target As Range).

this error shows up:

ambiguous name detected worksheet_change

any clues I can work around these 2 errors?

thanks in advance

here are my codes, the actual codes have tonnes of conditions and text check for each target address

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Address = [rng_opt1].Address Then 
If [rng_opt1] = "x" Then 
If [rng1_1] = "z" then 
[rng1_1] = " " 
End if 
End If 
End if
End sub

thanks to @urdearboy, I got it solved, my final codes is like this (much simplified version). it's tricky and took me a while as my target has defined name

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error GoTo Whoa

    Application.EnableEvents = False

    If Target.Address = [rng_opt1].Address Then
      Call Opt1(Target)
    ElseIf Target.Address = [rng1_1].Address Then
      Call Opt11(Target)
    End if

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue

End Sub

Sub Opt1(Target As Range)

    If Target.Address = [rng_opt1].Address Then
           If [rng_opt1] = "x" Or [rng_opt1] = "y" Then
              If [rng1_1] = "z" 
                   [rng1_1] = " "
              End If
           End if
    End if
End Sub

Sub Opt11(Target As Range)

    If Target.Address = [rng1_1].Address Then
      If [rng1_1] = " " Then
        If [rng1_2] = " " And [rng1_3] = " " And [rng1_4] = " " Then
           [rng1_1] = "y"
           [rng1_2] = "x"
        End If
      End If
    End if 
 End sub

Possible duplicate of Ambiguous name detected Worksheet_Change — Ian
Welcome! Please look at the possible duplicate question, and see if that answers your problem. — kismert

urdearboy urdearboy · Accepted Answer · 2018-07-03T23:21:24

You can only have one WorkSheet_Change event on a worksheet which is why you are getting the Ambiguous Name Detected error.

If your code is too long, try to create your actions in a Sub and then call those subs given certain criteria. This way, you can limit your WorkSheet_Change code to strictly evaluate the Target.

In you WorkSheet_Change code you can have something like:

If Target.Value = “x” Then
  Call SubX
ElseIF Target.Value = “y” Then
  Call SubY
ElseIF Target.Value = “z” Then
  Call SubZ
End IF

SubX ()
   ‘Do Something
End Sub

SubY ()
   ‘Do Something
End Sub

SubZ ()
   ‘Do Something
End Sub

Note:

You will need to disable events before you make any physical change to your sheet otherwise you will find yourself in a infinite loop and crash your instance of excel. Use the below method to avoid this issue:

Application.EnableEvents = False
   ‘Physical changes to worksheet
Application.EnableEvents = True

visual basic procedure too large error & ambiguous name detected worksheet_change

1 Answers

Note: