Passing var types, groups and properties to a sub
 

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


-----Original Message-----
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

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote
in message
...
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


.