Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to create Events (XP)

    Hello everybody!

    I have a class clsPoint which is very simple. It only contains an x and a y-coordinate. Every time a coordinate changes, I want a feedback (lets say a messagebox or a changing text in a cell) To realise it, I would like to create an event. I have searched a lot in the internet, also in this forum, but unfortunately did not find something that helped or easy enough.
    I am a beginner using classes and I want to understand the basic concepts behind it. So if there is somebody out there, who wouldn't mind to explain or give a short example, it would be great.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to create Events (XP)

    Not quite what you ask, but you could do something like this:

    Class module clsPoint:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private MyX As Double
    Private MyY As Double

    Public Property Let X(XVal As Double)
    MyX = XVal
    Debug.Print "X changed to " & XVal
    End Property

    Public Property Let Y(YVal As Double)
    MyY = YVal
    Debug.Print "Y changed to " & YVal
    End Property

    Public Property Get X() As Double
    X = MyX
    End Property

    Public Property Get Y() As Double
    Y = MyY
    End Property

    <img src=/w3timages/blueline.gif width=33% height=2>

    Example of use:

    Sub TestPoint()
    Dim pt As New clsPoint
    With pt
    .X = 4
    .Y = 3
    Debug.Print .X, .Y
    End With
    Set pt = Nothing
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create Events (XP)

    Hello Hans!

    Thanks for your answer. You are right, this is not really what I really need. I was originally thinking about something like this but it did not feed my needs.
    Are there any experts out there? I know, it's an advanced programming technique, but I believe, some of you know.
    Basically, I want to understand the mechanisms behind it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to create Events (XP)

    I have attached a demo with a userform that raises an event in the class module clsPoint.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create Events (XP)

    There are quite a few experts around here, however, They don't understand exactly what you want. From your description, they are guessing at what you want and answering that. Could you give a bit more detailed description of what you are trying to do?
    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to create Events (XP)

    Hans's second example should be what you want: he is the EXPERT. If not, as Legare said, we have experts, but we cannot solve problems with unclear descriptions. Reposting SammyB
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create Events (XP)

    Hello together!

    Thanks for all the replies. I definitely know, that there are a lot of experts out there. And I have a lot of admiration for Hans and his knowledge. At least, he is the one, who had a solution for nearly every question from me. Every hint was a 100% solution for my problems. Sam and Legare, you are right, my question was not very precise. This was because I do not exactly know what can be done with user defined events. I will try to do better next time. My main interest is to understand the basic mechanism to be enabled to think about new concepts. The idea of the mentioned class clsPoints was to give an easy example only. I did not mean to confuse anybody. Also I did not mean to offend somebody here. If I did, I would like to apologize!
    So thanks again for your sugestions. I think, Hans' example will help me to understand how it works. If somebody has some good recources of information in the internet, I would apreciate to get to know about them.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to create Events (XP)

    We weren't offended at all! <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>the way to tell if we are offended is that we stop responding!
    As for good resources, most large Excel programming books have a chapter on creating classes. Books by John Walkenbach or Stephen Bullen are probably your best bet. On-line, there is http://msdn.microsoft.com/library/default.....asp?frame=true. Also, <post:=115,871>post 115,871</post:> is another example of controling events with a class module. If you look at the entire thread, you will see another link for command buttons. The most useful class with events that I have written was a color chooser that pops up pallet of color chips and then returns the selected color. Not written for Excel, tho. Hope this gives you some ideas! You've got me wondering if you could create a class with two members which are cells on a worksheet and then respond in the class to changes in the cells. But, I'm not sure that there would be any use for this class. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create Events (XP)

    Thanks again for your help!

    I have started reading the mentioned recources. I fear, it will take some time to take in the basics of the concepts. Hans, your example is a big help to understand the mechanisms, it brings some light into my darkness ;-)
    Sam, thanks for the links, they will keep me busy for some time. Your idea of a class which reacts to changes in a worksheet is something I am thinking about. I have a class, which describes a vehicle. It contains data like center of gravity, mass of the vehicle, distance between front and rear axle, some engine data (like torque over speed), gearbox ratios and so on. With this information, I do some calculation, e.g. acceleration capability an may others. Now I want the calculation automatically updated, whenever a property of my vehicle object changes. The worksheet may be understood as my user-interface which contains input data (my properties) and output data (like acceleration capability). Now changing an input parameter should update the vehicle property and start the recalculation. Since there are a lot of properties taken into account, I was thinking about using events, because the needed reaction is the same and independant from the changed property. Do you think, this could be realised? I will give more details, if I could not manage to describe my target.

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to create Events (XP)

    > Since there are a lot of properties taken into account, I was thinking about using events, because the needed reaction is the same and independant from the changed property.
    It might be useful to have an object and properties, but it seems like that is what we are usually doing with named ranges and that normal Excel recalculation is all that is needed. However, using classes might be a useful way to think about a problem.

    Changing the subject, you said, "it will take some time to take in the basics of the concepts." Does that mean that the object-oriented concepts are new to you? If so, you probably do not want to learn them with Excel. It fights you rather than helps you. Probably a better way to learn these concepts is to take a beginning programming course at a local college. I'm taking one now that uses C# and it has really helped me to think in terms of objects. It's hard to get 30 years of Fortran out of my brain, although one of these day I'm going to write ooFortran.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create Events (XP)

    Porley

    Depending on your needs, there might be several "levels" at which you may consider implementing an "event-style" behaviour in your class. Hans' method is probably the cleanest and simplest, and may be sufficient for what you need. Going a level deeper (and I'm no expert in VB, having done most OO work in C++) it seems several steps are involved:

    o Declaring Event members within the class:
    Public Event OnChange(ByVal arg As <Type>)

    o Raise the event as appropriate in code (or call a "wrapper" routine) like:
    Private Sub Change(ByVal arg As <Type>)
    RaiseEvent OnChange(arg)
    End Sub

    o Connect the class events to the form, using:
    Public WithEvents Instance As MyClass

    There's a nice example, along these same generic lines, but with better detail, at Declaring and Raising Events in Visual Basic 6

    Alan

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to create Events (XP)

    Thanks, Alan, that is a very good link. But, I think that we would really like to add a level of complexity related to Excel:

    Suppose that we have a car class. I would like to have the properties of the class be ranges in the spreadsheet, so that I could have a constructor that initializes all of the car's properties from a single range. Moreover (and here's where I'm scratching my head <img src=/S/confused.gif border=0 alt=confused width=15 height=20>), when any of the cells that are members of the car change, then the class would do some calculations and raise a change event.

    Ah, writing all this down has helped, the class could create an instance of a userform with textboxes that it links to the cells in the worksheet. Then, the class could catch the textbox's change event and use that to trigger it's change event. Sounds doable. Now, if we just had nothing to do! I'll try to put together a prototype, but I'm out-of-town for the next couple of days.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to create Events (XP)

    Sammy

    As usual I missed the all-important XL/VBA context, and jumped into VB6. Your "thinking out loud" triggered <img src=/S/pun.gif border=0 alt=pun width=22 height=18> another thought though, vaguely related to something I've been doing recently. For your Car class, it might work something like this:

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to create Events (XP)

    > As usual I missed the all-important XL/VBA context
    But, it was a very useful and informative link re classes and events. There is so little material, you have to read it all.

    > Each worksheet Change Event procedure checks (via a class member function) to see if the changed cell(s) overlap any of the Car Name ranges
    Yes, it's going to have to be something like that because I've tried the Userform route and cannot get it to work.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to create Events (XP)

    Here is an example of a box class. Whenever the corner coordinates change, the perimeter & area are updated. I had to do as Alan suggested and use the sheet change event - ugly, but I cannot figure out any other way to make it work. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •