Excel 2003 Macro runtime error 1004 application-defined or object-defined error

Question

We are using a macro to remove rows of data from our excel 'database' that have been tagged as being completed/done. this is the coding ...

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

the other part of this code that is referenced:...

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 indicates that the macro is getting hung up on the row: database.removecompletedrequests in the first section of code I provided above.

What do I need to do to resolve this?

Database module exists with "RemoveCompletedRequests" function ... coding for this is ...

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

Answer 1

I don't believe you need to reference the module that contains the macro. To be clear, remove Database. from Database.RemoveCompletedRequests. If you insist on doing so, you should avoid naming modules that could be confused with Objects. That is, you might want to call your module modDatabase instead.

Some other pointers: All variables should be declared/Dim'd at the start of the module. In RemoveCompletedRequests, for example, you're re-declaring your variables each time you loop through your For/Next. Move these to the top of your sub.

Finally, when you write something like Dim i, j As Integer, i is being declared as a Variant, not an Integer. You need to be explicit about these declarations: Dim i as Integer, j As Integer.