CODEWITHSUNDEEP
arraysmacosexcelvba
phreshsprout
phreshsprout

Reputation: 59

How to reference an array address of a VBA Max or Ubound function?

How do I reference an array address where the WorksheetFunction.Max has found the largest value within the array? There can be multiple highs within the array.

Dim myArraySum(1 to 4) as long 
Dim myArrayAddress(1 to 4) as integer

myArraySum(1) = 2
myArraySum(2) = 5
myArraySum(3) = 7
myArraySum(4) = 7

           myArrayHigh = Application.WorksheetFunction.Max(myArraySum)
           myArrayAddress = 'code needed

My desired output is 

myArrayHigh = 7
myArrayAddress(1) = 3
myArrayAddress(2) = 4

Upvotes: 1

Views: 368

Answers (2)

jacouh
jacouh

Reputation: 8741

The most straightforward way is to use a loop to check the values of every array element. 

Function GetMaxIndicesArray(ByRef myArraySum() As Long, ByRef myArrayAddress() As Integer) As Integer
  Dim i As Integer, j As Integer, iLow As Integer, iUp As Integer
  Dim lMax As Long

  iLow = LBound(myArraySum)
  iUp = UBound(myArraySum)

  lMax = Application.WorksheetFunction.Max(myArraySum)
  j = 1

  For i = iLow To iUp
    If (myArraySum(i) = lMax) Then
      myArrayAddress(j) = i
      j = j + 1
    End If
  Next

  GetMaxIndicesArray = j - 1
End Function

Sub test()
  Dim myArraySum(1 To 4) As Long
  Dim myArrayAddress(1 To 4) As Integer

  Dim i As Integer, n As Integer

  myArraySum(1) = 2
  myArraySum(2) = 5
  myArraySum(3) = 7
  myArraySum(4) = 7

  myArrayHigh = Application.WorksheetFunction.Max(myArraySum)
  'myArrayAddress = "" 'code needed

  n = GetMaxIndicesArray(myArraySum, myArrayAddress)

  Debug.Print "myArrayHigh = " & myArrayHigh
  For i = 1 To n
    Debug.Print "myArrayAddress(" & i & ") = " & myArrayAddress(i)
  Next

End Sub

Then in you debugger window, just type

test

The Result is like:

myArrayHigh = 7
myArrayAddress(1) = 3
myArrayAddress(2) = 4

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Use a simple loop:

Sub dural()
   Dim myArraySum(1 To 4) As Long
   Dim myArrayAddress(1 To 4) As Integer
   Dim myArrayHigh As Long, k As Long, msg As String

   myArraySum(1) = 2
   myArraySum(2) = 5
   myArraySum(3) = 7
   myArraySum(4) = 7

   myArrayHigh = Application.WorksheetFunction.Max(myArraySum)

   k = 1
   For i = LBound(myArraySum) To UBound(myArraySum)
      If myArraySum(i) = myArrayHigh Then
         myArrayAddress(k) = i
         k = k + 1
      End If
   Next i

   msg = ""
   For i = LBound(myArrayAddress) To UBound(myArrayAddress)
      msg = msg & vbCrLf & i & vbTab & myArrayAddress(i)
   Next i

   MsgBox msg
End Sub

Upvotes: 1

Related Questions