2

Okay, so I'm trying to write a simple script in VBA for Excel that changes an AutoFilter based on some value the user selects in a cell on the spreadsheet. So far it's been working out pretty well, but now I'm getting the following error and I can't figure out what's causing it:

Run-time error '91': Object variable or With block variable not set

Keep in mind that this is literally the first time I've ever tried to write anything in VBA so I'm not very familiar with the language. I am very familiar with Excel though and I know several other programming languages (Java, JavaScript, Ruby, LUA).

Here's the code I wrote; the error is happening on line 9.

Private Sub Worksheet_Change(ByVal Target As Range)

    '' Review Level Changed ''
    If Target.Address = Worksheets("Sheet1").Range("review_level").Address Then

        ' MsgBox "You just changed " & Target.Address & " to " & Target.Value ' Debug
        Dim oldProtection As Protection
        If Worksheets("Sheet1").ProtectContents = True Then
            oldProtection = Worksheets("Sheet1").Protection ' It errors out here
            Worksheets("Sheet1").Unprotect
        End If

        If Target = "B" Then
            ActiveSheet.ListObjects("review_checklist").Range.AutoFilter Field:=2, _
                Criteria1:=Array("B", "C", "D"), Operator:=xlFilterValues
        ElseIf Target = "C" Then
            ActiveSheet.ListObjects("review_checklist").Range.AutoFilter Field:=2, _
                Criteria1:="=C", Operator:=xlOr, Criteria2:="=D"
        ElseIf Target = "D" Then
            ActiveSheet.ListObjects("review_checklist").Range.AutoFilter Field:=2, _
                Criteria1:="=D"
        End If

        If Not IsEmpty(oldProtection) Then ' Btw, this IS how you check that oldProtection isn't null, right?
            Call ProtectWithProtection(oldProtection)
        End If

    End If

End Sub

Private Sub ProtectWithProtection(ByRef Protect As Protection)
    Worksheets("Sheet1").Protect ' ToDo: Use attributes from Protect
End Sub

This code is located inside "Sheet1" in my Excel project. Note that I am running Excel 2007.

Community
  • 1
  • 1
Ajedi32
  • 45,670
  • 22
  • 127
  • 172

1 Answers1

5

Whenever you have an object in VBA, you need to assign it a value using the Set operator. For example: 

Set oldProtection = Worksheets("Sheet1").Protection
Kevin Pope
  • 2,964
  • 5
  • 32
  • 47
  • So whenever I'm setting a variable that holds an object instead of a primitive, I need to use `Set`? I.E. if oldProtection was a Boolean instead the `Set` keyword wouldn't be necessary? If that's true, do you know why that is? (Like I said, I'm still just learning VBA.) – Ajedi32 Jan 04 '13 at 21:40
  • Yeah, that's been my experience. According to [this answer](http://stackoverflow.com/a/349636/493055), this is to set an object reference (similar to a pointer in C). – Kevin Pope Jan 04 '13 at 21:44
  • Ah, so `Set` is used in VBA as a way of explicitly stating that an object reference needs to be created, so it isn't needed with primitives like booleans and integers since their values would be stored directly in the variable. – Ajedi32 Jan 04 '13 at 21:50
  • 2
    Great explanation here http://stackoverflow.com/questions/9481140/exposing-property-as-variant-in-net-for-interop/9924325#9924325 about how the need for `Set` is a result of having default properties available on COM objects. – Tim Williams Jan 04 '13 at 22:03