1

though searched thoroughly I don't get a range passed into a userform via property. I get the error object required. The code is inside the userform, the parameter prange is global. Any help would be appreciated much. Thanks.

Public Property Get MyRng() As Range

Set MyRng = r1

End Property

Public Property Set MyRng(ByRef prange As Range)

Set r1 = prange

End Property
Community
  • 1
  • 1
Dooey
  • 35
  • 4

1 Answers1

2

That should be the code inside the userform

Option Explicit
Dim mRng As Range
Public Property Get myRng() As Range
    Set myRng = mRng
End Property

Public Property Set myRng(nRg As Range)
    Set mRng = nRg
End Property

Just a simple example

Option Explicit

    Sub Test()
    Dim frm As UserForm1
    Dim rg As Range

        Set frm = New UserForm1
        Set frm.myRng = Range("A1")
        Set rg = frm.myRng
        Debug.Print rg.Address

    End Sub
Storax
  • 11,158
  • 3
  • 16
  • 33
  • still no change, meanwhile I doubt you can use properties to pass range objects in VBA. – Dooey Apr 07 '18 at 09:08
  • What do you mean no change. You can use properties to pass a range object. – Storax Apr 07 '18 at 09:13
  • I mean I still get the same error, runtime error 91, object variable not set. – Dooey Apr 07 '18 at 09:19
  • Sorry, but where do you get this error? You did not mention a runtime error before! If you use your global variable prange you are going into the wrong direction. – Storax Apr 07 '18 at 09:21
  • Sorry for the confusion, I thought object required is always a runtime error. Upon compilation everything is fine. I no longer use prange inside the userform. But allow me to ask how then does the range to be dealt with gets from the invoking procedure into the userform? Thanks. – Dooey Apr 07 '18 at 09:27
  • Did you try my simple example? It's best you step through it then you will see what is happening by running the code with [Debug/Step into](https://www.techonthenet.com/excel/macros/vba_debug2013.php) or pressing F8 – Storax Apr 07 '18 at 09:33
  • Maybe a more concise site for [Debug/Step into](http://www.excel-easy.com/vba/examples/debugging.html) – Storax Apr 07 '18 at 09:40
  • Sorry, I did not create a new instance of the userform. I just used the .load and .show commands with the class design code. Your 2nd simple example helped me. Thanks a lot Storax! – Dooey Apr 07 '18 at 10:03
  • 1
    Then have a look at [this](https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/) and why it's usually not a good idea to use the default instance. – Storax Apr 07 '18 at 10:26
  • As you referred to good practice in showing a userform instance, you can find an example regarding modeless forms at https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly – T.M. Apr 26 '18 at 07:24