Creating Function Help
Posted on 2005-05-14
I need to create this into a function where I am going to pass in an array and guess percentage. This code is an example I want to return a percentage using the VB IRR function.
Dim Guess, RetRate, Values(4) As Double
Dim Fmt, Msg As String
Guess = 0.1 ' Guess starts at 10 percent.
Fmt = "#0.00" ' Define percentage format.
Values(0) = -2465399 ' Business start-up costs.
' Positive cash flows reflecting income for four successive years.
Values(1) = 1725956: Values(2) = 1686970
Values(3) = 1599299
RetRate = IRR(Values, Guess) * 100 ' Calculate internal rate.
Msg = "The internal rate of return for these four cash flows is "
Msg = Msg & Format(RetRate, CStr(Fmt)) & " percent."
MsgBox (Msg) ' Display internal return rate.
So I really just need to pass in an array of values for Values and a Guess to the IRR function. I keep getting array expected error in VB when trying to compile. This is what I have but it does not compile and its looking for an array for Values.
Public Function IRRCalc(ByRef Values As Variant, ByRef Guess As Double) As Double
IRRCalc = IRR(Values, Guess) * 100
If possible I want to return a percentage like 46.75%