arrays - VBA 数组。最小元素及其编号

Question

如何找到数组 V(12,9) 的最小元素及其编号？

Private Sub Command2_Click()
Dim V(1 To 12, 1 To 9) As Integer
Randomize
For i = 1 To 12
For j = 1 To 9
V(i, j) = Rnd * 50
Next j
Next i

Answer 1

识别二维数组中的最小值

• 在即时窗口中查看信息和结果 (Ctrl+G)。它比消息框中的演示文稿更好，更具教育意义。
• 对于这么小的数字，如果需要的话，您可以用整数替换所有长整数。这是一个 link描述为什么我们不再使用 Integer。

Private Sub Command2_Click()
    
    Const Max As Long = 50
    
    ' Populate the array.

    Dim V(1 To 12, 1 To 9) As Long
    
    Dim i As Long
    Dim j As Long
    
    Randomize
    For i = 1 To 12
        For j = 1 To 9
            V(i, j) = Rnd * Max
        Next j
    Next i
    
    Debug.Print GetDataString(V, , , "Random numbers from 0 to " & Max)
    
    Debug.Print "How Min Was Changed in the Loop (It Started at " & Max & ")"
    Debug.Print "The array was looped by rows."
    Debug.Print "Visually find the following values to understand what happened."
    Debug.Print "i", "j", "Min"

    ' Calculate the minimum.
    
    Dim Min As Long: Min = Max
    
    For i = 1 To 12
        For j = 1 To 9
            If V(i, j) < Min Then
                Min = V(i, j)
                Debug.Print i, j, Min
            End If
        Next j
    Next i
    
    Debug.Print "The minimum is " & Min & "."
    
    MsgBox GetDataString(V, , , "Random numbers from 0 to " & Max) & vbLf _
        & "The minimum is " & Min & ".", vbInformation
    
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the values of a 2D array in a string.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetDataString( _
    ByVal Data As Variant, _
    Optional ByVal RowDelimiter As String = vbLf, _
    Optional ByVal ColumnDelimiter As String = " ", _
    Optional ByVal Title As String = "PrintData Result") _
As String
    
    ' Store the limits in variables
    Dim rLo As Long: rLo = LBound(Data, 1)
    Dim rHi As Long: rHi = UBound(Data, 1)
    Dim cLo As Long: cLo = LBound(Data, 2)
    Dim cHi As Long: cHi = UBound(Data, 2)
    
    ' Define the arrays.
    Dim cLens() As Long: ReDim cLens(rLo To rHi)
    Dim strData() As String: ReDim strData(rLo To rHi, cLo To cHi)
    
    ' For each column ('c'), store strings of the same length ('cLen')
    ' in the string array ('strData').
    
    Dim r As Long, c As Long
    Dim cLen As Long
    
    For c = cLo To cHi
        ' Calculate the current column's maximum length ('cLen').
        cLen = 0
        For r = rLo To rHi
            strData(r, c) = CStr(Data(r, c))
            cLens(r) = Len(strData(r, c))
            If cLens(r) > cLen Then cLen = cLens(r)
        Next r
        ' Store strings of the same length in the current column
        ' of the string array.
        If c = cHi Then ' last row (no column delimiter ('ColumnDelimiter'))
            For r = rLo To rHi
                strData(r, c) = Space(cLen - cLens(r)) & strData(r, c)
            Next r
        Else ' all but the last row
            For r = rLo To rHi
                strData(r, c) = Space(cLen - cLens(r)) & strData(r, c) _
                    & ColumnDelimiter
            Next r
        End If
    Next c
    
    ' Write the title to the print string ('PrintString').
    Dim PrintString As String: PrintString = Title
    
    ' Append the data from the string array to the print string.
    For r = rLo To rHi
        PrintString = PrintString & RowDelimiter
        For c = cLo To cHi
            PrintString = PrintString & strData(r, c)
        Next c
    Next r
    
    ' Assign print string as the result.
    GetDataString = PrintString

End Function