VBA宏运行时错误求助：第二行触发下标越界（Subscript out of range）

Hey there, let's break down why that error is hitting your second line of code and fix it step by step.

The Root Cause of the Error #

The Subscript out of range error on Set wb = Workbooks(PLC) almost always boils down to one of two issues:

• The value stored in the PLC variable doesn't match the exact name (including file extension) of any currently open workbook.
• The PLC variable isn't properly declared or assigned a valid value before this line runs.

Quick Fixes to Resolve the Issue #

Let's cover the most common scenarios and how to fix them:

1. If PLC is a hardcoded workbook name

If you intended PLC to be the literal name of your workbook (like "PLC_Data.xlsx"), you need to wrap it in quotes because it's a string value. Your corrected code would look like this:

Sub ClosePLCWorkbook()
    Dim wb As Workbook
    ' Replace "PLC.xlsx" with your actual workbook name (including extension)
    Set wb = Workbooks("PLC.xlsx")
    wb.Close SaveChanges:=False ' Note the added colon before =
    Application.DisplayAlerts = True
End Sub

Critical note: Don't skip the file extension (.xlsx, .xlsm, etc.)—this is a super common oversight that triggers the subscript error!

2. If PLC is a variable storing the workbook name

First, make sure you explicitly declare the variable and assign it a valid, matching workbook name before referencing it. Example:

Sub ClosePLCWorkbook()
    Dim wb As Workbook
    Dim PLC As String ' Declare the variable explicitly
    
    ' Assign the exact name of your open workbook
    PLC = "ProductionPLC.xlsm"
    
    Set wb = Workbooks(PLC)
    wb.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

Double-check that the value assigned to PLC matches the workbook name exactly (capitalization doesn't usually matter on Windows, but it's safer to match it perfectly).

3. Bonus: Add a safety check for unopened workbooks

To avoid the error entirely if the workbook isn't open, add a simple error-handling check:

Sub ClosePLCWorkbookSafely()
    Dim wb As Workbook
    Dim PLC As String
    
    PLC = "PLC.xlsx"
    
    On Error Resume Next ' Skip error if workbook isn't open
    Set wb = Workbooks(PLC)
    On Error GoTo 0 ' Reset default error handling
    
    If Not wb Is Nothing Then
        wb.Close SaveChanges:=False
        Application.DisplayAlerts = True
    Else
        MsgBox "Workbook " & PLC & " isn't open right now!"
    End If
End Sub

Don't Miss This Tiny Syntax Fix #

In your original code, SaveChanges:False is missing a colon before the equals sign—it should be SaveChanges:=False. This would cause another error once you fix the subscript issue, so be sure to correct that too!

内容的提问来源于stack exchange，提问作者Pat Redmann