VBA - Create an instance of a class and pass it to a function - how?

digitalformula

New Member
Joined
Jul 28, 2010
Messages
10
Hi all,

I've got a small class module containing 5 properties, all with Get and Let methods. I can create an instance of the class and assign values to the properties, like this:

Dim currentSite As New Site
currentSite.DateRange = "B9:AF9"

The 'currentSite' variable then needs to be passed to a function so that the function can access the instance properties and act on properties. My function declaration looks like this:

Sub AnalyseRange2(siteData As Site)

When the function is called, I just get this:

Run-time error '438':
Object doesn't support this property or method.

Seems self-explanatory enough but all I'm doing is passing an instance of the class, nothing else. I've tried passing the variable ByRef and ByVal - neither of those make any difference.

Can anyone help solve this?

Thanks! :)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

James_B

Active Member
Joined
Jun 11, 2009
Messages
447
What syntax are you using to pass it to the function exactly? (The reason I ask is because your example is a sub routine rather than a function, which may be the cause of the problem, or of course it may just be that I'm being tedious)
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Not to be nit picky, but you say you are passing an instance of your site class to a function. But in your post, you use 'Sub'.

Either way, it should work. I think something is going in in that function or in one of your property get procedures with your Site class.

What happens when you step through the code. Specifically as you step through the line that calls your (function?), do you get into the (function?) at all? At what line within that function does it error out?

I am curious about
Site.DateRange

Is it really a string? Or is it a Range? What is the data type?
 

digitalformula

New Member
Joined
Jul 28, 2010
Messages
10
Not to be nit picky, but you say you are passing an instance of your site class to a function. But in your post, you use 'Sub'.

You're right - bad choice of words on my part. It is a sub, not a function, and it returns nothing.

Either way, it should work. I think something is going in in that function or in one of your property get procedures with your Site class.

What happens when you step through the code. Specifically as you step through the line that calls your (function?), do you get into the (function?) at all? At what line within that function does it error out?

I can't step through it at all. I've added a breakpoint to the first valid line of the AnalyseRange2 sub but it doesn't even get that far.

I've got another sub that looks like this:

Sub PopulateMatrix()
Dim currentSite As New Site
currentSite.DateRange = "B9:AF9"
currentSite.SuccessCellOffset = 1
currentSite.DateCellOffset = 3
currentSite.SpecNameRange = "A10:A12"
currentSite.MatrixRange = "B10:AF12"
AnalyseRange2 (currentSite)
End Sub

Stepping through that simply throws the 438 runtime error as soon as it hits the line that calls AnalyseRange2.

I am curious about
Site.DateRange

Is it really a string? Or is it a Range? What is the data type?

Yes, it's really a string:

Private m_DateRange As String

Public Property Get DateRange() As String
DateRange = m_DateRange
End Property
Public Property Let DateRange(Value As String)
m_DateRange = Value
End Property

I made it a string for a number of reasons.

Technically speaking it's not be the best name for the property, hence the confusion. It's a row of cells that contains the numbers 1-31 that represent the days in a 31-day month.
 

James_B

Active Member
Joined
Jun 11, 2009
Messages
447

ADVERTISEMENT

Try it without the brackets, i.e.

Code:
AnalyseRange2 currentSite
 

digitalformula

New Member
Joined
Jul 28, 2010
Messages
10
Try it without the brackets, i.e.

Code:
AnalyseRange2 currentSite

LOL! You gotta be kidding me Excel/VBA - that worked.

My bad for doing all my dev so far in C# ... passing parameters without brackets seems ... stupid.

Thanks. :)
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Just out of curiosity, does it work if you use:

Call AnalyseRange2 (currentSite)

instead of

AnalyseRange2 (currentSite)
 

James_B

Active Member
Joined
Jun 11, 2009
Messages
447
Yeah, I know; I fell foul of that a number of times due to C++ familiarity.

Just for interest's sake, I'm pretty sure you can run a function and ignore its return values using

Code:
Call Fn()

Or if the function is void (returnless) you can probably do it with

Code:
Fn(Arg1, Arg2)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,486
Messages
5,548,350
Members
410,828
Latest member
A9Bosv3
Top