Subscript out of range - Arrays & NPV

[ducker]

Attempting to use NPV function in Access 97. I keep getting this error. Dispite the fact that I believe I'm using the function properly, as well as setting up the array correctly.

pretty straight forward as far as I can tell. I keep getting a Subscript out of range on the NPV function statement. specfically the "NPVarray()" part.

Private Sub calc_adjustment_Click()
    Dim dbs As Database
    Dim rst As Recordset
    Dim strsql As String
    Dim NPVarray
    Dim Count, n As Integer
    Dim value, interest As Double
    
Set dbs = CurrentDb

With [Forms]![frm_Merge=Yes Data Entry]
    Count = ![MonthsToGo]
    ReDim NPVarray(Count) As Double
    
    value = ![CurrentBalance]
    interest = InputBox("Enter the interest rate.", "Calculate Rent Adjustment") / 100
    n = 0
    NPVarray(n) = value * Count * -1
    n = n + 1
    
    While n <= Count
        NPVarray(n) = value
        n = n + 1
    Wend
    n = 0

    ![npv] = npv(interest, NPVarray())

End With
End Sub

 

[RuralGuy]

Since this is a zero based array, I'm pretty sure...

While n <= Count

should be...

While n < Count

There are a couple of other issues of which you should be aware. In your Dimention statements you have:

Dim Count, n As Integer
Dim value, interest As Double

Because of the way Access works, Count and value are Dimentioned as Variants. You probably wanted this:

Dim Count As Integer, n As Integer
Dim value As Double, interest As Double

And finally, it does not take much to confuse Access. Having a Field named [npv] while using the npv Function could do it. It is probably not the case here and npv is not an Access reserved word but it is just something to consider when picking names in the future.

Additional: You should explicitly clear anything you set.

Set dbs = Nothing

Before you exit the Sub!

 

[ducker]

Since this is a zero based array, I'm pretty sure...

While n <= Count

should be...

While n < Count

Actually, I don't believe it is. since the first value in the array for the NPV function is a negative number, followed by 45 payments. I actually want to run it to the 45th itteration.

There are a couple of other issues of which you should be aware. In your Dimention statements you have:

Dim Count, n As Integer
Dim value, interest As Double

Because of the way Access works, Count and value are Dimentioned as Variants. You probably wanted this:

Dim Count As Integer, n As Integer
Dim value As Double, interest As Double

Yea, that was my mistake. I keep forgetting.

And finally, it does not take much to confuse Access. Having a Field named [npv] while using the npv Function could do it. It is probably not the case here and npv is not an Access reserved word but it is just something to consider when picking names in the future.

I'll give this a try. I know what you mean. I get really frustrated at the novice developers in the company that often use "!" or "#" in their field names!!!

Additional: You should explicitly clear anything you set.

Set dbs = Nothing

Before you exit the Sub!

This is something I haven't heard before.

 

[workmad3]

wouldnt using a for loop make more sense here?

For n = 0 to count
NPVarray = value
Next n

seeing as you are using the while loop as a for loop anyway

 

[ducker]

either way will get it to loop. the problem is the NPV function itself is failing.

 

[workmad3]

hmm... looking again. do you need to pass NPVarray() or NPVarray ?
It could be complaining because you are giving the subscript reference symbols(read - brackets) without giving a reference.

 

[RuralGuy]

Actually, I don't believe it is. since the first value in the array for the NPV function is a negative number, followed by 45 payments. I actually want to run it to the 45th itteration.

ReDim NPVarray(Count) As Double

If Count is = to 45 then the Redim will Dimension the array to 45 elements 0 to 44. Maybe you need to redim to (Count+1)

 

[workmad3]

or maybe you need to do

ReDim NPVarray (-1 to Count)

 

[ducker]

Ok. Tweeked the code with all the suggestions. And well, now I can't get it to compile
COMPILE ERROR:
Type mismatch:array or user-defined type expected.

The error on the array part of the NPV function.
If I manually set the variable NPVarray in the first DIM statement as an array of length 46 it will compile fine. for some reason the ReDim doesn't seem to be working properly.

I'm not passing the value to another procedure, but simply want to display it on a form.

Thanks for all the help so far guys!

Private Sub calc_adjustment_Click()
    Dim dbs As Database
    Dim rst As Recordset
    Dim strsql As String
    Dim NPVarray
    Dim Count As Integer
    Dim n As Integer
    Dim value As Double
    Dim interest As Double

    
Set dbs = CurrentDb

With [Forms]![frm_Merge=Yes Data Entry]
    Count = ![MonthsToGo]
    ReDim NPVarray(Count + 1) As Double
    
    value = ![CurrentBalance]
    interest = InputBox("Enter the interest rate.", "Calculate Rent Adjustment") / 100
    n = 0
    NPVarray(n) = value * Count * -1
    n = n + 1
    
    For n = 1 To Count
        NPVarray(n) = value
    Next n
    n = 0
    ![npv_value] = npv(interest, NPVarray())

End With
End Sub

 

[RuralGuy]

Dim NPVarray()

 

[ducker]

actually:

dim NPVarray() as double

Woooo.... it worked

Thanks all.