Solved

Creating Function Help

Posted on 2005-05-14
Medium Priority
242 Views
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%
0
Question by:sbornstein2

LVL 28

Accepted Solution

vinnyd79 earned 120 total points
ID: 14003636
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

LVL 8

Assisted Solution

wraith821 earned 80 total points
ID: 14003646
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 Comment

ID: 14003683
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 Comment

ID: 14003730
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

LVL 3

Expert Comment

ID: 14003950
Perhaps you should post a pointer in ASP on this one.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 12 hours left to enroll