Object oriented programming in vba

[M1kehailu]

Hello all, this is my first time posting here.
I would like some help visualizing a problem that I just cannot seem to do.
I know some sites do not allow to ask for help on coursework but I could not
find that in the rulebook here, and incase you need any more clarification,
yes it is my coursework for a uni project that I humbly ask for your help.

I have to structure a peice of software in VBA in an object oriented format
(i thought excel VBA lacked some inheritance functions that C++ has?) anyway,
my peice of software is to generate simple and log returns of 30 companies'
stock prices, generate the mean of those, then compute their variances.
For the life of me I cannot even begin to structure them into classes,
objects and whatnot, would anybody be kind enough to help a noob?
WARNING this is a 3 part question, i need help translating some other
functions too.
many thanks in advance to anyone's help

 

[Joel]

Excel VBA has two types of modules. Regular modules and class modules. You
can add either type using the VBA menu Insert.

You can use any library function in VBA that you can use in C++ if the
functions are declared properly. For example in VBA you can use Wind32 DLLs
by declaring the DLL and the function you want to use.

To use class modules in VBA your main function will be a non Class module.
Your main routine will call the class module initialization function(s). The
first class module initialization function can call the other class module
initialization functions.

I hope this will help.

 

[Bob Phillips]

You need to think of a class as the representation of your business model.
So you have companies, so you need a company class, you have stock prices,
so you need a stock price class. You may have others.

Identify all of the attributes (properties) of these objects, and actions
that operate on/by these objects (methods) and that defines the behaviour of
your class. You then need to code up the class modules to reflect this
behaviour.

You will need a collection class also, to collect the companies, to collect
the prices, etc.

 

[M1kehailu]

Thank you Bob

I will take on board what you said and get to work with what I have already
done. If I cannot get any further then I will ask for help again.

Many thanks again
M1keHailu

 

[gimme_this_gimme_that]

Classes don't only appear in class modules.

You can create classes like you would in other languages:

Class User
Private m_sAMAccountName
Public Property Let sAMAccountName(p_sAMAccountName)
m_sAMAccountName = UCase(p_sAMAccountName)
end Property

Public Property Get sAMAccountName()
sAMAccountName = m_sAMAccountName
End Property

Public Function MyFunction(var as String) as String
'do suff
MyFunction="a result"
End Function

End Class

Dim u as User
u.sAMAccountName="assdfd01"
s = u.MyFunction("test")

 

[Chip Pearson]

Classes don't only appear in class modules.

Class User ' snip
End Class

Not in VBA. You're thinking VBNET. In VBA, you have 1 class = 1 class
module. No more no less.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Chip Pearson]

I have an introduction to classes at
http://www.cpearson.com/excel/Classes.aspx. You might find it useful.

(i thought excel VBA lacked some inheritance functions that C++ has?)

That's very true. VBA has no inheritance at all. You can implement an
interface but one class cannot be derived from another. Also, there is
no support for overloading functions, a nice feature of C++ and the
NET languages.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[M1kehailu]

You will need a collection class also, to collect the companies, to collect

the prices, etc. - Could you elaborate? and perhaps provide with an example?

Many thanks

M1keHailu

 

[Bob Phillips]

Okay, in this example we have modelling a family. Obviously, a family is
made of people, so we have a person object/class, and there is more than one
person in the family, so the family is the collection class in this example.

First, we create a class called Person, and define its attributes as shown
here. Obviously, a person can have a lot more attributes, but you only
define those that you use in your model

Option Explicit

Private mmName As String
Private mmDOB As Date

Public Property Let Name(ByVal Name As String)
mmName = Name
End Property
Public Property Get Name() As String
Name = mmName
End Property


Public Property Let DateOfBirth(ByVal DoB As Date)
mmDOB = DoB
End Property
Public Property Get DateOfBirth() As Date
DateOfBirth = mmDOB
End Property

Public Property Get DayOfBirth() As String
DayOfBirth = Format(mmDOB, "dddd")
End Property


Then we have to create each person like so


Dim mpPerson1 As Person

Set mpPerson1 = New Person
With mpPerson1

.Name = "Bob"
.DateOfBirth = #9/16/1949#
mpPeople.Add mpPerson
End With
Set mpPerson = Nothing

to create another person we create another class

Dim mpPerson2 As Person


Set mpPerson2 = New Person
With mpPerson2

.Name = "Lynne"
.DateOfBirth = #4/5/1956#
mpPeople.Add mpPerson
End With
Set mpPerson = Nothing

Now we could just keep adding people like this, but we would have a lot of
individual person variables pointing at our Person objects, and we would
have to track them all within our code. As VBA supports collections, we can
create a collection class for the people, Family.

Before showing the code for Family, I'll show an example of how the Person
classes and Family class would be used

Public Sub CreateAFamily()
Dim mpFamily As Family
Dim mpPerson As Person

Set mpFamily = New Family

Set mpPerson = New Person
With mpPerson

.Name = "Bob"
.DateOfBirth = #9/16/1949#
mpFamily.Add mpPerson
End With
Set mpPerson = Nothing

Set mpPerson = New Person
With mpPerson

.Name = "Lynne"
.DateOfBirth = #4/5/1956#
mpFamily.Add mpPerson
End With
Set mpPerson = Nothing

For Each mpPerson In mpFamily
Debug.Print mpPerson.Name & " is a " & mpPerson.DayOfBirth & "'s
child"
Next mpPerson

Set mpFamily = Nothing

End Sub

Note that we create the Person class for each individual as before, but we
add it to the Family collection class, and then we can access the individual
Person objects through the collection class.

One other thing to notice is that we can create data witin the class, it
doesn't all have to be passed to it. In my example, the class calculates the
day the persosn was born on from the DoB.

Here is the Family class code

Option Explicit

Private mmPeople As Collection

Function NewEnum() As IUnknown
Set NewEnum = mmPeople.[_NewEnum]
End Function

Public Function Add(Being As Person)
mmPeople.Add Being, Being.Name
End Function

Public Property Get Count() As Long
Count = mmPeople.Count
End Property

Public Property Get Items() As Collection
Set Items = mmPeople
End Property

Public Property Get Item(Index As Variant) As Person
Set Item = mmPeople(Index)
End Property

Public Sub Remove(Index As Variant)
mmPeople.Remove Index
End Sub

Private Sub Class_Initialize()
Set mmPeople = New Collection
End Sub

Private Sub Class_Terminate()
Set mmPeople = Nothing
End Sub

 

[Bernd P]

Hello,

The shortest real life example with a (very!) reasonable background I
saw so far:
http://log4vba.everage.ca/

Regards,
Bernd