Consulting

Results 1 to 8 of 8

Thread: Solved: Custom Method?

  1. #1
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location

    Solved: Custom Method?

    Hi Everyone,

    I'm looking for information or links on how I could create my own method. I'm not referring to a UDF, but an actual method (or property I suppose). As a very simple example, I could write the following in excel vba:

    [vba]Sub testSub()
    Dim RG As Range, LRow As Long
    Set RG = Range("B225")
    LRow = TheLastRow(RG)
    Msgbox LRow
    End Sub

    Public Function TheLastRow(Rng As Range) As Long
    TheLastRow = Rng.Row + Rng.Rows.Count - 1
    End Function[/vba]

    But I was wondering how I could create a .LastRow method, so I could use:

    [vba]LRow = RG.LastRow[/vba]

    Note that I'm not really after a .LastRow method, but merely just using that as a very simple example. I'm assuming I'll need a normal VB program for this rather than VBA, and I wouldn't mind that as I'd like to pick up a copy anyways. Any help, or pointing me in the right direction, would be greatly appreciated!

    Thanks
    Matt

  2. #2
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    344
    Location
    Hi Matt,

    No VBA can handle it. You just need to create a class module.

    Module:
    Sub testSub()
        Dim objRG As New Class1
        
        Set objRG.MyRange = Range("B2:D25")
        MsgBox objRG.TheLastRow
    
        Set objRG = Nothing
        
    End Sub
    Class:
    '
    ' Class1
    '
    Public MyRange As Range
    Public Function TheLastRow() As Long
        TheLastRow = Me.MyRange.Row + Me.MyRange.Rows.Count - 1
    End Function
    Obviously this just demonstrates your very simple example but hopefully this will get you started.
    Cheers
    Andy

  3. #3
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Andy, that is neat! I've only dabbled into class modules in the past, and this is going to give me a lot of new things to play with and learn about, so thanks!

    Is there any way I can incorporate this into the range object? So that I could do something similar to [VBA]Range("B225").TheLastRow[/VBA]

    Thanks again
    Matt

  4. #4
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    344
    Location
    Hi Matt,

    I don't think so, I could be wrong

    Also I think it could get mighty confusing.

    Cheers
    Andy
    Cheers
    Andy

  5. #5
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    I didn't think so, but I figured it couldn't hurt to ask This is all fun to play with, and extremely informative.
    Thanks again, it was exactly what I needed to see!
    Matt

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Matt,
    I got this file from a chap in EE, as I was trying to understand a little of Class Modules as well. Its designed to automate a SaveAs routine
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Neat! The only other time I played extensively with class modules was when I was answering a Q on EE that asked how they could create a 'global' workbook_open sub. That took a bit of trial and error, and much searching, but I was able to figure it out (thanks to a newworkbook sub Brad wrote). This is neat though, gave me some more ideas on how to play with these CMs
    Thanks!

  8. #8
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Guys,

    I think that there's some really valuable info in here that would look really good in a KB entry!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

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