Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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. "M1kehailu" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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. -- __________________________________ HTH Bob "M1kehailu" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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 "Bob Phillips" wrote: 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. -- __________________________________ HTH Bob "M1kehailu" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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) On Mon, 16 Feb 2009 14:39:12 -0800 (PST), " wrote: 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") |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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) On Mon, 16 Feb 2009 04:04:00 -0800, M1kehailu wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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" wrote: 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. -- __________________________________ HTH Bob "M1kehailu" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
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 -- __________________________________ HTH Bob "M1kehailu" wrote in message ... 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" wrote: 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. -- __________________________________ HTH Bob "M1kehailu" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object oriented programming in vba
Hello,
The shortest real life example with a (very!) reasonable background I saw so far: http://log4vba.everage.ca/ Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Location Oriented Formula | Excel Worksheet Functions | |||
Programming a chart object | Excel Programming | |||
Need help with Range object in Macro programming | Excel Programming | |||
Protected Row-Oriented Data Entry | Excel Worksheet Functions | |||
Programming code to new object | Excel Programming |