Refer to sheet via class

dugdugdug

Active Member
Joined
May 11, 2012
Messages
342
Can someone please tell me what is wrong with the following?

This is in module1:

Code:
Option Explicit

Sub test()

Dim abc As Class1

Set abc = New Class1

abc.Mysheet = "Sheet1"

End Sub

This is in Class1:

Code:
Option Explicit

Private pMySheet As Worksheet

Public Property Get Mysheet() As Variant

Mysheet = pMySheet

End Property

Public Property Set Mysheet(ByVal vNewValue As Variant)

pMySheet = vNewValue

End Property

When I run this, it says, "Object variable or With block variable not set"

Thanks
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You're missing Set in the sub and the class.
Code:
'Module1
Sub test()

Dim abc As Class1

    Set abc = New Class1

    Set abc.Mysheet = Sheet1

End Sub

'Class1

Private pMySheet As Worksheet

Public Property Get Mysheet() As Variant

    Set Mysheet = pMySheet

End Property

Public Property Set Mysheet(ByVal vNewValue As Variant)

    Set pMySheet = vNewValue

End Property
 

dugdugdug

Active Member
Joined
May 11, 2012
Messages
342
You're missing Set in the sub and the class.
Code:
'Module1
Sub test()

Dim abc As Class1

    Set abc = New Class1

    Set abc.Mysheet = Sheet1

End Sub

'Class1

Private pMySheet As Worksheet

Public Property Get Mysheet() As Variant

    Set Mysheet = pMySheet

End Property

Public Property Set Mysheet(ByVal vNewValue As Variant)

    Set pMySheet = vNewValue

End Property

Many thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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
Top