☰

Excel 2003 宏运行时错误 1004 应用程序定义或对象定义错误

tag-icon • tag-icon macros microsoft-excel-2003
Excel 2003 宏运行时错误 1004 应用程序定义或对象定义错误

我们正在使用宏从我们的 Excel“数据库”中删除已标记为已完成/完成的数据行。这是编码...

Private Sub cmdRemove_Click()
    Dim varResult As VbMsgBoxResult
    varResult = MsgBox("Are you sure you want to remove all completed tasks? Ensure that the database has been backed up before running this operation.", vbYesNo)
    If varResult = vbYes Then
        Database.RemoveCompletedRequests
    End If
    MsgBox "Operation completed.", vbInformation
End Sub

引用的该代码的另一部分：......

Public Sub RemoveCompletedRequests()
    Dim i, j As Integer
    Dim varRequests() As Request
    varRequests = GetAllRequests

    For i = LBound(varRequests) To UBound(varRequests)
        If varRequests(i).Type = eDrafting Or varRequests(i).Type = eProject Then
            Dim varTasks() As Task
            Dim blnDelete As Boolean
            blnDelete = True
            varTasks = GetAllTasksByRequestNumber(varRequests(i).RequestNumber)
            For j = LBound(varTasks) To UBound(varTasks)
                If HasCompletedStatus(varTasks(j)) = False Then
                    blnDelete = False
                End If
            Next j
            If blnDelete Then
                For j = LBound(varTasks) To UBound(varTasks)
                    DeleteTask varTasks(j)
                Next j
                DeleteRequest varRequests(i)
            End If
        Else
            Dim varTask As Task
            varTask = GetPrimaryTaskByRequestNumber(varRequests(i).RequestNumber)
            If HasCompletedStatus(varTask) Then
                DeleteTask varTask
                DeleteRequest varRequests(i)
            End If
        End If
    Next i
End Sub

vb 表示宏挂在行：我上面提供的代码第一部分中的 database.removecompletedrequests。

我需要做什么来解决这个问题？

数据库模块存在“RemoveCompletedRequests”功能...其编码是...

Public Sub RemoveCompletedRequests()
Dim i, j As Integer
Dim varRequests() As Request
varRequests = GetAllRequests

For i = LBound(varRequests) To UBound(varRequests)
    If varRequests(i).Type = eDrafting Or varRequests(i).Type = eProject Then
        Dim varTasks() As Task
        Dim blnDelete As Boolean
        blnDelete = True
        varTasks = GetAllTasksByRequestNumber(varRequests(i).RequestNumber)
        For j = LBound(varTasks) To UBound(varTasks)
            If HasCompletedStatus(varTasks(j)) = False Then
                blnDelete = False
            End If
        Next j
        If blnDelete Then
            For j = LBound(varTasks) To UBound(varTasks)
                DeleteTask varTasks(j)
            Next j
            DeleteRequest varRequests(i)
        End If
    Else
        Dim varTask As Task
        varTask = GetPrimaryTaskByRequestNumber(varRequests(i).RequestNumber)
        If HasCompletedStatus(varTask) Then
            DeleteTask varTask
            DeleteRequest varRequests(i)
        End If
    End If
Next i
End Sub

Private Sub DeleteRequestRow(pintRow As Integer)
Dim intRow As Integer
Dim ws As Worksheet
Set ws = Sheets.Item(mstrRequestsTable)
intRow = pintRow

While ws.Cells(intRow + 1, 1) <> ""
    ShiftRow mstrRequestsTable, intRow + 1, intRow, mintColumnsInRequestsTable
    intRow = intRow + 1
Wend
End Sub

Private Sub DeleteRequest(pvarRequest As Request)
    DeleteRequestRow FindRequestRowByRequestNumber(pvarRequest.RequestNumber)
End Sub

Private Sub DeleteTaskRow(pintRow As Integer)
Dim intRow As Integer
Dim ws As Worksheet
Set ws = Sheets.Item(mstrTasksTable)
intRow = pintRow

While ws.Cells(intRow + 1, 1) <> ""
    ShiftRow mstrTasksTable, intRow + 1, intRow, mintColumnsInTasksTable
    intRow = intRow + 1
Wend
End Sub

' Deletes the task as well as removing all merge links to this task.
Private Sub DeleteTask(pvarTask As Task)
    ClearMergesForReferenceNumber pvarTask.ReferenceNumber
    DeleteTaskRow FindTaskRowByReferenceNumber(pvarTask.ReferenceNumber)
End Sub

' Shifts a row of data to a different row, clearing the old row
Private Sub ShiftRow(pstrTable As String, pintSourceRow As Integer, pintTargetRow As Integer, pintNumberOfColumns As Integer)
Dim i As Integer
Dim ws As Worksheet
Set ws = Sheets.Item(pstrTable)

If pintSourceRow <> pintTargetRow Then
    For i = 1 To pintNumberOfColumns
        ws.Cells(pintTargetRow, i) = ws.Cells(pintSourceRow, i)
        ws.Cells(pintSourceRow, i) = ""
    Next i
End If
End Sub

Private Sub ClearMergesForReferenceNumber(pstrReferenceNumber As String)
Dim i As Integer
Dim varTasks() As Task
varTasks = GetAllTasks

For i = LBound(varTasks) To UBound(varTasks)
    If varTasks(i).MergedWithReferenceNumber = pstrReferenceNumber Then
        varTasks(i).MergedWithReferenceNumber = ""
    End If
Next i
End Sub

答案1

我认为您不需要引用包含宏的模块。为了清楚起见，请从中删除Database.。Database.RemoveCompletedRequests如果您坚持这样做，则应避免命名可能与对象混淆的模块。也就是说，您可能希望modDatabase改为调用您的模块。

其他一些提示：所有变量都应在模块开始时声明/变暗。RemoveCompletedRequests例如，在中，每次循环 For/Next 时都要重新声明变量。将它们移到子程序的顶部。

最后，当您编写类似 的内容时Dim i, j As Integer，i 被声明为 Variant，而不是 Integer。您需要明确说明这些声明：Dim i as Integer, j As Integer。

相关内容