PDA

View Full Version : Creating an Object Model



vahidce
04-14-2013, 02:25 AM
Hello Everybody

This is my first post in this forum.

I'm going to create an Object Model for some building in VBA. So it begins with Building object at the top. Then it reduces to concepts like Grids, Stories and etc, then each of these reduce to other objects and all of these have their properties/methods.

Let me elaborate this with an example. I want to read the building model information from a text file, then create an object model of the building.

Let's say I have a 4 Story building that has 20 Beams in each story, and then each of these Beams have 8 Bars, these Bars have Areas, Length, Diameter and other properties.

I want to be able access them by using their indexes in their respective collections. For example, I want to access the Length property of the 2nd Bar in the 5th Beam in the 1st story of the whole building like this:


Building.Stories(1).Beams(5).Bars(2).Length


Accessing the Material of the 2nd Beam in the 3rd Story would be like this:


Building.Stories(3).Beams(2).Material

How can I code the object model part like above?


Best Regards
Vahid

sassora
04-14-2013, 03:58 AM
Hi Vahid, you may find the following interesting: http://stackoverflow.com/questions/2161666/what-are-the-benefits-of-using-classes-in-vba and http://www.cpearson.com/excel/classes.aspx

Paul_Hossler
04-14-2013, 07:36 AM
Looks like you already have the model defined, and it's just the VBA implimentation that you're asking about

This is a simple example to start you off.

4 classes, example properties, and a simple ConOps.



Option Explicit
Enum eMaterial
eGold = 1
eSilver = 2
eCopper = 3
eIron = 4
eOak = 5
End Enum
Dim aMtlList As Variant
Sub demo()
Dim SuperMarket As clsBuilding, DriveIn As clsBuilding

aMtlList = Array(vbNullString, "Gold", "Silver", "Coper", "Iron", "Oak")

Set SuperMarket = New clsBuilding

With SuperMarket

'story 1, 2 beams, 2 bars / beam
.AddStory

With .Story(1)
.AddBeam
With .Beam(1)
.AddBar
Call .Bar(1).Size(100, 50)
.Bar(1).Material = eGold

.AddBar
Call .Bar(2).Size(10, 5)
.Bar(2).Material = eSilver
End With

.AddBeam
With .Beam(2)
.AddBar
Call .Bar(1).Size(10, 5)
.Bar(1).Material = eIron

.AddBar
Call .Bar(2).Size(4, 2)
.Bar(2).Material = eOak
End With
End With

'story 1, 2 beams, 2 bars / beam
.AddStory

With .Story(2)
.AddBeam
With .Beam(1)
.AddBar
Call .Bar(1).Size(100, 50)
.Bar(1).Material = eGold

.AddBar
Call .Bar(2).Size(10, 5)
.Bar(2).Material = eSilver
End With

.AddBeam
With .Beam(2)
.AddBar
Call .Bar(1).Size(10, 5)
.Bar(1).Material = eIron

.AddBar
Call .Bar(2).Size(4, 2)
.Bar(2).Material = eOak
End With
End With

End With

MsgBox SuperMarket.NumberOfStories
MsgBox SuperMarket.Story(1).NumberOfBeams
MsgBox SuperMarket.Story(2).NumberOfBeams

MsgBox aMtlList(SuperMarket.Story(1).Beam(1).Bar(1).Material)

SuperMarket.Story(1).Beam(1).Bar(1).Material = eOak

MsgBox aMtlList(SuperMarket.Story(1).Beam(1).Bar(1).Material)


End Sub


I'd probably add a lot more error checking and some smarter and more complex properties. I used an Array, but you could use Collections


I'm 100% sure that the pro programmers here can offer a lot more ideas, but this is the basics. I'd like to learn more also

Paul

SamT
04-14-2013, 07:46 AM
Vahid,

Welcome to VBAX.

I have spent more time as a builder than as a coder, I started coding for myself and other builders so I have some experience in both those endeavors.

If you are the only person working on this project and you want a reasonably accurate model of a building, I think that about a year is enough time to complete it.

I believe that you will need to use Classes as Sassora hinted at.

I have attached a book with a list of almost all physical objects found in buildings and an example of an Object Model description laid out in a spreadsheet for you to look at for ideas.

vahidce
04-17-2013, 02:25 AM
Thanks Sassora, I hadn't seen the first link.

vahidce
04-17-2013, 02:29 AM
Hi Paul, this worked. Thank you so much, there is so much to learn from this code. A good friend of mine, has helped me write one with collections, unfortunately I'm new and I can't attach it here. If you are interested, please let me know I will send you the code.

vahidce
04-17-2013, 02:31 AM
SamT

Actually I'm a structural engineer myself, I'm trying to write a comprehensive program to accept model information and design data and then draw the structural details.

SamT
04-17-2013, 08:43 AM
Vahid,

It is my experience that the very first thing one must accomplish when designing an application is understand all the data structures you must work with. Excel Lists are a great way to do this. That is a prerequisite to knowing what to do with the data.

Here are two examples of an object Class of the simplest physical construction object I can think of, ie, nails. The first uses the tables in the attached spreadsheet to store nail characteristics, while the other stands alone. Both are very incomplete, just the barest essentials to give an understanding of Classes.

Option Explicit
'Used With Sheet "Fasteners"

Dim c_ShearStrength As Double
Dim c_PullOutResistance As Double
Dim c_Length As Single
Dim c_Diameter As Double
Dim c_Weight As Double
Dim c_Size As String
Dim c_Type As String

Private Enum ColumnNumbers
SizeCol = 1
ShearCol
PullOutCol
LenCol
DiaCol
WtCol
End Enum

Private Const shtNails As String = "Fasteners"

Private Sub Class_Intialize(NailSize As String, NailType As String)
c_Size = GetSize(NailSize)
c_Type = GetType(NailType)
c_Weight = WorksheetFunction.VLookup(c_Size, Sheets(shtNails).c_Type, WtCol, True)
c_Length = WorksheetFunction.VLookup(c_Size, Sheets(shtNails).c_Type, LenCol, True)
'c_ShearStrength, c_Diameter, Etc,
End Sub

Private Sub GetSize(SizeNail As String)
'Called from Class initialize sub

Select Case SizeNail
Case "8"
c_Size = "8d" 'Must match Column 1 of Vlookup Table
Case "8d"
c_Size = "8d"
Case "8p"
c_Size = "8d"
'Repeat above for all possible nomenclatures
Case "16"
'Repeat above for all possible size nails
End Select
End Sub

Private Sub GetType(TypeNail As String)
'Called from Class initialize sub

Select Case TypeNail
Case "Galvanized"
c_Type = "Galv" 'Must match Defined Name of Vlookup Table
'Repeat above for all possible nomenclatures
Case "VinylCoatedSinker"
c_Type = "VCS"
'Repeat for all types
End Select
End Sub

Property Get Weight() As Double
Weight = c_Weight
End Property

Property Get Length() As Double
Length = c_Length
End Property

'Repeat Property Gets as needed for all nail characteristics

[/vba]
Option Explicit
'True object, needs no external reference

Dim c_ShearStrength As Double
Dim c_PullOutResistance As Double
Dim c_Length As Single
Dim c_Diameter As Double
Dim c_Weight As Double
Dim c_Size As String
Dim c_Type As String

Private Enum WeightGalv
Galv6 = 20
Galv8 = 30
Galv16 = 60
End Enum

Property Let Size(SizeNail As String)
Select Case SizeNail
Case "8"
c_Size = "8d"
Case "8d"
c_Size = "8d"
Case "8p"
c_Size = "8d"
Case "16"
'Repeat above for 16d nails
'Repeat above for all possible size nails
End Select
End Property

Property Let NailType(TypeNail As String)
Select Case TypeNail
Case "Galvanized"
c_Type = "Galv"
'Repeat for all type
Case "VinylCoatedSinker"
c_Type = "VCS"
End Property

Property Get Weight() As Double
Weight = GetWeight()
End Property

Private Sub GetWeight()
Select Case c_Type
Case "Galv"
Select Case c_Size
Case "8d"
GetWeight = Galv8
Case "16d"
GetWeight = Galv16
End Select
Case "VCS"
Select Case c_Size
Case "16d"
GetWeight = VCS16
End Select
'Repeat for all cases
End Select
End Sub

End Sub

vahidce
04-27-2013, 01:00 AM
Hello Everybody,

I have come with a solution to this. I post it here for others if they ever visit this thread.

Thanks