Macro that acts like function

[James Rohrer]

Can anyone provide me with an example of a macro that acts like a function. It will be formatted like this.

Function(Array_1,Array_2,Array_3,Data_Array_1,Data_Array_2,Data_Array_3)

Outputs True/False

Thanks

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Bob Phillips]

Function myFunc(ParamArray rng())
Dim i As Long
Dim cell As Range

myFunc = False
For i = LBound(rng) To UBound(rng)
For Each cell In rng(i)
If cell.Value > 10 Then
myFunc = True
Exit Function
End If
Next cell
Next i

End Function


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[James]

I tried this and the following is the result. Am I doing anything wrong? The input data range was names MyRange. The function which was Ctrl-Shift-Entered into the output range was =myFunct(MyRange)

17 1 4 7
15 16 16 5
1 18 16 18
4 14 5 6
4 8 12 14
11 4 1 2
14 18 6 13
16 18 8 9


TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[James]

I changed the function to =myFunc(a1) and copied and pasted into the other cells and got the expected result. I tried to change the coding to get what I wanted like this.
Function myFunc(ParamArray rng()) As Range
Dim i As Long
Dim cell As Range
Dim tmp_rng As Range

For i = LBound(rng) To UBound(rng)
For Each cell In rng(i)
If cell.Value > 10 Then
tmp_rng(i) = True
ElseIf cell.Value <= 10 Then
tmp_rng(i) = False
End If
Next cell
Next i
myFunc = tmp_rng
End Function

and got this result where selected the output cellse and Ctl-Shif-Entered the following function =myFunc(MyRange) and got the following.

17 1 4 7
15 16 16 5
1 18 16 18
4 14 5 6
4 8 12 14
11 4 1 2
14 18 6 13
16 18 8 9


#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!

Am I trying to do somehting that can't be done?

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com