Subscript out of range error

[kuda25]

Dear all.

I am trying to write code that finds the max value in an array and returns te value and index reference of the value.

I have come up with the following code so far but I am not getting a "subscript out of range" error. have no idea where I have gone wrong. any ideas.



Option Explicit

Sub Optimal_Structure()


'This model finds the optimal structure for an agent structure.

Dim Agent_type As Variant
Dim Total_volume As Double
Dim Levels As Double
Dim Agent_limit As Variant
Dim BOR_table As Variant

'set agent type array
Total_volume = Range("Total_volume")
Agent_type = Range("agent_type").Value
Agent_limit = Range("Agent_limit").Value
BOR_table = Range("BOR_table").Value
Levels = Range("levels").Value

'Find max value in range and index.

ArrayMax (BOR_table)


End Sub

' Should return max value in Value_tables and index

Function ArrayMax(arr As Variant, Optional ByVal First As Variant, _
Optional ByVal Last As Variant, Optional MaxIndex As Long) As Variant
Dim Index As Long

If IsMissing(First) Then First = LBound(arr)
If IsMissing(Last) Then Last = UBound(arr)

MaxIndex = First
ArrayMax = arr(MaxIndex)

For Index = First + 1 To Last
If ArrayMax < arr(Index) Then
MaxIndex = Index
ArrayMax = arr(MaxIndex)
End If
Next
End Function

 

[SkipVought]

Usually means you have referenced a non-existing object.

Have you looked at your values in debug?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Unless you have option base 1 somewhere else in your module then it's probably to do with your definitions of 1st and last in your array...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question