PDA

View Full Version : Solved: Custom Method?



mvidas
12-08-2004, 12:12 PM
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:

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

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

LRow = RG.LastRow

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

Andy Pope
12-08-2004, 02:30 PM
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.

mvidas
12-09-2004, 07:13 AM
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 Range("B2:D25").TheLastRow

Thanks again
Matt

Andy Pope
12-09-2004, 08:23 AM
Hi Matt,

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

Also I think it could get mighty confusing.

Cheers
Andy

mvidas
12-09-2004, 08:26 AM
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

mdmackillop
12-09-2004, 10:32 AM
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

mvidas
12-09-2004, 11:45 AM
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!

Ken Puls
12-09-2004, 11:40 PM
Hi Guys,

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