# Creating Function Help

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
End Function

If possible I want to return a percentage like 46.75%
###### Who is Participating?

Commented:
Something like this?

Option Explicit

Public Function IRRCalc(ByRef Values() As Double, ByRef Guess As Double) As String
IRRCalc = Format(IRR(Values(), Guess) * 100, "#0.00") & "%"
End Function

Private Sub Command1_Click()
Dim Guess As Double
Static Values(3) As Double

Guess = 0.1  ' Guess starts at 10 percent.

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

MsgBox IRRCalc(Values(), Guess)  ' Calculate internal rate.

End Sub
0

Commented:
Here you go! it works. i've tested it

Option Explicit

Dim gGuess, RetRate, mValues(4) As Double
Dim Fmt, Msg As String
gGuess = 0.1  ' Guess starts at 10 percent.
Fmt = "#0.00"   ' Define percentage format.
mValues(0) = -2465399   ' Business start-up costs.
' Positive cash flows reflecting income for four successive years.
mValues(1) = 1725956: mValues(2) = 1686970
mValues(3) = 1599299
RetRate = IRRCalc(mValues, gGuess) ' 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.
End Sub

Public Function IRRCalc(ByRef Values() As Double, ByVal Guess As Double) As Double
IRRCalc = IRR(Values, Guess) * 100
End Function
0

Author Commented:
In my ASP application I am trying to run this function but I am getting subscript errors with my array and stuff:  Here is the ASP code:

<%
Dim objCalcVal, RetVal
Dim arrIRR(3),valGuess
arrIRR(0) = CDBL(-2465399)
arrIRR(1) = CDBL(1725956)
arrIRR(2) = CDBL(1686970)
arrIRR(3) = CDBL(1599299)
valGuess = 0.1
Set objCalcVal = Server.CreateObject("FinancialCalcs.clsFinancialCalcs")
RetVal = objCalcVal.IRRCalc(arrIRR(),valGuess)
Response.Write RetVal
%>
0

Author Commented:
I keep getting type mismath  Type mismatch: 'IRRCalc'.  Here is what I have:

VB DLL:
Public Function IRRCalc(ByRef Values() As Double, ByRef Guess As Double) As String
IRRCalc = Format(IRR(Values(), Guess) * 100, "#0.00") & "%"
End Function

ASP Page:
<%@ Language=VBScript %>
<HTML>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<BODY>

<%
Dim objCalcVal, RetVal
Dim arrIRR(3),valGuess
arrIRR(0) = CDBL(-2465399)
arrIRR(1) = CDBL(1725956)
arrIRR(2) = CDBL(1686970)
arrIRR(3) = CDBL(1599299)
valGuess = CDBL(0.1)
Set objCalcVal = Server.CreateObject("FinancialCalcs.clsFinancialCalcs")
RetVal = objCalcVal.IRRCalc(arrIRR,valGuess)
Response.Write RetVl
%>

</BODY>
</HTML>
0

Commented:
Perhaps you should post a pointer in ASP on this one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.