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

[digitalformula]

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!

 

[James_B]

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]

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]

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]

Try it without the brackets, i.e.

AnalyseRange2 currentSite

 

[digitalformula]

Try it without the brackets, i.e.

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]

Just out of curiosity, does it work if you use:

Call AnalyseRange2 (currentSite)

instead of

AnalyseRange2 (currentSite)

 

[James_B]

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

Call Fn()

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

Fn(Arg1, Arg2)