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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,792
Messages
5,513,466
Members
408,953
Latest member
Skiig

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top