VBA检查表是否存在-对象必需错误 [英] vba check if sheet exists - object required error

问题描述

我正在处理这段代码，当用户在目标范围内输入值时，程序将检查工作表名称是否存在，并做出相应的反应.

I'm working on this code that when user enter value in the target range, the program will check if sheet name exists, and react accordingly.

我有以下代码，第二部分(单元格C17)工作正常，但是第一部分(单元格C3)中的这一行If Not Sheet Is Nothing Then抛出了对象所需的错误.我在调试模式下查看代码，发现sheet的值为空，而sheet1为空.这两个部分的逻辑完全相同，所以我对第一个单元格为什么不起作用感到困惑.有人可以为我指出吗?谢谢.

I have the following code, the second part(Cell C17) works fine, but this line in the first part(Cell C3) If Not Sheet Is Nothing Then throws a object required error. I look at the code in the debug mode, and found the value of sheet is empty while sheet1 is nothing. The logic for the two parts are exactly the same, so I'm confused about why the first cell doesn't work. Could anyone point it out for me please? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rp, rp1 As String
Dim Sheet, Sheet1 As Worksheet

    rp = ThisWorkbook.Sheets("Settings and Instruction").Range("C3").Value
    rp1 = ThisWorkbook.Sheets("Settings and Instruction").Range("C17").Value


    On Error Resume Next
    Set Sheet1 = Worksheets(rp1)
    On Error GoTo 0

    On Error Resume Next
    Set Sheet = Worksheets(rp)
    On Error GoTo 0

    If Target.Address = "$C$3" Then


            If Not Sheet Is Nothing Then

            MsgBox "Sheet name already exists, please enter a new period."
            Else
            ConfirmPeriodNew.Show
            End If


    ElseIf Target.Address = "$C$17" Then

            If Not Sheet1 Is Nothing Then
            ConfirmPeriodUp.Show
            Else
            MsgBox "The period you've entered doesn't exist, please double check"
            End If

    End If

End Sub

推荐答案

问题出在我的评论中.工作表被定义为一个变体，因此在运行代码时，它不会被设置为对象.设置工作表"会导致错误(因为工作表不存在)，但是由于工作表的类型是可变的，因此其值为空，而不是稍后要检查的空".

The problem is as I mentioned in my comment. Sheet is defined as a variant, and so when you run the code, it is not set as an object. Setting "Sheet" is causing an error (as the worksheet doesn't exist), but since the type of Sheet is variant, its value is Empty instead of the Nothing you are later checking for.

Dim rp As String, rp1 As String
Dim Sheet As Worksheet, Sheet1 As Worksheet

这将解决您遇到的问题.

This will fix the issue you are having.

这篇关于VBA检查表是否存在-对象必需错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！