Referencing a range

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
My understanding of ranges is it is referenced like Worksheet.Range.

Can someone please tell me how I can make Idea 1 work?

Standard module:

Code:
' Idea 1.

Sub Fails()

    Dim a As Class1
    Set a = New Class1
   
    Set a.wks = Sheet1
    Set a.rng = a.wks.Range("A1")
   
    Call a.Test
   
End Sub

This is Class1:

Code:
Option Explicit

Private pwks As Worksheet
Private prng As Range

Public Property Get wks() As Worksheet

    Set wks = pwks
   
End Property

Public Property Set wks(ByVal w As Worksheet)

    Set pwks = w
   
End Property

Public Property Get rng() As Range

    Set rng = prng
   
End Property

Public Property Set rng(ByVal r As Range)

    Set prng = r
   
End Property

Public Sub Test()

     '***** TRIED THESE OPTIONS, ALL FAILED

    Debug.Print Me.wks(Me.rng.Value)
   
' or

    Debug.Print Me.wks.Me.rng.Value

'or

    Debug.Print Worksheets(Me.wks.Name).Me.rng.Value

End Sub

Bu this works:

Code:
' Idea 2.

Sub Works()

    Dim a As Class2
    Set a = New Class2
   
    Set a.wks = Sheet1
    a.str = "A1"
   
    Call a.Test
   
End Sub

Class2:

Code:
Option Explicit

Private pwks As Worksheet
Private pstr As String

Public Property Get wks() As Worksheet

    Set wks = pwks
   
End Property

Public Property Set wks(ByVal w As Worksheet)

    Set pwks = w
   
End Property

Public Property Get str() As String

    str = pstr
   
End Property

Public Property Let str(ByVal s As String)

    pstr = s
   
End Property

Public Sub Test()

    Debug.Print Me.wks.Range(str).Value
   
End Sub

Thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
In your Class1 the Test method should say
VBA Code:
Public Sub Test()

    Debug.Print prng.Value

End Sub

FYI, when a reference is set, the hierarchy of that object is established so it's already qualified.
 
Upvote 0
Solution
In your Class1 the Test method should say
VBA Code:
Public Sub Test()

    Debug.Print prng.Value

End Sub

FYI, when a reference is set, the hierarchy of that object is established so it's already qualified.
Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top