Creating a user defined class 1

[lucrahouse]

I was trying to learn something new. I wanted to take an example from my book, "Teach Yourself Excel in 21 Days", on page 457, was an example of a user defined class called electric heater. I want to be able to create multiple heaters and name each one something different. Here is an example of my code so far that I have written on the class module page:

Example below is from class module called: ElectricHeater
Option Explicit

Const ehTitle = "Electric Heater"

Dim ehName As String

Property Let Name(y As String)
ehName = y
End Property

Property Get Name() As String
Name = ehName
End Property

and here is an example of code which I'm using to try to create more than one heate and see if I'm successful. But I failed.

Example below is from class module called: Module1

Sub N_heater()
Dim x As Integer
Dim objHeater As ElectricHeater
Set objHeater = New ElectricHeater
objHeater.Name = "ABC"

Set objHeater = New ElectricHeater
objHeater.Name = "DEF"

For x = 1 To 2
MsgBox "Heater name is " & objHeater.Name
Next x
End Sub

I thought I could create a collection of heaters and call them back by name. If I can call them back by name, then maybe I can call back the other variables which I want to assign to them later. Can you help me with this idea. Any instructions would be appreciated.

 

[TonyJollans]

Hi lucrahouse,

There is nothing wrong with your Class as far as it goes. Your problem is simply in the code module (which, you should note is NOT (or should not be) a Class Module).

After setting the name of your first ElectricHeater you throw away your reference to it by assigning the Object variable (objHeater) to a new Heater. Your little loop then doesn't use the loop control variable, x (not that, as it stands, it could), for anything and simply references the same objHeater twice.

If you want a Collection to allow you to do as you say, you need to create it, and add the Heaters to it ...

[blue]Sub N_heater()
Dim x As Integer

[red]Dim Heaters As Collection
Set Heaters = New Collection
[/red]
Dim objHeater As ElectricHeater
Set objHeater = New ElectricHeater
objHeater.Name = "ABC"
[red]Heaters.Add objHeater, objHeater.Name[/red]

[green]' Now you have saved the reference you can reuse the pointer[/green]

Set objHeater = New ElectricHeater
objHeater.Name = "DEF"
[red]Heaters.Add objHeater, objHeater.Name[/red]

[green]' This references the Items in the Collection by index number[/green]
For x = 1 To 2
  MsgBox "Heater name is " & Heaters[red](x)[/red].Name
Next x

[green]' You can also reference them by name, for example ...[/green]
MsgBox Heaters("ABC").Name

End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[lucrahouse]

Thank you Tony for such a quick respone. Your code has proven to be an excellent lesson. I hope to call these objects and assign additional variables to them now. Before I had been using tables or arrays and looping through them to find the exact reference has always taken longer than I thought it should. This will help my code building and cut the processing time dramatically. Thank you again for your assistance.