UDF Function

[Mouimet]

Hi,
I have many different long formulas and I need to name each of them
This way I will be able to say: Choose(A1, ATV,AVG,Unit)
If cell A1 = 2 the cell will use the function AVG and show the results
My problem is when I create the functions I get an #Value in cell

Example :
Lets say AVG is one of my Function
In VB I have:

Function AVG()
ActiveCell.FormulaR1C1 = "=AVERAGE (RC[-3]:RC[-1])"
End Function


In Excel I have
In Cell A1 = 2

Cols----> b C D E
Row2--> 100 50 100 =Choose(A1,ATV, AVG,Unit)

Because the formula in cell E2 look for cell A1 the formula use the function
AVG
The results should show the average of B,C,D = 83

Right now the results = #Value
Why and how can I create these function?
Thanks and Happy New Year everyone

 

[Per Jessen]

Hi

First your UDF do not work, so I reworte it, second to use the UDF in your
worksheet, use AVG(), like:

Choose(A1,ATV(),AVT,Unit)

Function AVG()
Application.Volatile
Dim rng As Range
Set a = Application.Caller
Set rng = Range(a.Offset(0, -3), a.Offset(0, -1))
AVG = WorksheetFunction.Average(rng)
End Function

Regards,
Per