?
Solved

Creating Function Help

Posted on 2005-05-14
5
Medium Priority
?
242 Views
Last Modified: 2008-03-10
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
Comment
Question by:sbornstein2
5 Comments
 
LVL 28

Accepted Solution

by:
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

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

Author Comment

by:sbornstein2
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

by:sbornstein2
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>
<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
 
LVL 3

Expert Comment

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question