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("B2:D25")
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

Hi Matt,

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

Module:

Code:

---

Sub testSub()
    Dim objRG As New Class1
   
    Set objRG.MyRange = Range("B2:D25")
    MsgBox objRG.TheLastRow

    Set objRG = Nothing
   
End Sub

---

Class:

Code:

---

'
' 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.

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("B2:D25").TheLastRow[/VBA]

Thanks again
Matt

Hi Matt,

I don't think so, I could be wrong ;)

Also I think it could get mighty confusing.

Cheers
Andy

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

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

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!

Hi Guys,

I think that there's some really valuable info in here that would look really good in a KB entry! ;)