Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wondering where the information comes from...
For those of you who are obviously quite well versed in VBA, I only ask how
did you become so familiar with it? Was this brute force learning, supervised college courses, on the job experience, or something else. As an example, I am pretty sure that I need to start implmenting class modules, but I honestly feel completely overwhelmed. If there is some resource that can concisely and completely inform me as to their workings, I would appreciate someone pointing the way for me. wazooli |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wondering where the information comes from...
I am sure that everyone gets there by a different method, but the critical
factor IMO is actually using in a real-world situation. Courses, classes et al are all very good to get you kick-started, but usage is where you develop the real skills. On the other point, what is it that makes you think you need classes? Whilst very handy, and in some rare cases absolutely imperative, you can often achieve your objective without them. -- HTH RP (remove nothere from the email address if mailing direct) "Wazooli" wrote in message ... For those of you who are obviously quite well versed in VBA, I only ask how did you become so familiar with it? Was this brute force learning, supervised college courses, on the job experience, or something else. As an example, I am pretty sure that I need to start implmenting class modules, but I honestly feel completely overwhelmed. If there is some resource that can concisely and completely inform me as to their workings, I would appreciate someone pointing the way for me. wazooli |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wondering where the information comes from...
MSDN Link
http://msdn.microsoft.com/library/de...hatisclass.asp Chip Pearson's site http://cpearson.com/excel/codemods.htm A previous post from Chip on the subject:- There is far to much to say about Class Modules and Objects for a simple newsgroup post, so I'll try to cover a few of the basics. Basically, a Class is the definition of an Object. The word "object" is deliberately vague. And object is anything that you want to design. It is defined entirely (mostly) by its properties, methods, and events. In Excel, there are hundreds of "built-in" objects, all defined by class modules. The "class" is the definition of an "object". For example, a Worksheet is an object. And there is a class module which defines just what a Worksheet really is. There are various properties of a Worksheet object (e.g., Visible). Properties simply define and set various attributes. Think of properties as "Adjectives" which describe an object. An object also has Methods. Methods are the "Verbs" of objects. For example, a Worksheet object has an Activate method. This causes something to happen. Finally there are Events. I can't think of a good grammatical analogy for events. Essentially, Events are how an object tells the rest of the world that something happened. For example, in a Worksheet object, there is a Change event. This is the Worksheet object's way of telling the rest of world, "Hey, look at me, I changed". The rest of the world can ignore that event, or it may take action. But the world has been told that object has done something (or had done something to it). Now, you use Class Modules to create your own objects. Suppose you were writing an application that was used for employee tracking. Using a class module, you would define your own object called "Employee". This class would define a single, generic, employee. With the DIM and SET statement, you can create a specific employee, based on the "template" or "definition" of a generic employee. The Employee class would have several Properties, such as Name, Address, and Salary. It could also have methods, such as Promote, GiveRaise, and Fire. In your application, the Promote method would do the same things -- e.g., increasing the Salary property, updating a central database, sending an email to another department to buy him a nicer computer, etc. These actions are all the same whenever you Promote any employee. By using a Class Module to define a "generic" employee, you only have to write the code once. Then to work with a *specific* employee, you just call the methods for that employee: Dim ThisEmp As CEmployee ' more code Set ThisEmp = New CEmployee ThisEmp.Name = "John Smith" ' more code ThisEmp.Promote All of the code related to the Promote event is contained in the Class modules (the definition of any employee), so you can simply call the Promote method. Once you've defined the Class, you never have to worry about what Promote actually does. Here's another way to think about it. In the Worksheet object, there is a PrintOut method. Within the PrintOut method, there is all the code that actually formats the worksheet for printing, determines what printer you have, and actually does all the work of printing the sheet. As a VBA programmer, you don't have to worry about any of that. You simply call PrintOut, and let that do all the work for you. You don't have to worry about what sort of printer the user has, whether it can print color, and a hundred other things. You just call PrintOut and let the Object do all the work. Class Modules let you create you own objects, or extend the functionality of other, existing objects. They are very useful because they allow you to write the code once, and then simply create new objects based on the class (think of it like a blueprint for a house). It is write the code once, and use it many times. For example, I have a class module that extends the functionality of a standard list box. The standard list box doesn't have a MoveUp method, which simply moves the selected item one row up in the list. By using a Class Module, I added a MoveUp method (as well as MoveDown, MoveToTop, MoveToBottom, etc). I wrote that class one time. Now, whenever I need to use "better" list boxes in my applications, I just use that Class. I don't have to "re-invent the wheel" for every application I write. This just scratches the surface of what a Class is and how to use them. If you've ever heard the term "object oriented", Classes are the foundation of this entire design philosophy. ----------------------------------------------------- And from the same post, by Stephen Bullen:- ----------------------------------------------------- Just to provide the opposite end of the spectrum to Chip's excellent answer, class modules can also be though of as user-defined types (UDT) on steroids. A simple UDT can be used to store related information about a particular thing, such as Chip's employee: Type Employee Name As String DOB As String Grade As String Salary As Double End Type If you wanted to do stuff with an employee, you'd use a normal procedure somewhe Sub RaiseEmployee(uEmp As Employee, sNewGrade As String) 'Validate Grade '... uEmp.Grade = sNew Grade 'Do stuff to work out new salary etc. '... End Sub Sub FireEmployee(uEmp As Employee) uEmp.Grade = "F" uEmp.Salary = 0 End Sub Sub SetSalary(uEmp As Employee, dNewSalary As Double) 'Validate Salary '... 'Does new salary mean a new grade? '... End Sub etc. That's fine as far as it goes and you can create some great programs without ever using class modules. The main thing wrong with it is that the *data* for the object (i.e. the contents of your UDT) is separated from the *actions* that are performed on the data (the RaiseEmployee and FireEmployee subs). Hence, you have to be very careful that the same validation is performed in each sub and that one sub doesn't alter the data in a way that will cause another sub to fail; this is often the cause of some of the hardest bugs to find - logic problems. If you use a class module instead, you can include the validation and other functionality *with* the data; to the extent that the data can *not* be changed unless it's valid. You can think of it as that the 'Grade' property of the Employee (for example) can validate *itself* and can refuse to be updated, or it can know *itself* that when it changes to a valid new grade, it needs to change the salary too. In the example above, with two simple procedures, think what would happen if we had to add another check before changing the grade, or introduce a new action to be performed if the grade is changed (such as notifying their manager). In the procedural approach, we'd have to change two or three routines to handle it - i.e. wherever the grade is set. In the clas module approach, it is simply another action to be performed by the 'grade' property *itself* - none of the other code needs to know about it. i.e: Dim msGrade As String 'Data that only code in the class can 'see' 'Property to read the grade Public Property Get Grade() As String: Grade = msGrade: End property 'Property to set the grade Public Property Let Grade(sNew As String) If Not sNew Is Valid Then Err.Raise "Not a valid grade" Exit Property End If 'Grade is valid, so we can safely store it msGrade = sNew 'Now what else do we need to do when the grade changes? Select Case sNew Case "F" 'Being fired, better ask for a redundancy slip Salary = 0 Case "M" 'Being made a manager, better ask for a better car 'Increase the Salary too Case "D" 'Being demoted, schedule for more frequent reviews 'Decrease the Salary 'etc End Select End Property Public Sub Fire() Grade = "F" End Sub Now, everything that needs to be done when the grade is changed has been made an *integral* part of changing the grade - there's no way that the grade can be changed by anywhere else in the system without those checks and actions happening. Really, though, it just boils down to a different design and development style, and one that hopefully takes us further down the road of improved code reuse, more stability and fewer opportunities for bugs to creep in. The hardest thing to work out, though, is to decide which functionality should be 'in' the class module and which should be on the outside, but using the class module. For example, do we have a '.Fire' method within the class, or a Fire(oEmp As Employee) procedure outside that just sets the grade to "F"? I find that I'm using class modules more and more; it's almost at the stage where if I'm asked "Why use a class module", my reply is "Why not?" -------------------------------------------------------- And then just for the hell of it, Chip's addendum after reading Stephen's comments:- Chip Pearson May 18 2000, 12:00 am -------------------------------------------------------- Newsgroups: microsoft.public.excel.programming From: "Chip Pearson" - Find messages by this author Date: 2000/05/18 Subject: Just what is a Class Module? Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse "Stephen Bullen" wrote class modules can also be thought of as user-defined types (UDT) on steroids Excellent. You can think of Classes as "UDTs with verbs", or UDTs that "do things". I find that I'm using class modules more and more; it's almost at the stage where if I'm asked "Why use a class module", my reply is "Why not?" Very true. I've found that I do most things now in classes. Write once, instantiate the class, and it is all done. And once I figured out the "Implements" business, things got even better. (The help files are rather poor, here. You need to read MSDN msdn.microsoft.com) I'm sure there is some overhead involved in creating new objects, but RAM is cheap and the reliability and maintenance considerations exceed the resource usages. code reuse, more stability and fewer opportunities for bugs to creep in. From a practical matter, this cannot be over-estimated or over-stated.. Far too many bugs and problems are introduced by duplicated code. Classes allow you to write the code once, debug it there, and only have to maintain one procedure (or set of procedures). For "professional quality" applications, this is absolutely essential. As Stephen points out, a well-designed object model, accomplished with the use of well-thought-out class modules, will tremendously reduce the potential for bugs in your application. If you have VB5 or 6, you have a Class Builder add-in. Learn it and love it. It is not perfect, but can simplify the creation of your objects. You can then Import these modules into your Excel VBProject. The hardest thing to work out, though, is to decide which functionality should be 'in' the class module and which should be on the outside, but using the class module. For example, do we have a '.Fire' method within the class, or a Fire(oEmp As Employee) procedure outside that just sets the grade to "F"? Indeed, that is a "design philosophy" question. The answer is based on the developer's requirements, not purely technical considerations. There is no simple "right" way to decide this. It depends on what "Firing" an employee really means. Suppose you have three categories of employees: Executives, Supervisors, and Hourly. The process of "Firing" each type of employee may be quite different. In Excel2000 (VBA6), you can Implement the right class to have either a generic "Fire" method if that is appropriate, or a particular "Fire" method for each type of employee, and automatically have the "Fire" method for the appropriate Type of worker. This functionality was added in Excel2000 (VBA6) and is not available in Excel97 (VBA5). Admittedly, it is a bit confusing until you get used to it. But then, you've go a great tool at your disposal. Chapter 9 in the VB6 Programmer's Guide has some nice basic info about all of this. But to really learn about this, just experiment and play. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Wazooli" wrote in message ... For those of you who are obviously quite well versed in VBA, I only ask how did you become so familiar with it? Was this brute force learning, supervised college courses, on the job experience, or something else. As an example, I am pretty sure that I need to start implmenting class modules, but I honestly feel completely overwhelmed. If there is some resource that can concisely and completely inform me as to their workings, I would appreciate someone pointing the way for me. wazooli |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wondering where the information comes from...
I think I need classes because I would like to have a bunch of combo boxes
that all act the same, but get populated form different places. In case you haven't been following along in my arduous and excruciating learning process, here is a brief synopsis of what I am attempting: I have a sheet with several columns, describing all of the experiments I have performed thus far at the company I work at. Besides several columns that contain dates, lot numbers, and application codes, I have a fourth column that contains descriptors of the experiments. There is a subclass of experiments that are rather complicated. I currently have combo boxes for these complicated experiments that contain filenames (retrieved from an auxiliary sheet in the same workbook). With the help of yourself, as well as Tom Ogilvie, I have been able to write sub calls that enable me to open whichever workbook is chosen in the drop down. What I would like to do now is to have a secondary combo box open for any given first choice, that is populated with a listing of sheets within the chosen workbook. The reason for this is that some of these sheets have many thousands of rather tedious caluclations, and they tend to bog my machine down. The need for classes is the result of not wanting to have to 'hard code' in the names of sheets. I want a class module that will create the sub-combo box, populate it based upon the file name in the first combo box, and enable me to choose the appropriate worksheet. I woudl then like the secondary combo box to disappear. I hope this clears things up, and helps to shed some light on why I feel so overwhelmed. I feel I am trying to bite off more than I can chew, hence the call for guidance. wazooli "Bob Phillips" wrote: I am sure that everyone gets there by a different method, but the critical factor IMO is actually using in a real-world situation. Courses, classes et al are all very good to get you kick-started, but usage is where you develop the real skills. On the other point, what is it that makes you think you need classes? Whilst very handy, and in some rare cases absolutely imperative, you can often achieve your objective without them. -- HTH RP (remove nothere from the email address if mailing direct) "Wazooli" wrote in message ... For those of you who are obviously quite well versed in VBA, I only ask how did you become so familiar with it? Was this brute force learning, supervised college courses, on the job experience, or something else. As an example, I am pretty sure that I need to start implmenting class modules, but I honestly feel completely overwhelmed. If there is some resource that can concisely and completely inform me as to their workings, I would appreciate someone pointing the way for me. wazooli |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wondering where the information comes from...
Wazooli,
I gave a response a few days ago that allows controlling multiple comboboxes from a single control class. You can read that thread on Google at http://tinyurl.com/6a6w7. There was also a previous related question http://tinyurl.com/64oed at and http://tinyurl.com/3mje3. However, I think you want dynamic comboboxes for what you describe. Check out this web page http://www.xldynamic.com/source/xld.Dropdowns.html. -- HTH RP (remove nothere from the email address if mailing direct) "Wazooli" wrote in message ... I think I need classes because I would like to have a bunch of combo boxes that all act the same, but get populated form different places. In case you haven't been following along in my arduous and excruciating learning process, here is a brief synopsis of what I am attempting: I have a sheet with several columns, describing all of the experiments I have performed thus far at the company I work at. Besides several columns that contain dates, lot numbers, and application codes, I have a fourth column that contains descriptors of the experiments. There is a subclass of experiments that are rather complicated. I currently have combo boxes for these complicated experiments that contain filenames (retrieved from an auxiliary sheet in the same workbook). With the help of yourself, as well as Tom Ogilvie, I have been able to write sub calls that enable me to open whichever workbook is chosen in the drop down. What I would like to do now is to have a secondary combo box open for any given first choice, that is populated with a listing of sheets within the chosen workbook. The reason for this is that some of these sheets have many thousands of rather tedious caluclations, and they tend to bog my machine down. The need for classes is the result of not wanting to have to 'hard code' in the names of sheets. I want a class module that will create the sub-combo box, populate it based upon the file name in the first combo box, and enable me to choose the appropriate worksheet. I woudl then like the secondary combo box to disappear. I hope this clears things up, and helps to shed some light on why I feel so overwhelmed. I feel I am trying to bite off more than I can chew, hence the call for guidance. wazooli "Bob Phillips" wrote: I am sure that everyone gets there by a different method, but the critical factor IMO is actually using in a real-world situation. Courses, classes et al are all very good to get you kick-started, but usage is where you develop the real skills. On the other point, what is it that makes you think you need classes? Whilst very handy, and in some rare cases absolutely imperative, you can often achieve your objective without them. -- HTH RP (remove nothere from the email address if mailing direct) "Wazooli" wrote in message ... For those of you who are obviously quite well versed in VBA, I only ask how did you become so familiar with it? Was this brute force learning, supervised college courses, on the job experience, or something else. As an example, I am pretty sure that I need to start implmenting class modules, but I honestly feel completely overwhelmed. If there is some resource that can concisely and completely inform me as to their workings, I would appreciate someone pointing the way for me. wazooli |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nothing to do with Excel....wondering if there is a way to check.. | Excel Discussion (Misc queries) | |||
I am wondering if someone could help me solve this equation | Excel Discussion (Misc queries) | |||
Just wondering | Excel Programming | |||
Wondering if there is a way? | Excel Programming | |||
Wondering If Possible | Excel Programming |