Attempt to return Range; Run Time Error 91

Viewed 41

I wrote a function that's simply supposed to return a range. However I get a run time error 91 (object variable or with block not set). I don't understand why, since the debugger says, that the value is set and Nothing at the same time.

The idead behind this code is the following: I've got a userform where the user can select up to 6 columns, column A is always included and therefore not optional. I would like to return the range of that selection.

screenshot, debugger values at bottom right

Private Function calcGraphRange() As range
    Dim rng0, rng1, rng2, rng3, rng4, rng5, rng6 As range

    Debug.Assert (Me.CheckBox1.Value = True)

    Set rng0 = range("A:A") 
    Set rng1 = range("B:B") 
    Set rng2 = range("C:C") 
    Set rng3 = range("D:D") 
    Set rng4 = range("E:E") 
    Set rng5 = range("F:F") 
    Set rng6 = range("G:G") 


    Set calcGraphRange = Application.Union(rng0, _
    IIf(Me.CheckBox1.Value = True, rng1, rng0), _
    IIf(Me.CheckBox2.Value = True, rng2, rng0), _
    IIf(Me.CheckBox3.Value = True, rng3, rng0), _
    IIf(Me.CheckBox4.Value = True, rng4, rng0), _
    IIf(Me.CheckBox5.Value = True, rng5, rng0), _
    IIf(Me.CheckBox6.Value = True, rng6, rng0))
End Function

Here is what I did so far:

I'd appreciate any help or tips. Have a nice day.

0 Answers
Related