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%
sbornstein2Asked:
Who is Participating?
 
vinnyd79Connect With a Mentor 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
 
wraith821Connect With a Mentor Commented:
Here you go! it works. i've tested it

Option Explicit

Private Sub Form_Load()
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
 
sbornstein2Author 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
 
sbornstein2Author 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>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<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
 
TOC-FriedCommented:
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.

All Courses

From novice to tech pro — start learning today.