Getting Error 91: Object variable or With block variable not set in VBA

Question

Not really sure why this isnt working. It is populating the data correctly, but always gives the error code: 91: Object variable or With block variable not set. Trying to take inputs from an operator user input which is handeled by other code. Then populate a spreadsheet with this import oven data code.

Dim OvenArray(1 To 7) As Worksheet
Dim ws_Oven1 As Worksheet
Dim ws_Oven2 As Worksheet
Dim ws_Oven3 As Worksheet
Dim ws_Oven4 As Worksheet
Dim ws_Oven5 As Worksheet
Dim ws_Oven6 As Worksheet
Dim ws_Oven7 As Worksheet

Sub InitializeWorksheets()
    Set ws_Oven1 = ThisWorkbook.Sheets("Oven #1")
    Set ws_Oven2 = ThisWorkbook.Sheets("Oven #2")
    Set ws_Oven3 = ThisWorkbook.Sheets("Oven #3")
    Set ws_Oven4 = ThisWorkbook.Sheets("Oven #4")
    Set ws_Oven5 = ThisWorkbook.Sheets("Oven #5")
    Set ws_Oven6 = ThisWorkbook.Sheets("Oven #6")
    Set ws_Oven7 = ThisWorkbook.Sheets("Oven #7")

    Debug.Print ws_Oven1.Name
    Debug.Print ws_Oven2.Name
    Debug.Print ws_Oven3.Name
    Debug.Print ws_Oven4.Name
    Debug.Print ws_Oven5.Name
    Debug.Print ws_Oven6.Name
    Debug.Print ws_Oven7.Name
End Sub

Sub DataImport(userDate As Date, userTime As Date, OvenNum As Integer, _
VacLevel As Variant, TC1 As Variant, TC2 As Variant, FixArray() As String, _
ImportQty As Integer, userComments As String, location As String)

Dim count As Integer

InitializeWorksheets

Set OvenArray(1) = ws_Oven1
Set OvenArray(2) = ws_Oven2
Set OvenArray(3) = ws_Oven3
Set OvenArray(4) = ws_Oven4
Set OvenArray(5) = ws_Oven5
Set OvenArray(6) = ws_Oven6
Set OvenArray(7) = ws_Oven7

Debug.Print "OvenNum: " & OvenNum
Debug.Print "OvenArray(OvenNum).Name: " & OvenArray(OvenNum).Name

If OvenNum < 1 Or OvenNum > 7 Then
    MsgBox "OvenNum is out of range. It should be between 1 and 7."
    Exit Sub
End If

OvenArray(OvenNum).Activate

EnsureLocationColumnExists userDate, userTime, OvenNum, VacLevel, TC1, TC2, FixArray, 
ImportQty, userComments, location

End Sub

Sub EnsureLocationColumnExists(userDate As Date, userTime As Date, OvenNum As Integer, 
_
VacLevel As Variant, TC1 As Variant, TC2 As Variant, FixArray() As String, _
ImportQty As Integer, userComments As String, location As String)

Dim count As Integer

Debug.Print "Entering EnsureLocationColumnExists"
Debug.Print "OvenNum: " & OvenNum
Debug.Print "OvenArray(OvenNum).Name: " & OvenArray(OvenNum).Name

OvenArray(OvenNum).Unprotect "Penguin5"

For count = 1 To ImportQty
    Debug.Print "Inserting row " & count
    OvenArray(OvenNum).Cells(3, 1).EntireRow.Insert

    Debug.Print "Setting values for row " & count
    Debug.Print "FixArray(count - 1, 1): " & FixArray(count - 1, 1)
    Debug.Print "FixArray(count - 1, 2): " & FixArray(count - 1, 2)
    Debug.Print "FixArray(count - 1, 3): " & FixArray(count - 1, 3)

    OvenArray(OvenNum).Cells(3, 1).value = Format(userDate, "M/D/YY")
    OvenArray(OvenNum).Cells(3, 2).value = Format(userTime, "H:MM AM/PM")
    OvenArray(OvenNum).Cells(3, 3).value = FixArray(count - 1, 1)
    With OvenArray(OvenNum).Cells(3, 4)
        .NumberFormat = "@"
        .value = FixArray(count - 1, 2)
    End With
    OvenArray(OvenNum).Cells(3, 5).value = OvenNum
    OvenArray(OvenNum).Cells(3, 6).value = VacLevel
    OvenArray(OvenNum).Cells(3, 7).value = TC1
    OvenArray(OvenNum).Cells(3, 8).value = TC2
    OvenArray(OvenNum).Cells(3, 9).value = FixArray(count - 1, 1) & "_F" & 
FixArray(count - 1, 3)
    OvenArray(OvenNum).Cells(3, 10).value = location
    OvenArray(OvenNum).Cells(3, 11).value = userComments

    Debug.Print "Row " & count & " inserted"
Next count

OvenArray(OvenNum).Protect "Penguin5"

End Sub

Answer 1

Not an answer to your root issue, but some suggestions for reducing the bulk of your code:

Const PW As String = "Penguin5" 'use constants for fixed values

'Return a worksheet given the oven number
Function OvenWorksheet(OvenNum As Long) As Worksheet 'prefer Long over Integer for whole numbers
    Const MAX_OVEN As Long = 7
    If OvenNum >= 1 And OvenNum <= MAX_OVEN Then
        Set OvenWorksheet = ThisWorkbook.Worksheets("Oven #" & OvenNum)
    Else
        MsgBox "Oven Number '" & OvenNum & "' is out of range." & _
             vbLf & "It should be between 1 and " & MAX_OVEN & "."
        'this branch returns `Nothing`
    End If
End Function


Sub DataImport(userDate As Date, userTime As Date, OvenNum As Long, _
        VacLevel As Variant, TC1 As Variant, TC2 As Variant, FixArray() As String, _
        ImportQty As Long, userComments As String, location As String)

    Dim oven As Worksheet, i As Long
    
    Set oven = OvenWorksheet(OvenNum)
    If oven Is Nothing Then Exit Sub 'nothing to update
    
    oven.Unprotect PW
    
    For i = 1 To ImportQty
        oven.Cells(3, 1).EntireRow.Insert
        With oven.Rows(3)
            .Cells(1).Value = Format(userDate, "M/D/YY")
            .Cells(2).Value = Format(userTime, "H:MM AM/PM")
            .Cells(3).Value = FixArray(i - 1, 1)
            With .Cells(4)
                .NumberFormat = "@"
                .Value = FixArray(i - 1, 2)
            End With
            .Cells(5).Value = OvenNum
            .Cells(6).Value = VacLevel
            .Cells(7).Value = TC1
            .Cells(8).Value = TC2
            .Cells(9).Value = FixArray(i - 1, 1) & "_F" & FixArray(i - 1, 3)
            .Cells(10).Value = location
            .Cells(11).Value = userComments
        End With
    Next i
    
    oven.Protect PW
End Sub