Vba formula to support range input

[rossking]

First I am not sure what it is called when you do below, so hard to search and see if there is a soultion.

What I am referring to is:


If you take a Raneg of cells and give them a name (Named Range)

ie THISAREA and it refers to A1:A20


then in B2:B20 you paste the same formula down

=If(THISAREA=1,"YES","NO")

Excel is smart enought to know this is a range of 20 cells, but I am only referring to the row that the formula is on.

So above on row 5 would evaluate only A5


How do i add that same logic to a VBA Function, Today if I apss same Range to function it wil fail as it gets the whole range and doesnt know what line to pull from



Function MyFunc(Data as variant) as variant

If Data = 1 then
MyFunc = "YES"
else
MyFunc = "NO"
end if

end Function

So i would do same as above but paste

=MyFunc(THISAREA)

and it will only work if THISAREA is a 1 cell range

 

[Norie]

It really depends on what MyFunc does how you handle things if a range is passed to it.

For example, if you wanted to check if 'YES' appears in the range you could use a loop.