Passing var types, groups and properties to a sub

[ExcelMonkey]

I have a routine which checks cells and comments in a
spreadsheet. I have written the routine using two
separate For Each loops. However I would like to optimize
the code so that only one loop exists and it always knows
whether what element (type of variable) it is ( in this
case a comment("cmt") or a range ("Range")). I may also
have to pass the both the group (i.e. sh )and the group
property to a variable as well (i.e. .UsedRange
and .Comments)

I am guessing that I can either make the variables cmt or
cell Public so that I do not have to pass them to the
routine. Or I can pass them with some additional
information telling the sub what type of variable they
are. I am assuming I can use the TypeName function to
return the variable type. But how do you pass this to a
sub when calling it. Don't know how to pass group or
properties of group to a sub.

See original code below with incomplete suggestion for new
code

Original Code

Dim cmt As Comment
Dim cell As Range

For Each cell In sh.UsedRange

Next

For Each cmt In sh.Comments

Next
End Sub

New code
Dim cmt As Comment
Dim cell As Range


Call ForEachLoop (??????)


End Sub

 

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

If you have a cell with comments, this is part of the UsedRange. So you only
need to pass the usedrange to the routine, and have it test the comment and
the value.

You could also just get away passing the sheet object to the routine, and
test the usedrange within that.

Sub newRoutine(sh as Worksheet)
Dim cmt As Comment
Dim cell As Range

For Each cell In sh.UsedRange
On Error Resume Next
Set cmt = cell.Comment
On Error GoTo 0
If Not cmt Is Nothing Then
'etc.
End If
Next cell
Next
End Sub
--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub EFGH()
Dim cmt As Comments
Dim cell As Range
Call ForEachLoop(ActiveSheet.UsedRange)
Call ForEachLoop(ActiveSheet.Comments)
End Sub


Sub ForEachLoop(coll As Object)
Dim itm As Object
For Each itm In coll
If TypeName(itm) = "Range" Then
' special code for range
ElseIf TypeName(itm) = "Comment" Then
' special code for comment
End If
Next
End Sub

 

[ExcelMonkey]

Tom, I am considering calling this For Each loop several
times(7-10 times). The order in which I call it will vary
as well. That is, it may contain cells or comments but in
varying orders. Is it possible to write this code with
only one call statement instead of the two you have
suggested.

Thanks