函式檢查作業表是否存在

我有一個函式可以檢查作業簿中是否wsName已經存在名為的作業表。我遇到的問題是在重組和洗掉On Error Resume Next. 我期望的是宏運行并生成作業簿中尚不存在的作業表副本，如果作業表已經存在，則列印出來ErrorMsg說"Unknown Error". 然而，我看到的是宏列印出來，ErrorMsg即使作業表不存在并復制它。我正在嘗試這種方法來SheetExists查看是否有一種方法可以讓函式在不使用的情況下運行，On Error Resume Next因為我不想宏忽略生成的錯誤，而是希望它列印出來"Unknown Error"

Global Parameter As Long, RoutingStep As Long, wsName As String, version As String, ErrorMsg As String, SDtab As Worksheet
Global wb As Workbook, sysrow As Long, sysnum As String, ws As Worksheet

Public Sub Main()
    Dim syswaiver As Long, axsunpart As Long
    Dim startcell As String, cell As Range
    Dim syscol As Long, dict As Object, wbSrc As Workbook

Set wb = Workbooks("SD3_KW.xlsm")
Set ws = wb.Worksheets("Data Sheet") 


syswaiver = 3
axsunpart = 4


Set wbSrc = Workbooks.Open("Q:\Documents\Specification Document.xlsx")
Set dict = CreateObject("scripting.dictionary") 

If Not syswaiver = 0 Then
    startcell = ws.cells(2, syswaiver).Address 
Else
    ErrorMsg = "waiver number column index not found. Value needed to proceed"
    GoTo Skip
End If

For Each cell In ws.Range(startcell, ws.cells(ws.Rows.Count, syswaiver).End(xlUp)).cells 
    sysnum = cell.value
    sysrow = cell.row
    syscol = cell.column
    
    If Not dict.Exists(sysnum) Then 
        dict.Add sysnum, True
    
        If Not SheetExists(sysnum, wb) Then 
            If Not axsunpart = 0 Then
                wsName = cell.EntireRow.Columns(axsunpart).value 
                If SheetExists(wsName, wbSrc) Then 
                    wbSrc.Worksheets(wsName).copy After:=ws 
                    wb.Worksheets(wsName).Name = sysnum 
                Set SDtab = wb.Worksheets(ws.Index   1)
                Else
                    ErrorMsg = ErrorMsg & IIf(ErrorMsg = "", "", "") & "part number for " & sysnum & " sheet to be copied could not be found"
                    cell.Interior.Color = vbRed
                GoTo Skip
                End If
      Else
                ErrorMsg = "part number column index not found. Value needed to proceed"
            End If 
            
        Else 
            MsgBox "Sheet " & sysnum & " already exists."
        End If
    End If
    
Skip:

Dim begincell As Long, logsht As Worksheet 
Set logsht = wb.Worksheets("Log Sheet") 
    With logsht ' wb.Worksheets("Log Sheet")
        begincell = .cells(Rows.Count, 1).End(xlUp).row
        .cells(begincell   1, 3).value = sysnum
        .cells(begincell   1, 3).Font.Bold = True
        .cells(begincell   1, 2).value = Date
        .cells(begincell   1, 2).Font.Bold = True

        If Not ErrorMsg = "" Then
            .cells(begincell   1, 4).value = vbNewLine & "Complete with Erorr - " & vbNewLine & ErrorMsg
            .cells(begincell   1, 4).Font.Bold = True
            .cells(begincell   1, 4).Interior.Color = vbRed
        Else
            .cells(begincell   1, 4).value = "All Sections Completed without Errors"
            .cells(begincell   1, 4).Font.Bold = True
            .cells(begincell   1, 4).Interior.Color = vbGreen
        End If
    End With

Next Cell 

End Sub

Function SheetExists(SheetName As String, wb As Workbook)  
On Error GoTo Message
SheetExists = Not wb.Sheets(SheetName) Is Nothing
Exit Function
Message:
    ErrorMsg = "Unknown Error"
End Function

uj5u.com熱心網友回復：

你的功能代碼總是到達最后一行，就像它......

如果作業表物件存在，您必須放置一個代碼行以退出函式：

Function SheetExists_(SheetName As String, wb As Workbook) As Boolean
On Error GoTo Message
SheetExists_ = Not wb.Sheets(SheetName) Is Nothing
If Not wb.Sheets(SheetName) Is Nothing Then Exit Function
Message:
    MsgBox "Unknown Error"
End Function

編輯：

Function SheetExists_(SheetName As String, wb As Workbook) As Boolean
On Error GoTo Message
SheetExists_ = Not wb.Sheets(SheetName) Is Nothing: Exit Function

Message:
    'reaching this part will (only) make it returning `False`...
End Function

請注意上面的函式是SheetExists_. 它有一個名稱結尾的下劃線字符。我有這個名字的另一個功能......

uj5u.com熱心網友回復：

您的“SheetExists”函式將始終將“ErrorMsg”設定為“未知錯誤”。后添加“退出功能” SheetExists = Not wb.Sheets(SheetName) Is Nothing

uj5u.com熱心網友回復：

你現在的方式是，當作業表不存在時，你將 ErrorMsg 設定為“未知錯誤”。這就是為什么每張紙都會出錯，因為每張紙在您的測驗設定中都是不同的。您的函式仍然會給出 False（不存在）但也會給出錯誤。

uj5u.com熱心網友回復：

使用一個簡單的函式來檢查。你不應該混淆作業表和作業表

Public Function sh_Exist(TheWorkbook As Workbook, SheetName As String) As Boolean
Dim s As String
On Error GoTo errHandler
s = TheWorkbook.Sheets(SheetName).Name
sh_Exist = True
Exit Function
errHandler:
End Function

標籤：擅长VBA

上一篇：Range類的PasteSpecial方法在第一次運行時失敗但在第二次運行時失敗

下一篇：覆寫VBA中現有資料之前的警告