Type Mismatch error when calling IRR

[RichW]

When I call the IRR function within VBA, I get an error
number 13 ("Type Mismatch"). I have even tried cutting and
pasting the sample code from the Access Help file article
on the IRR function, but with the same results. I have
tried every combination conceivable of alternate arguments
(changing the array from type variant to type double,
omitting the guess variable, etc., etc.), but in every
single case I get the Type Mismatch error. What gives? I
am using Access 2002 (XP). Any thoughts? TIA

 

[John Nurick]

Hi Rich,

The sample code runs just fine for me (Access 2002 SP1, Windows 2000
SP3).

Are you getting the type mismatch error at compile time or run time?

As far as I can tell, the cashflows must be in an actual array of
doubles: you can't use a variant containing an array of doubles, or an
array of variants. So if you don't know the number of cashflows in
advance you'll need to declare a dynamic array
Dim Values() As Double
and then use ReDim to fix the number of elements when you know it.

 

[George Nicholson]

I don't know if this is the problem but passing a Null (or string) value to
a function that expects a number can generate Type Mismatch errors.
Therefore, I'd double check what values are being passed to IRR within the
array.