If someone can help, I need some in properly defining some call parameters in an Access 2003 to Excel 2003 VBA problem. I'm trying to use the XIRR function in the ATP 2.0 Type Library from Access. I have referenced the ATP 2.0 Type Library in my Access project. Here is the relevant VBA code (with a little pseudocode) I'm using behind a form:
Dim aCF as Variant 'this variant will hold the cash flows
Dim aDates as Variant 'this variant will hold the dates
Dim oATP2 As ATP2.OCATP
Set oATP2 = New ATP2.OCATP 'used in the Form_Open event to instantiate the object
In this model I always have five cash flows to work with: prior quarter value as an outflow, three months of net collections and the current quarter terminal value. (If there were more elements involved, I would certainly use a loop structure.) In a user-defined sub I redim the variants, load the arrays and call XIRR:
GetAssetReturn_X()
REDIM aDates(4) 'base 0
aDates(0) = DateSerial(Year(wDBBaseDate), Month(wDBBaseDate) - 2, 1) - 1 'e.g. 3-31- 2010
aDates(1) = DateSerial(Year(wDBBaseDate), Month(wDBBaseDate) - 1, 1) - 1 'e.g. 4-30-2010
aDates(2) = DateSerial(Year(wDBBaseDate), Month(wDBBaseDate), 1) - 1 'e.g. 5-31-2010
aDates(3) = wDBBaseDate 'e.g. 6-30-2010
aDates(4) = wDBBaseDate 'e.g. 6-30-2010
REDIM aCF(4) 'base 0
'from a recordset...
aCF(0) = -rs.Fields(2) 'pprd cash flow
aCF(1) = rs.Fields(3) 'net collection cprd - 2
aCF(2) = rs.Fields(4) 'net collection cprd - 1
aCF(3) = rs.Fields(5) 'net collection cprd
aCF(4) = rs.Fields(6) 'cprd cash flow
GetAssetReturn_X = oATP2.XIRR(aCF, aDates)
End Sub
The autosense feature works; when I type "oATP2." and I get a list of available functions ater the dot. So I assume the object is, in fact, correctly instantiated. Maybe not. However, whenever I run the code, I get the infamous runtime error "91: Object variable or With block variable not set." For the life of me, I'm missing the structural problem here. So I am presently assuming that the calling parameters have not been correctly described. I read somewhere these have to be variants. Maybe these need to be arrays or maybe range objects. Thanks.