Subscript out of range error when using dim worksheet in VBA Excel

Question

The code is below. It is a subroutine to add a note in an Excel spreadsheet. I get a "Subscript out of range" error when I try to Dim wbook as Workbook. I've looked through a bunch of examples, and many use this format. I know this error can be caused by a bunch of different problems. I am open to all suggestions, and thanks in advance for your time.

VBSTART
Sub MarkContactToAdd

Dim xlApp
Dim wbook As Workbook
Dim xlSheet As Worksheet

Set xlApp = CreateObject("Excel.Application")
Set wbook = Workbooks("D:\Leads1.xlsx")
Set xlSheet = wbook.Worksheets(1)

xlApp.visible = True
'optionally make the sheet visible
xlSheet.Application.Visible = True
xlSheet.Cells(row,12).Value = "Add this contact"

End Sub
VBEND

Answer 1

Using Excel From Another Office Application

Option Explicit

Sub WriteContact()
    MarkContactToAdd "Add this contact"
End Sub

Sub MarkContactToAdd(ByVal WriteString As String)
    
    ' Late Binding (This Code)
    ' Other Office applications like 'Word', 'Outlook', 'PowerPoint'...
    ' don't know about the Excel objects 'Workbook', 'Worksheet', 'Range'...
    ' and about the Excel constants 'xlSheetHidden', 'xlUp'...
    
    ' Early Binding
    ' If you create a reference to Excel by checking
    ' 'VBE-Tools-References-Microsoft Excel xx.0 Object Library',
    ' the code becomes more familiar e.g.:
    ' 'Dim xlApp As Excel.Application', 'Set xlApp = New Excel.Application',
    ' 'Dim wb As Excel.Workbook','Dim ws As Excel.Worksheet',
    ' 'Dim dCell As Excel.Range'...
    
    ' Create a reference to a new instance of Excel.
    Dim xlApp As Object: Set xlApp = CreateObject("Excel.Application")
    
    ' To not end up with multiple open instances that would have to be closed
    ' in the 'Windows Task Manager'.
    On Error GoTo ClearExcelError
    
    ' Declare the workbook variable.
    Dim wb As Object
    
    ' "In the instance"...
    With xlApp
        
        ' In this case, you only want to quickly write something to a worksheet,
        ' so you want to out-comment or delete the following line
        ' after you have finished testing it.
        .Visible = True ' Default is 'False'.
        
        ' Open the workbook and create a reference to it.
        Set wb = .Workbooks.Open("D:\Leads1.xlsx") ' Note the leading dot.
        
    End With
   
    ' Create a reference to the workbooks first worksheet.
    Dim ws As Object: Set ws = wb.Worksheets(1)
    
    ' Optionally make the sheet visible.
    ' Excel Constant: 'xlSheetHidden' = -1 = True
    If ws.Visible <> -1 Then
        ws.Visible = -1
    End If
    
    ' Create a reference to the first available cell in column 'L' ('12').
    ' Excel Constant: 'xlUp' = -4162 (= 3)
    Dim dCell As Object
    Set dCell = ws.Cells(ws.Rows.Count, "L").End(-4162).Offset(1)
    
    ' Write to the first available cell.
    dCell.Value = WriteString
    
    ' Save and close the workbook.
    wb.Close SaveChanges:=True
        
ExcelSafeExit:
        
    ' Close the instance of Excel.
    xlApp.Quit
    
    Exit Sub

ClearExcelError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ExcelSafeExit

End Sub