VBA代码Else分支引发运行时错误求助：Range方法调用失败致Excel崩溃

Hey there, let's break down why that frustrating Method 'Range' of object '_Worksheet' failed error (and Excel crash) is happening when you keep the Else branch. Since your code works fine when you comment that block out, the issue is definitely tied to how you're referencing ranges in the Else section. Here's what to check and fix:

Common Causes & Targeted Fixes #

1. Missing Quotes Around Range Addresses #

This is the most likely culprit. If you forgot to wrap your cell addresses in double quotes in the Else branch, VBA will treat them as undefined variables instead of valid range references. For example:
Wrong:

Range(A1,B2,C3).Value = "Other" ' No quotes around cell addresses

Right:

Range("A1,B2,C3").Value = "Other" ' Quotes make this a valid range reference

2. Implicit Worksheet References #

If you don't specify which worksheet your ranges belong to, VBA defaults to the currently active sheet. If that sheet isn't the one you intend (or it changes mid-execution), it can throw this error or cause unexpected behavior. Always explicitly define your worksheet to avoid this:

Sub FixedCode()
    Dim targetSheet As Worksheet
    ' Replace "Sheet1" with your actual worksheet name
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    With targetSheet
        If .Range("C15").Value = "No" Then
            ' Assign your specific values here
            .Range("D5").Value = "Specific Value 1"
            .Range("E6").Value = "Specific Value 2"
        Else
            ' Now the range is tied to the correct sheet
            .Range("D5,E6").Value = "Other"
        End If
    End With
End Sub

Note the dot (.) before Range—it tells VBA to use the worksheet defined in the With block, eliminating ambiguity.

3. Invalid Range Syntax #

Double-check your range formatting: use commas for non-contiguous cells (like Range("A1,B2")) and colons for contiguous ranges (like Range("A1:C3")). Typos like extra spaces or misspelled cell names can also trigger this error.

4. Variable Name Conflicts #

Make sure you haven't named any variables Range or Worksheet—these are reserved VBA object names, and using them as variable names will break object references and cause unexpected crashes.

Debugging Tip to Pinpoint the Issue #

Use Excel's built-in debugger to find the exact line causing the problem:

1. Open the VBA editor with Alt + F11
2. Click inside your subroutine
3. Press F8 to step through the code line by line
4. The line that triggers the error is where you'll find the root problem

If Excel still crashes after fixing these issues, try restarting Excel (or your computer) to clear any corrupted COM object cache. You can also repair your Office installation via Windows Settings if the problem persists.

内容的提问来源于stack exchange，提问作者Michael Amati