XIRR in Access HELP A NOOB PLOX!! 2

[SoLost1]

I am brand new to Access and have only basic VBA experience coding macros in Excel, so apologies if I am being obtuse, but I am really struggling trying to get this(

https://www.tek-tips.com/viewthread.cfm?qid=1789559)

to work. Can I paste the code exactly as written for the two functions and it should work? The first part seems to be calling a GetGoodGuess() function that isn't defined anywhere.

For the second part can I use exactly as is or do I need to add code to reference the library? How do I go about doing that?

Public Function XIRR_Wrapper(Payments() As Currency, Dates() As String, Optional GuessRate As Double = 0.9)
'Must add a reference to the Xcel library. Use Tools, References, Microsoft Excel XX.X Object Library
XIRR_Wrapper = Excel.WorksheetFunction.Xirr(Payments, Dates, GuessRate)
End Function

I tried to figure something out via Google searches, but there doesn't seem to be much that works on this topic. Could you kindly explain like you would to a noob idiot, because I am a noob idiot?

 

[Andrzejek]

How to Use Tools, References, Microsoft Excel XX.X Object Library

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SoLost1]

Andy,
Thank you for the prompt and helpful response, although I might need some time to digest because I am slow on the uptake, but if I use the early binding and activate the reference checkbox is that sufficient or do I need to add code to create an Xcel object?

Any thoughts on the GetGoodGuess function? Should I be able to copy and paste the two sections exactly as written and have it work or are there other areas where I need to do a deeper dive/write code? Thanks!!!

 

[SoLost1]

I still have not been able to get it to work. Can anyone please help this poor moron?

 

[combo]

In first section you decide if you use excel library or MajP's implementation, by commenting/uncommenting the two last bolded lines.
In the first case you need a reference to Axcel and code from the second section and some functions from the third one. In the second case only the code in third section is necessary.

combo

 

[SoLost1]

Combo,
Thanks for the response. If you are using the excel library you need parts of the third section? I thought the third section is only MajP's proprietary code. Any thought on the GetGoodGuess() function part? Thanks.

 

[combo]

AccessXIRR calls GetGoodGuess function, in GetGoodGuess there are calls to NetPresentValue and HasSignChange, all three (if I found all calls) functions are in the third code section and you need them.

combo

 

[SoLost1]

Ahhhh I see. Thank you for the prompt response and patience with me, combo; as you can see I am a total noob when it comes to code. Now that you have pointed it out I am like "unagi" and I can see the forest that I couldn't see for all the trees in the way. I will give it a go, I imagine it will work now given it has clearly worked for so many, but I can't underestimate my ability to trip over my own feet, so I will revert back if I need further spoon feeding. Thanks so much again.

 

[SoLost1]

When I try to use the XIRR_Wrapper() option I get a "Compile error: Type mismatch: array or user-defined type expected" on the "Dates" parameter of the XIRR_Wrapper function? Any thoughts?

I tried testing the MyXIRR() option because I am a super smart next level international hacker/coder, and I was able to get that to produce values on a simple test data I threw together, but if at all possible I'd like to get the Excel XIRR_Wrapper option to work, because I know both use the Newton Raphson method, but I know there are unexpected divergences because Excel is blackbox about their exact algorithm, so there is no way MajorP could code it to converge on the same root(or converge/not converge) possibility as the Excel 100% of the time without calling the Excel function.

Combo or anyone, halp me please! In all seriousness thanks so much for the help so far, I imagine to the observer it is like watching a dog repeatedly run into a glass door and then you just unlatch it for them. Facepalm.

 

[Andrzejek]

If that's the code you are referring to:

Public Function XIRR_Wrapper(Payments() As Currency, [red]Dates[highlight #FCE94F]()[/highlight] As String[/red], Optional GuessRate As Double = 0.9)[green]
   'Must add a reference to the Xcel library. Use Tools, References, Microsoft Excel XX.X Object Library[/green]
   XIRR_Wrapper = Excel.WorksheetFunction.Xirr(Payments, Dates, GuessRate)
End Function

This Function expects an Array of Dates()
What do you pass?

I am a super smart next level international hacker

That may get you banned from this site...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SoLost1]

Well clearly that was facetious sarcasm because I couldn't hack my way out of a cardboard box, even with all the kings horses and all the kings men spoon feeding me help it seems.

Passed it a Access column name per MajorP's instructions in another thread, so it should recognize as an array. I didn't get the error msg calling the function; this was just in pasting the code into VBA module. And I was able to get MajorP's MyXIRR() to work, which I imagine also expects an array, so not sure what the issue is.

 

[SoLost1]

It's interesting the MajorP's XIRR_Wrapper() calling Excel has Dates defined as String,

Public Function XIRR_Wrapper(Payments() As Currency, Dates() As String, Optional GuessRate As Double = 0.9)

but MajorP's homebaked has Dates() as Date

Public Function MyXIRR(Payments() As Currency, Dates() As Date, Optional GuessRate As Double = 0.1) As Variant

Could that be the reason the error is being thrown? I feel like a lot of people were using it as is, so I'd be very surprised if it were wrong.

Also, I just figured out how to use the forum code button, because I'm super smart like that.

 

[combo]

Excel VBA XIRR
You may experiment with types, maybe date serial numbers are accepted.

combo

 

[SoLost1]

The Access column type is Date/Time on Access 365, can I define beyond that? Maybe I will try changing the XIRR_Wrapper() to Dates() AS Date and see if that resolves.

Is there any way to msg specific users on this forum? Combo, you have been immense help(can't stress how grateful I am) and super patient with this chimpanzee, but it would be interesting to have the author of the code opine.

 

[Andrzejek]

No, you cannot contact any specific person here on TT.
Any code samples provided here is not supported, you have to figure out any kinks you need to modify. But that’s half of the fun, right?



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SoLost1]

Oh for sure, I wasn't looking for support, rather just curious because somehow MajorP had magically appeared on each thread with questions about his tool, so I was wondering if there was some forum function that I was missing in my typical fashion.

I hope he's just taking a break from the forum or on vacation and nothing happened to him, because his homebaked is very impressive and a lifesaver; I've been testing it against Excel and a lot of the time it is spot on, but there are unexpected divergences(likely due to the blackbox of the default guess the Excel uses if you do not specify as a parameter although I have read it may also use a bisecting method Newton hybrid as opposed to the pure Newton Raphson MajorP uses and the MS Excel documentation claims Excel uses as well)

Andrzejek, any insight on what's throwing the error? Is anyone else able to get the xirr_wrapper to work exactly as written? HALLLLP!

 

[Andrzejek]

If you still are getting "Compile error: Type mismatch: array or user-defined type expected", why not test the code with some hard-coded values just to see if the code will work?
Something like:

Option Explicit

Sub startHere()
Dim [blue]dates[/blue](2) As Date
Dim [red]pay[/red](2) As Currency

[blue]dates[/blue](0) = CDate("1/1/2020")
[blue]dates[/blue](1) = CDate("2/2/2021")
[blue]dates[/blue](2) = CDate("3/3/2022")

[red]pay[/red](0) = 12.34
[red]pay[/red](1) = 34.56
[red]pay[/red](2) = 56.43

Call XIRR_Wrapper([red]pay[/red], [blue]dates[/blue])

End Sub

Public Function XIRR_Wrapper([red]Payments()[/red] As Currency, [blue]dates()[/blue] As Date, Optional GuessRate As Double = 0.9)[green]
   'Must add a reference to the Xcel library. Use Tools, References, Microsoft Excel XX.X Object Library
   'XIRR_Wrapper = Excel.WorksheetFunction.Xirr(Payments, Dates, GuessRate)[/green]
Dim i As Integer

For i = LBound(Payments) To UBound(Payments)
    Debug.Print "Payments " & i & " is " & Payments(i)
Next i
For i = LBound(dates) To UBound(dates)
    Debug.Print "dates " & i & " is " & dates(i)
Next i

End Function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson