I might be being a little too tricky with Excel here but because there
doesn't seem to be support for overloading constructors with Class Modules in
Excel I've created a Factory class to instantiate objects and return them. I
have an "Employee" Class Module and a "Factory" Class Module and I'm trying
to get the factory to instantiate (and populate) an employee object based on
an id parameter.

Factory code:
Public Function getEmployee(id As Integer) As employee
Dim emp As employee
Dim staffSheet As Worksheet
Dim staffIDs As Range
Dim staffRow As Integer

Set staffSheet = Worksheets("Staff")
Set staffIDs = staffSheet.Range("A1",
staffSheet.Range("A65536").End(xlUp))

staffRow = staffIDs.Find(id).Row

Debug.Print staffRow

If staffRow < 1 Then
getEmployee = Null
Exit Function
End If

Set emp = New employee
getEmployee = emp
End Function

Yes - I know - at the moment it doesn't populate, but that's because I can't
even get this code to work properly :-)

The method that calls this code is:
Set factory = New factory
Set employee = factory.getEmployee(1)
with both the "factory" and "employee" declared in the general sections.

I'm getting a runtime error 91 - which I know - on the "set employee" line
but I'm at a loss as to why this might be happening. The Debug.Print line in
the factory code correctly reports the row number of the employee(1) as "2".

Any ideas what I'm doing wrong here? I've tried both "employee=factory..."
and "set employee=factory..." with the same error!

"George" wrote in message
...


If staffRow < 1 Then
getEmployee = Null
Exit Function
End If

Set emp = New employee
getEmployee = emp
End Function


You need to use the Set keyword /every/ time you assign an object (including
a return value of a function):

If staffRow < 1 Then
set getEmployee = Nothing ' Note that 'Nothing' is a null object
in VB
Exit Function
End If

Set emp = New employee
Set getEmployee = emp

Rob

I think you need set statements within your getemployee function since your
function returns an object.

Set getEmployee = Null
Set getEmployee = emp


"George" wrote:

I might be being a little too tricky with Excel here but because there
doesn't seem to be support for overloading constructors with Class Modules in
Excel I've created a Factory class to instantiate objects and return them. I
have an "Employee" Class Module and a "Factory" Class Module and I'm trying
to get the factory to instantiate (and populate) an employee object based on
an id parameter.

Factory code:
Public Function getEmployee(id As Integer) As employee
Dim emp As employee
Dim staffSheet As Worksheet
Dim staffIDs As Range
Dim staffRow As Integer

Set staffSheet = Worksheets("Staff")
Set staffIDs = staffSheet.Range("A1",
staffSheet.Range("A65536").End(xlUp))

staffRow = staffIDs.Find(id).Row

Debug.Print staffRow

If staffRow < 1 Then
getEmployee = Null
Exit Function
End If

Set emp = New employee
getEmployee = emp
End Function

Yes - I know - at the moment it doesn't populate, but that's because I can't
even get this code to work properly :-)

The method that calls this code is:
Set factory = New factory
Set employee = factory.getEmployee(1)
with both the "factory" and "employee" declared in the general sections.

I'm getting a runtime error 91 - which I know - on the "set employee" line
but I'm at a loss as to why this might be happening. The Debug.Print line in
the factory code correctly reports the row number of the employee(1) as "2".

Any ideas what I'm doing wrong here? I've tried both "employee=factory..."
and "set employee=factory..." with the same error!

Also, I'm not positive about this:
Set getEmployee = Null

Usually, I would use:
Set getEmployee = Nothing



"JMB" wrote:

I think you need set statements within your getemployee function since your
function returns an object.

Set getEmployee = Null
Set getEmployee = emp


"George" wrote:

I might be being a little too tricky with Excel here but because there
doesn't seem to be support for overloading constructors with Class Modules in
Excel I've created a Factory class to instantiate objects and return them. I
have an "Employee" Class Module and a "Factory" Class Module and I'm trying
to get the factory to instantiate (and populate) an employee object based on
an id parameter.

Factory code:
Public Function getEmployee(id As Integer) As employee
Dim emp As employee
Dim staffSheet As Worksheet
Dim staffIDs As Range
Dim staffRow As Integer

Set staffSheet = Worksheets("Staff")
Set staffIDs = staffSheet.Range("A1",
staffSheet.Range("A65536").End(xlUp))

staffRow = staffIDs.Find(id).Row

Debug.Print staffRow

If staffRow < 1 Then
getEmployee = Null
Exit Function
End If

Set emp = New employee
getEmployee = emp
End Function

Yes - I know - at the moment it doesn't populate, but that's because I can't
even get this code to work properly :-)

The method that calls this code is:
Set factory = New factory
Set employee = factory.getEmployee(1)
with both the "factory" and "employee" declared in the general sections.

I'm getting a runtime error 91 - which I know - on the "set employee" line
but I'm at a loss as to why this might be happening. The Debug.Print line in
the factory code correctly reports the row number of the employee(1) as "2".

Any ideas what I'm doing wrong here? I've tried both "employee=factory..."
and "set employee=factory..." with the same error!

Jim - thanks for the response. You're quite right about the "Nothing" - the
"Null" is a hangup from my experience with other languages :-)

I've gone the whole hog with your suggestion as well and tried:

Factory.getEmployee :-
Set getEmployee = Nothing
Set emp = Nothing
Set emp = New Employee
Set getEmployee = emp

As well as setting "employee" to nothing before the call to getEmployee
however I'm still getting the runtime 91 error.

Any other ideas about what I'm doing wrong - I'm still thinking it's my code
rather than anything else!

Interestingly I get the same error if I try to find an ID that I know
doesn't exist. Not sure if that helps, but it might.
Thanks

"JMB" wrote:

I think you need set statements within your getemployee function since your
function returns an object.

Set getEmployee = Null
Set getEmployee = emp


"George" wrote:

I might be being a little too tricky with Excel here but because there
doesn't seem to be support for overloading constructors with Class Modules in
Excel I've created a Factory class to instantiate objects and return them. I
have an "Employee" Class Module and a "Factory" Class Module and I'm trying
to get the factory to instantiate (and populate) an employee object based on
an id parameter.

Factory code:
Public Function getEmployee(id As Integer) As employee
Dim emp As employee
Dim staffSheet As Worksheet
Dim staffIDs As Range
Dim staffRow As Integer

Set staffSheet = Worksheets("Staff")
Set staffIDs = staffSheet.Range("A1",
staffSheet.Range("A65536").End(xlUp))

staffRow = staffIDs.Find(id).Row

Debug.Print staffRow

If staffRow < 1 Then
getEmployee = Null
Exit Function
End If

Set emp = New employee
getEmployee = emp
End Function

Yes - I know - at the moment it doesn't populate, but that's because I can't
even get this code to work properly :-)

The method that calls this code is:
Set factory = New factory
Set employee = factory.getEmployee(1)
with both the "factory" and "employee" declared in the general sections.

I'm getting a runtime error 91 - which I know - on the "set employee" line
but I'm at a loss as to why this might be happening. The Debug.Print line in
the factory code correctly reports the row number of the employee(1) as "2".

Any ideas what I'm doing wrong here? I've tried both "employee=factory..."
and "set employee=factory..." with the same error!

And now I have it working - except when there's no employee to return, but I
can work that out. I could have sworn I tried all the combinations but putting

I think in all my crazy late night editing last night I removed the "Set"
statement from the caller method - now that's back in your suggestion is just
the ticket Jim!

Thanks for the assistance!

George


Jim - thanks for the response. You're quite right about the "Nothing" - the
"Null" is a hangup from my experience with other languages :-)

I've gone the whole hog with your suggestion as well and tried:

Factory.getEmployee :-
Set getEmployee = Nothing
Set emp = Nothing
Set emp = New Employee
Set getEmployee = emp

As well as setting "employee" to nothing before the call to getEmployee
however I'm still getting the runtime 91 error.

Any other ideas about what I'm doing wrong - I'm still thinking it's my code
rather than anything else!

Interestingly I get the same error if I try to find an ID that I know
doesn't exist. Not sure if that helps, but it might.
Thanks

"JMB" wrote:

I think you need set statements within your getemployee function since your
function returns an object.

Set getEmployee = Null
Set getEmployee = emp