Excel VBA Classes Error: 424 Object Required

[Sooty]

Hi All,

I'm receiving the error 424:Object required when I try to pass a range
object to a method that I've defined for my own class object.

Class Module: Spread

Public Sub Calculate(ByRef TheStrip as Range)

' Done nothing

End Sub

Program Module: Main

Public Function Spreads(ByRef TheStrip as Range)

Dim oSpread as Spread
Set oSpread = New Spread
oSpread.Calculate(TheStrip) 'error occurs here!

End Function

Would appreciate any help.

Regards,
Paul.

 

[wisccal]

Remove the parentheses around TheStrip like so:

oSpread.Calculate TheStrip

Alternatively, you could youse the Call keyword:

Call oSpread.Calculate(TheStrip)

Hope that helps.

Regards,
Steve

 

[Chip Pearson]

Change
oSpread.Calculate(TheStrip)
to
oSpread.Calculate TheStrip

When you use parentheses around an argument to a Sub procedure, that
indicates to VBA that you want to evaluate the variable's value before
passing it, passing the value of the argument rather than the argument
itself.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)