help with VB function please

Posted by Bob Findlay on June 04, 2001 1:39 AM

Hi
I have the following code

Public Function bobmode(ByVal ref As Range, Optional ref2 As Variant = Null, Optional ref3 As Variant = Null)
For Each cell In ref
do something
Next

this works fine if I pass a normal cell reference, but if I pass a 3-D reference, it doesn't work.

any idea why a 3-d ref should be different?
how can I code it to cope with a 3-d reference?

thanks

Posted by David Rainey on June 04, 2001 10:19 AM

I don't exactly know/like what you are doing here.
using the , optional as rage was the solution for the 3d reference

you just put a comma between each 2d reference
=bobmode(Sheet1!H4:H12,Sheet2!H4:H12,Sheet3!H4:H12)

Posted by Bob Findlay on June 05, 2001 7:56 AM

yes, but that's not the solution for a 3-d reference, it's a workaround with multiple 2-d references ;-)

that's fine if you only have 2 or 3 - I have >20 - that a rather cumbersome and inflexible way of doing it. I just can't see why the 3-d reference doesn't work. As far as I can see, it should!