Macro that acts like function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Rohrer - 19 Dec 2006 14:19 GMT

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

Reply to this Message

Bob Phillips - 19 Dec 2006 15:24 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Can anyone provide me with an example of a macro that acts like a
> function. It will be formatted like this.
[quoted text clipped - 7 lines]
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

Reply to this Message

James - 21 Dec 2006 21:56 GMT

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

Reply to this Message

James - 21 Dec 2006 22:39 GMT

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

Reply to this Message