Help passing Range to Class Module method

[Brian Herbert Withun]

I am attempting to instantiate a user defined object (a Class Module)
and then pass a range of cells to that object by calling one of its
methods.

This is what I have so far:

'==================
' Class Module: "DowntimeReport"

Option Explicit

' this is intended to be an object method
Public Function PopulateByRange(R1 As Range) As Boolean

Dim MyRec As Range

For Each MyRec In R1
MyRec.Cells(1, 1).Interior.ColorIndex = 43
Next MyRec

End Function

'==================
' Module: "Kernel"

' this is just a VB Macro
Sub KWTest()
Dim MyObj As DowntimeReport
Set MyObj = New DowntimeReport
Dim MyRng As Range
Set MyRng = Selection
With MyObj
.PopulateByRange (ActiveSheet.Range("B2:F44"))
'.PopulateByRange (MyRng)
End With
End Sub

'==================

This works as written, and it changes the background color of cells
B2:F44

What I would like to have it do is change the background color of the
Selection, not just a hard-coded range of cells. If I uncomment the
line above (.PopulateByRange(MyRng)) it fails with "Object Required."

How can I pass the active selection as a range to my method
PopulateByRange() ?

In case it matters, I am initiating this by assigning the macro
"KWTest" to a custom button on a custom toolbar. I select a range of
cells and the press the custom button.

Brian Herbert Withun

 

[paul.robinson]

Hi
Try Activesheet.Selection
regards
Paul

 

[Phillip]

Hi
Try Activesheet.Selection
regards
Paul















- Show quoted text -

Phillip London UK

Try this

'Standard Module

Sub KWTest()
Dim MyObj As DownTimeReport
Set MyObj = New DownTimeReport
With MyObj
.PopulateByRange R1:=Selection
End With
End Sub


'Class Module

Option Explicit

' object method
Public Sub PopulateByRange(R1 As Range)
Dim MyRec As Range
For Each MyRec In R1
MyRec.Cells(1, 1).Interior.ColorIndex = 43
Next MyRec
End Sub

 

[Brian Herbert Withun]

Try Activesheet.Selection

regards
Paul

trying:

Set MyRng = ActiveSheet.Selection

causes:

Runtime-error '438':
Object doesn't support this property or method

 

[Brian Herbert Withun]

With MyObj

.PopulateByRange R1:=Selection

This did it! Thank you.


I would feel considerably less icky if I knew why that is different
from this:

.PopulateByRange (Selection)

for calls to this method:

Public Function PopulateByRange(R1 As Range) As Boolean
....

 

[paul.robinson]

oops, can't set to Selection directly. This should work:
Set myrange = Range(Selection.Address)

regards
Paul

 

[Peter T]

-

I would feel considerably less icky if I knew why that is different
from this:

.PopulateByRange (Selection)

for calls to this method:

Public Function PopulateByRange(R1 As Range) As Boolean

Placing the selection in brackets like that Evaluates the Range before
passing it to the function. The evaluated range will be a Variant array or a
single value (if selection is a single cell). Either way it'll fail as your
function expects to receive a Range object.

If you really want to place Selection in brackets you could do it like this

Call .PopulateByRange(Selection)


In passing, referring to the snippet of code in your OP

For Each MyRec In R1
MyRec.Cells(1, 1).Interior.ColorIndex = 43
Next MyRec

No need to loop, simply -

R1.Interior.ColorIndex = 43
or
R1.Value = 123.34

Regards,
Peter T

 

[kounoike]

I think it's because you use Function but not return value.

i change your code a little

' Class Module: "DowntimeReport"

Public Function PopulateByRange(R1 As Range) As Boolean

Dim MyRec As Range
On Error GoTo errhandle
For Each MyRec In R1
MyRec.Cells(1, 1).Interior.ColorIndex = 43
Next MyRec
'as Peter said, just the code below
'R1.Interior.ColorIndex = 43
'would do
PopulateByRange = True
Exit Function

errhandle:
PopulateByRange = False
End Function

' Module: "Kernel"

Sub KWTest()
Dim MyObj As Downtimereport
Set MyObj = New Downtimereport
Dim MyRng As Range
Dim result As Boolean

Set MyRng = Selection
With MyObj
'.PopulateByRange (ActiveSheet.Range("a1:b5"))
result = .PopulateByRange(MyRng)
If result Then
MsgBox "Method Succeeded"
Else
MsgBox "Method Failed"
End If
End With
End Sub

keiji

 

[Jon Peltier]

Simply using Selection is fine. The problem was parentheses around the
argument.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


oops, can't set to Selection directly. This should work:
Set myrange = Range(Selection.Address)

regards
Paul