Simple UDF help

[Salman]

Hi I have the following simple UDF. How can I reference
the column numbers (the column in which they reside on the
worksheet) of the arguments "first" and "last" from inside
the function?

Function CAGR(first As Double, last As Double, n As
Integer)
CAGR = (last / first) ^ (1 / n) - 1
End Function

Thanks.

 

[Tom Ogilvy]

since you are passing first and last as numbers, you can't. If you intend
to pass them is as range references, then you can redefine them as Ranges

Function CAGR(first As Range, last As Range, _
n As Integer)
dim firstColumn as Long, lastColumn as Long
firstColumn = first.Column
lastColumn = Last.Column
CAGR = (last.Value / first.Value) ^ (1 / n) - 1
End Function

Assumes first and last are single cell references.

 

[Chip Pearson]

Salman,

If you want to reference the columns, you need to declare 'first'
and 'last' as Ranges. E.g.,

Function CAGR(first As Range, last As Range, n As Integer)
CAGR = (last / first) ^ (1 / n) - 1
Debug.Print first.Column, last.Column
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com