Finding max value in Array (vba)

[VBAn00b]

Dear forum members,

I have googled for hours but couln't find a understandable code that helps me to do the following:

Find the max. value within an array

I have a multi dimentional array with the date, the fund and the stock price. I need to calculate the max so I can find the time with the highest stock price within the array.

I hope someone can help me back on my way to solve this problem!

 

[iliace]

Can you also give the respective code for finding the minimum? Just switching > to < doesn't do the trick :D
Held would be much appreciated.

Many thanks!

To ensure that it works, you should initialize the myMax to the lowest possible value of the data type. For example, if you're looking for a maximum of 32-bit signed integers, you'll want so start out with something like:

myMax = -2e9

Conversely, when looking for a minimum, initialize to the highest possible value, such as:

myMin = 2e9

etc. Adjust the initial value depending on your data type, data set, etc.

Hope this helps.

 

[MoniqueB]

Thank you iliace,
I've tried using the code you wrote here, but I keep getting 'subscript out of range' when I hit the line 'If arr(i, col) > myMax Then'
my array is arrAvailSpec(1 to 6) and I define it as such outside the function, i've tried:
FindMax(arrAvailSpec(), 1)
and I've tried
FindMax(arrAvailSpec, 1)

Both ways I get the error 'subscript out of range' at the line in the function indicated above.
Do you know what I'm missing?

Thank you,

 

[NdNoviceHlp]

Monique when U define an array arrAvailSpec(1 to 6) then there are place holders for data at:arrAvailSpec(1), arrAvailSpec(2), arrAvailSpec(3), ...etc, arrAvailSpec(6). So FindMax(arrAvailSpec(1)) for example should produce a result. HTH. Dave

 

[NdNoviceHlp]

Monique my apologies I provided a syntax response.. I should have reviewed the FindMax function. I'm not certain why iliace refers to columns in his FindMax code... it really seems to complicate things. If you want to find the maximum value of your single dimension array here's some code...

Public Function FindMax(arr() As Variant) As Double
  Dim myMax As Double
  Dim i As Long
  myMax = 0
  For i = LBound(arr) To UBound(arr)
    If arr(i) > myMax Then
      myMax = arr(i)
    End If
  Next i
FindMax = myMax
End Function

To use...FindMax(ArrayName()). In your example...

FindMax(arrAvailSpec())

HTH. Dave

 

[NdNoviceHlp]

Not quite happy with that last post either. This is better. Dave

Public Function FindMaxArrayVal(Arr() As Variant) As Double
'Arr is array name
'return maximum array value
  Dim myMax As Double
  Dim i As Long
  myMax = Arr(0)
  For i = LBound(Arr) + 1 To UBound(Arr)
    If Arr(i) > myMax Then
      myMax = Arr(i)
    End If
  Next i
FindMaxArrayVal = myMax
End Function
Public Function FindMinArrayVal(Arr() As Variant) As Double
'Arr is array name
'return minimum array value
  Dim myMin As Double
  Dim i As Long
  myMin = Arr(0)
  For i = LBound(Arr) + 1 To UBound(Arr)
    If Arr(i) < myMin Then
      myMin = Arr(i)
    End If
  Next i
FindMinArrayVal = myMin
End Function

To test and operate...

Dim ArrayName() As Variant
ArrayName = Array(11, 33, 5, 7, 3)
MsgBox FindMaxArrayVal(ArrayName)
MsgBox FindMinArrayVal(ArrayName)