Object variable of with block variable not Set

Question

I am a novice in vba who currently designing some sorts of automated Matrix system in excel. I tried both sets of codes in a Worksheet and it runs perfectly. But,when i try to use the same code in an event sub in an userform, an error 91 popped out and showed an error in orivalue, though I already assign a value to it. Also I will highlight the debug lines according to the compiler.

Here are the codes for the function.

Function find_prevconfig(x2 As Integer) As Range
    For y = 0 To 30
        If Range("E590").Offset(y, x2) = "Y" Then
            Set find_preconfig = Range("C590").Offset(y, 0)
            Exit Function
        End If
    Next y
End Function

And here is the event sub that i called the function to:

Private Sub btn_confirm_Click()
    Dim orivalue As Range
    Dim i As Integer

    For i = 0 To 30
        If Range("E26").Offset(0, i).Value = Range("J6").Value Then
            Set orivalue = find_prevconfig(i)
            MsgBox (orivalue)
        End If
    Next i
End Sub

The debug line is MsgBox (orivalue) as it said orivalue = nothing. Your help and advices are really much appreciated!

If the If range("E590").Offset(y, x2) = "Y" Then condition fails, the find_prevconfig() function returns default value (Nothing), which is the reason — Алексей Р
– Алексей Р, Commented Dec 5, 2022 at 6:49
The context matters. i.e. when the event runs, which sheet are you on. — shahkalpesh
– shahkalpesh, Commented Dec 5, 2022 at 6:53
@АлексейР as i set up the value in the target Ranges, i will put at least one "Y". So the condition will always be met. if i add msgbox(find_prevconfig) in the function block codes, it run perfectly and the value that i search will be found correctly. The problem is when calling and assigning the function value to an object in the event sub — taqiuddin yusri
– taqiuddin yusri, Commented Dec 5, 2022 at 7:03
@shahkalpesh i tried first declaring the worksheet that i intended to search using Thisworkbook.sheets("sheet1"). But still when i called the value of the function in the event sub, the same error keep poppin out. — taqiuddin yusri
– taqiuddin yusri, Commented Dec 5, 2022 at 7:05
What happens if you try If Trim(UCase(ThisWorkbook.Sheets("Sheet1").Range("E590").Offset(y, x2).Value)) = "Y" Then instead of If range("E590").Offset(y, x2) = "Y" Then? — Siddharth Rout
– Siddharth Rout, Commented Dec 5, 2022 at 7:14

Siddharth Rout · Accepted Answer · 2022-12-05 08:55:51Z

the object variable or With block variable not set" or Error "91"

There are few things that I will address.

1. Regarding the error, you need to check if the object exists before you use it. For example

The line MsgBox (orivalue) should be written as

Set orivalue = find_prevconfig(i)
If Not orivalue Is Nothing Then
    MsgBox orivalue.Value
Else
    MsgBox "Object is Nothing"
End If

2. Your object find_prevconfig will always be Nothing even if the condition is True. And that is because of a typo. Function name is find_prevconfig but you are using find_preconfig. It is advisable to always use Option Explicit

3. Fully qualify your objects. In your code if you do not do that, then it will refer to the active sheet and the active sheet may not be the sheet that you are expecting it to be. For example ThisWorkbook.Sheets("Sheet1").Range("E590").Offset(y, x2)

4. Even though, .Value is the default property of a range when you are assigning a value or reading a value, it is advisable to use it explicitly. I personally believe it is a good habit. Will help you avoid lot of headaches in the future when you are quickly skimming the code. Set rng = Range("SomeRange") vs SomeValue = Range("SomeRange").Value or SomeValue = Range("SomeRange").Value2

5. When you are doing a string comparison, it is advisable to consider that the strings can have spaces or can be of different case. "y" is not equal to "Y". Similarly, "Y " is not equal to "Y". I, if required, use TRIM and UCASE for this purpose as shown in the code below.

Your code can be written as (UNTESTED)

Option Explicit

Function find_prevconfig(x2 As Long) As Range
    Dim y As Long
    Dim rng As Range
    Dim ws As Worksheet
    
    '~~> Change sheet as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    For y = 0 To 30
        If Trim(UCase(ws.Range("E590").Offset(y, x2).Value2)) = "Y" Then
            Set rng = ws.Range("C590").Offset(y)
            Exit For
        End If
    Next y
    
    Set find_prevconfig = rng
End Function

Private Sub btn_confirm_Click()
    Dim orivalue As Range
    Dim i As Long
    Dim ws As Worksheet
    
    '~~> Change sheet as applicable
    '~~> You can also pass the worksheet as a parameter if the comparision is
    '~~> in the same sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    For i = 0 To 30
        If ws.Range("E26").Offset(0, i).Value = ws.Range("J6").Value Then
            Set orivalue = find_prevconfig(i)
            
            '~~> Msgbox in a long loop can be very annoying. Use judiciously
            If Not orivalue Is Nothing Then
                'MsgBox orivalue.Value
                Debug.Print orivalue.Value
            Else
                'MsgBox "Object is Nothing"
                Debug.Print "Object is Nothing"
            End If
        End If
    Next i
End Sub

Collectives™ on Stack Overflow

Object variable of with block variable not Set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related