Writing function to find minimum value of 2-D array in VBA

user_10439589 0
Viewed 55

I want to modify this sub procedure (Sub only) that looks for minimum temperature from 4 places (4 columns), into one with Function procedure.

 Sub test()

    Dim i As Integer, j As Integer
    Dim temp(30, 3) As Integer
    Dim minTemp(3) As Integer
    Dim tmp As Integer
    
    For i = 2 To 30
        For j = 2 To 5
            temp(i - 2, j - 2) = Cells(i, j).Value
        Next j
    Next i
    
    For j = 0 To 3
        minTemp(j) = 100
        tmp = 0
        
        For i = 0 To 30
            tmp = temp(i, j)
            
            If minTemp(j) >= tmp Then
                minTemp(j) = tmp
            End If

        Next i
        Cells(33, j + 2).Value = minTemp(j)
    Next j

End Sub

and I try to modify into:

Sub test()

    Dim i As Integer, j As Integer
    Dim temp(30, 3) As Integer

    
    For i = 2 To 30
        For j = 2 To 5
            temp(i - 2, j - 2) = Cells(i, j).Value
        Next j
    Next i
    
    MsgBox LowestTemp(temp())
    
End Sub

Function LowestTemp(tArr() As Variant) As Variant
    
    For i = LBound(tArr) To UBound(tArr)
    minVal = tArr(1)
        For j = 0 To 3
        If tArr(j) >= minVal Then
            minVal = tArr(j)
        End If
    Next i
    
    LowestTemp = minVal

End Function

but I find it hard to pass the 2-D array to the Function procedure, please help?

vba excel arrays
2 Answers

(1) You can read data from Excel into an array with one statement.

Dim temp As Variant
temp = ActiveSheet.Range("B2:E30").value

Note that temp is defined as Variant, not as array. A Variant can hold anything, and that includes an Array.

(2) There are different ways to pass an Array into a Function or Subroutine, but the types need to match. In your case, you are passing a 2-dimensional array of Integer as an argument to a function that expects a 1-dimensional array of Variant, and those type doesn't match. VBA can convert an Integer into a Variant, but it cannot convert whole Arrays, and it cannot convert different dimensions. The easiest way for you is to declare you function as

Function LowestTemp(tArr As Variant) As Variant

Again, a single Variant can hold anything and therefore would accept your Temp-array.

(3) There is a much easier, build-in way to get the minimum using Application.WorksheetFunction.Min:

Dim temp As Variant, minTemp
temp = ActiveSheet.Range("B2:E30").value 
minTemp = Application.WorksheetFunction.Min(temp)
user_75997980

Return Array Minimum

Sub test()

    Const rgAddress  As String = "B2:E30" ' at least 2 cells

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve
    Dim rg As Range: Set rg = ws.Range(rgAddress)
    
    Dim tData() As Variant: tData = rg.Value
    
    MsgBox LowestTemp(tData)
    
End Sub

Function LowestTemp(tData() As Variant) As Double
    
    Const tMax As Double = 1000000000
    
    Dim Temp As Double: Temp = tMax
    
    Dim Item As Variant
    
    For Each Item In tData
        If VarType(Item) = 5 Then
            If Item < Temp Then
                Temp = Item
            End If
        End If
    Next Item
    
    LowestTemp = Temp

End Function
user_98140690
Related