Solved: CAGR formula using only range as input

[greglittle]

Can someone please check this formula to see why I keep getting a #VALUE error when I use it in the sheet? Thanks!

[VBA]
Function CAGR(rng As Range) As Double
Dim firstCell As Range
Dim lastCell As Range
Dim firstVal As Double
Dim lastVal As Double

firstCell = rng.Cells(1, 1)
lastCell = rng.Cells(1, rng.Count)
firstVal = Range(firstCell).Value
lastVal = Range(lastCell).Value

Debug.Print firstCell, lastCell, firstVal, lastVal

CAGR = (lastVal / firstVal) ^ (1 / rng.Count) - 1

End Function[/VBA]


Also, does anyone know how to use Debug.Print to see what the values of the variables are for a function (like this) that is used as a formula in the sheet? I've never had the need to see variable values in this case (and it didn't work!).

[CatDaddy]

put a breakpoint in the program before the line "CAGR = ..." then when you hover over the variables with the mouse it will tell you their values

[Paul_Hossler]

I think that since firstCell and lastCell are already Ranges, you don't want to use Range(firstCell)

Probably something like this

[VBA]
firstCell = rng.Cells(1, 1)
lastCell = rng.Cells(1, rng.Count)
firstVal = firstCell.Value
lastVal = lastCell.Value

[/VBA]


Paul

[greglittle]

@CatDaddy, I inserted a breakpoint, but none of the variables have values. Since firstVal and lastVal depend on firstCell and lastCell parsing the range info correctly, there must be something wrong with my use of ".Cells(x,y)" in the following part:

[VBA]
firstCell = rng.Cells(1, 1)
lastCell = rng.Cells(1, rng.Count)[/VBA]

Any thoughts?


@Paul, good point, but that still didn't resolve the error. Any other ideas?

Thanks!

[Paul_Hossler]

Any other ideas?

Might be the way you're calling it then

Can you post a small WB showing the problem?

Paul

[greglittle]

Here you go.

[abdulla]

Try it now

[VBA]Function CAGR(rng As Range) As Double
Dim firstval As Double
Dim lastval As Double
firstval = rng.Cells(1, 1).Value
lastval = rng.Cells(1, rng.Columns.Count).Value
CAGR = (lastval / firstval) ^ (1 / rng.Columns.Count) - 1
End Function[/VBA]

[greglittle]

Thanks! Based on your solution, I'm not sure why mine didn't work (especially since firstCell and lastCell are ranges). But, even so, your code is more efficient anyway. Take care!