2

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.

PiddBoo
  • 31
  • 3
  • What does 'Me' refer to in your code. i.e. is your function inside a class? – freeflow Jul 29 '20 at 15:36
  • Hi and thank you for your comment. So the debugger shows, that "Me" represents the UserForm_Main (the one that holds the checkboxes). The code is written in the general codebehind of my userform. This function is not inside of a class or another function. I hope that helps. – PiddBoo Jul 29 '20 at 15:41
  • I tried it with a userform, the code works for me, have you tested to set the private to public? Are the checkbox names correct? What are you doing with the result of the function? I called it by using msgbox(calcGraphRange().address) and i received the address of the range. – Andreas Jul 29 '20 at 15:42
  • I think the problem may be in the code that's calling this function. As @Andreas said - the code runs as it is. `rng0` - `rng5` are declared as variants though, only `rng6` is a range. `Dim rng0 as Range, rng1 as Range, ....` – Darren Bartrup-Cook Jul 29 '20 at 15:45
  • Thank you very much for your help. So I tried changing the access modifier, which didn't change anything. All the checkbox names were correct. However changing the caller function worked for me aswell. I have 6 checkboxes and a button. That button is supposed to get the range and pass that range, so a diagram can be created. I guess storing the Range as: Dim rr As Range rr = calcGraphRange is creating the problem. – PiddBoo Jul 29 '20 at 16:06
  • Always best to *qualify* which worksheet each `Range` is on. – BigBen Jul 29 '20 at 17:10

0 Answers0