Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts

    Create an Object from data

    Good Afternoon Loungers,

    I would like to create an object that monitors patient data:

    First Name
    Last Name
    Account Number
    Med Rec Number
    DOB (date of birth)
    admission date
    Discharge date
    Length of Stay (calculation)

    The data will be sent to various work sheets. Can anyone point me in the right direction?

    Thank you in advance,
    Alex

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Alex,

    It sounds as though you are describing a class. A class is merely a blueprint of the object you wish to create. Once you have the class created, you can instantiate as many copies as you like of the object. Although some of the code can be similar to a standard module, the structure of a class module is quite different. Once the Class has been created, however, the use of the object created from the class is pretty straight forward in a standard module. The object will have properties and methods like objects you are familiar with such as a worksheet, Range, or form.

    I will take a class that I had written for employees and modify it to contain the properties you list above.

    Maud

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts

    Class module

    Alex,

    Below is a class module that I modified to contain the properties that you have described in your post. All the properties are read and write except for the LengthOfStay property which is read only because it is a calculation. You create a class module the same way you would add a standard module: In the Vb Editor, click Insert> Class Module. Rename the class module to ClsPatient in the properties window. Paste the code below into the module. Your blueprint is complete.

    Code:
    Private pnamefirst As String
    Private pnamelast As String
    Private paccountnum As String
    Private pmedrecnum As String
    Private psex As String
    Private pdob As Date
    Private padmdate As Date
    Private pdschdate As Date
    
    'FIRST NAME PROPERTY
    Public Property Get NameFirst() As String
        NameFirst = pnamefirst
    End Property
    Public Property Let NameFirst(fname As String)
        pnamefirst = fname
    End Property
    
    'LAST NAME PROPERTY
    Public Property Get NameLast() As String
        NameLast = pnamelast
    End Property
    Public Property Let NameLast(lname As String)
        pnamelast = lname
    End Property
    
    'ACCOUNT NUMBER PROPERTY
    Public Property Get AccountNum() As String
        AccountNum = paccountnum
    End Property
    Public Property Let AccountNum(account As String)
        paccountnum = account
    End Property
    
    'MEDICAL RECORD NUMBER PROPERTY
    Public Property Get MedRecNum() As String
        MedRecNum = pmedrecnum
    End Property
    Public Property Let MedRecNum(medrec As String)
        pmedrecnum = medrec
    End Property
    
    'SEX PROPERTY
    Public Property Get Sex() As String
        Sex = psex
    End Property
    Public Property Let Sex(malefemale As String)
        psex = malefemale
    End Property
    
    'DATE OF BIRTH PROPERTY
    Public Property Get DOB() As Date
        DOB = pdob
    End Property
    Public Property Let DOB(birthdate As Date)
        pdob = birthdate
    End Property
    
    'ADMISSION DATE PROPERTY
    Public Property Get AdmDate() As Date
        AdmDate = padmdate
    End Property
    Public Property Let AdmDate(admission As Date)
        padmdate = CDate(admission)
    End Property
    
    'DISCHARGE DATE PROPERTY
    Public Property Get DschDate() As Date
        DschDate = DschDate
    End Property
    Public Property Let DschDate(discharge As Date)
        pdschdate = CDate(discharge)
    End Property
    
    'LENGTH OF STAY PROPERTY (READ ONLY)
    Public Property Get LengthOfStay() As Integer
        LengthOfStay = DateDiff("d", padmdate, pdschdate)
    End Property
    From a standard module, the properties will be available for the object in dot notation (see the code below). To create the copy (or instantiate the object), you must declare and set the object variable as you would with creating the instance of a worksheet. There is one difference and that is that you must use the "New" keyword (line 2 of the code). Once the object is created, you can read or write to the properties as you would any other object.

    Class1.png

    This following code will add the data to the patient object and return the length of stay. You can create as many instances of this object as you want, one for each patient, using a different object variable name, and add them to a collection (ex. patient list). You can also write Private subroutines in the class to function as methods for the object and accessible through the dot notation like the properties.

    Code:
    Public Sub PatientInfo()
    Dim patient As ClsPatient
    Set patient = New ClsPatient
    patient.NameFirst = "John"
    patient.NameLast = "Robinson"
    patient.AccountNum = "3001123781"
    patient.MedRecNum = "L3467499"
    patient.DOB = "2/22/1964"
    patient.AdmDate = "12/17/2013"
    patient.DschDate = "12/30/2013"
    MsgBox patient.LengthOfStay
    End Sub
    Notice how it looks very similar to a database record. Although setting up a class can be perplexing, it isolates the code into a container while using just the container in code is quite simple.

    Hope I touched on what you are trying to do.
    Maud

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2014-01-01)

  5. #4
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Maudibe,

    OMG, that is what I was talking about! This has condensed my code greatly. This was a suggestion from a co-worker and he was correct. I have adapted the class module into our daily worksheet to produce the header for the patient's documents. I want to be able to cycle through a list of patients from another workbook and add another routine to calculate age. Is it possible to add a print method to the class to print the documents?

    Thank you for such a clear explantion on how to do this.

    Alexandra

  6. #5
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    I am having trouble calculating the age. The following formula returns 18211. Where am I going wrong?

    Code:
    Public Sub Test()
    ptdob = "2/22/1964"
    Age = Date - CDate(ptdob)
    End Sub
    A.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alouso,

    Try this:
    DateCalc.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Alouso (2014-01-01)

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi RG

    What if this is a maternity hospital, and the 'patient' was born two weeks ago?

    zeddy

  10. The Following User Says Thank You to zeddy For This Useful Post:

    Alouso (2014-01-01)

  11. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Alexandra,

    Using the example class I previously uploaded, here is a second formula that will calculate age. I have placed this in a Property Get routine that can be added to the class module. You can adapt RG's formula in your class module as well. My formula will calculate age using the years (result 50) whereas RG's formula takes into consideration the days to complete the year (result 49). Your choice.

    My formula (placed in class module):
    Code:
    'AGE PROPERTY (READ ONLY)
    Public Property Get Age() As Integer
        Age = DateDiff("yyyy", pdob, Date)
    End Property
    RG's formula (placed in class module):
    Code:
    'AGE PROPERTY (READ ONLY)
    Public Property Get Age() As Integer
        Age = Format(Now() - CDate(pdob), "yy")
    End Property
    A class method to print the sheet would be something like this (not tested). You can add additional code if you would like it to do something else or use it as a template to write additional methods.

    (Placed in class module)
    Code:
    'PRINT DOCUMENTS
    Public Sub PrintDoc()
         Activeworksheet.PrintOut
    End Sub
    To create the header, you do not need to place the code to do so within your code. This can also be a method in the class. Here is a sample I put together that will format the worksheet then build the header. Place the code in your class module then to create a header, your code would then need one line: patient.Header

    (placed in class module)
    Code:
    'BUILD HEADER
    Public Sub Header()
        'FORMAT COLUMN
        With Range("A1:A5")
            .Font.Bold = True
            .HorizontalAlignment = xlRight
        End With
        With Range("E1:E5")
            .Font.Bold = True
            .HorizontalAlignment = xlRight
        End With
        With Range("B1:B5")
            .Font.Bold = False
            .HorizontalAlignment = xlLeft
        End With
        With Range("F1:F5")
            .Font.Bold = False
            .HorizontalAlignment = xlLeft
        End With
        'COLUMN 1
         Cells(1, 1) = "Patient Name:"
         Cells(1, 2) = pnamelast & ", " & pnamefirst
         Cells(2, 1) = "Date of Birth:"
         Cells(2, 2) = pdob
         Cells(3, 1) = "Age:"
         Cells(3, 2) = Age
         Cells(4, 1) = "Sex:"
         Cells(4, 2) = psex
         'COLUMN 2
         Cells(1, 5) = "Account #:"
         Cells(1, 6) = paccountnum
         Cells(2, 5) = "Medical Record #:"
         Cells(2, 6) = pmedrecnum
         Cells(3, 5) = "Admission Date:"
         Cells(3, 6) = padmdate
         Cells(4, 5) = "Discharge Date:"
         Cells(4, 6) = pdschdate
         Cells(5, 5) = "LOS:"
         Cells(5, 6) = LengthOfStay
         'DRAW HEADER LINE
        With Range("A5:F5").Borders(xlEdgeBottom)
            .LineStyle = xlDouble
        End With
    End Sub
    The code you would integrate into your module for the complete instance of the patient would be:

    (placed in standard module)
    Code:
    Public Sub PatientInfo()
    Dim patient As ClsPatient
    Set patient = New ClsPatient
    patient.NameFirst = "John"
    patient.NameLast = "Robinson"
    patient.AccountNum = "3001123781"
    patient.MedRecNum = "L3467499"
    patient.DOB = "2/22/1964"
    patient.Sex = "M"
    patient.AdmDate = "12/17/2013"
    patient.DschDate = "12/30/2013"
    patient.Header  'CREATE HEADER
    patient.PrintDoc  'PRINT SHEET
    End Sub
    Header.png

    HTH
    Maud
    Last edited by Maudibe; 2014-01-01 at 11:48.

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2014-01-01)

  13. #9
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Retired Geek,

    Thanks for the formula. It works as you posted. I will try it by adapting it into the class module as Maudibe describes.

    Maudibe,

    I will try your formula as well. Thanks again for showing a sample print routine. I am amazed that the header routine can be added as a method as well. I was writing the code to build the header but I am understanding more what you meant by "isolating the code". It is all coming together for me. I can see the potential here. My routine has shrunk from ~90 lines to around 25 and is so easy to follow now, accepting the fact that I do not need to know what goes on in the class modle; just that it works. I guess it is like not questioning what goes on behind the scenes in the code when I write, Range("A1:C1").select. Will apply when back at my desk.

    Zeddy,

    You have a great point with an age less then 1 year but fortunately, I am dealing with adults greater than or equal to 18 (16 if emancipated).

    Thanks all for your help
    Alexandra

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by zeddy View Post
    Hi RG

    What if this is a maternity hospital, and the 'patient' was born two weeks ago?

    zeddy
    Seems to work there also.
    DateCalc.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi RG

    OK, I'll accept an age of 00 years.
    But usually, the 'age' of a person is given
    ..in days, (up to about 2 weeks)
    ..in weeks (up to about 3 months)
    ..in months (up to about 2 years)
    ..in years (for most of us)
    ..in centuries (for surviving Highlanders)

    ..and anyway, your age format would give my Great Great Gran who is 104 yrs old an age of 04
    Now, it just so happens that treatment for very young patients (under 10yrs old) can be similar to those over 100 yrs old.
    But I wouldn't want to be in that Hospital.

    zeddy

  16. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Right you are! My original post was more about the formatting than the formula as that was the OP question. If I was actually doing the calcs there would be tests to check for the edge conditions as you suggest. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Following Zeddy's parameters, this code will calculate the days, weeks, months, years bracket for any age

    DOB.png

    Code:
    Public Sub CalcAge()
    For I = 2 To 10
    ptdob = Cells(I, 1)
    AgeYears = WorksheetFunction.RoundDown(DateDiff("d", ptdob, Date) / 365, 0)
    AgeDays = DateDiff("d", ptdob, Date)
    Select Case AgeYears
        Case Is < 2
            Select Case AgeDays
                Case Is < 14
                    Age = DateDiff("d", ptdob, Date) & " days"
                Case Is < 90
                    Age = WorksheetFunction.RoundDown(DateDiff("d", ptdob, Date) / 7, 0) & " weeks"
                Case Else
                    Age = DateDiff("m", ptdob, Date) & " months"
                End Select
        Case Else
            Age = DateDiff("yyyy", ptdob, Date) & " years"
        End Select
        Cells(I, 2) = Age
    Next I
    End Sub
    Last edited by Maudibe; 2014-01-02 at 07:22.

  18. #14
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Maud,

    Following your structure, I wrote a property for the patient's address and it works with the code line patient.Address = "125 NY Street". What do you think?

    Code:
    'Address Property
    Public Property Get Address() As String
        Address = paddress
    End Property
    Public Property Let Address(qaddress As Date)
        paddress = Worksheets("Face Sheet").Cells(4, 1).Value
    End Property
    Alexandra

  19. The Following User Says Thank You to Alouso For This Useful Post:

    Maudibe (2014-01-04)

  20. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Alexandra,

    Proud of ya!! You are 97% of the way there. Let me offer a suggestion in the form of a question. What would happen if the address is no longer in the active sheet cell A4? Your code would not perform as expected. It is great that it runs that way but let's tweak it a bit. The Let Address Property lets a coder input an address by the way of patient.Address= "something" and passes the value to qaddress in the Property Let Address routine (which btw needs to be as String). It was there but you didn't use it. There is no need to place the actual cell address in the code when qaddress already holds the value. So you can set paddress to equal the passed value held in qaddress. Here would be the code:

    Code:
    'ADDRESS PROPERTY
    Public Property Get Address() As String
        Address = paddress
    End Property
    Public Property Let Address(qaddress As String)
        paddress = qaddress
    End Property
    In your code within a standard module you can now assign the value (Let) by:
    A string: patient.Address = "125 NY Street"
    A variable: patient.Address = street where street is a variable holding a string value
    A cell reference: patient.Address = Cells(4,1).value
    InputBox value: patient.Address = InputBox("Enter the address")

    Try creating other Let and Get properties for City, State, Zip, Phone, Cell, email, Height and weight, etc. Remember Let Property lets the coder assign a value where the Get Property allows the coder to retrieve the value. If you want Read only, then create a Get without the Let

    HTH,
    Maud

  21. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2014-01-03)

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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