VBA MIN and MAX function always returning 0-CodePudding

Hello I am trying to get the MIN and MAX values from the array and it always returns "0" despite anything. My code:

Dim MachineCapacitySmallestArray() As Variant
MachineCapacitySmallestArray = thisworkbook.worksheets(1).range("C25:D25")

SmallestCapacity = Application.Min(MachineCapacitySmallestArray)

in range I have natural numbers

I tried formatting those cells to numbers etc. but nothing works. What is the mistake I'm making and how to fix it?

CodePudding user response：

According to the comments, it seems that your problem is your data, you have likely strings in your cell, not numbers (maybe somehow imported?)

As already mentioned, changing the cell format doesn't change the content of a cell, it just defines how to display data. The number 3.14 can be displayed as 3, as 3.140000, as 00003.14 or as 3.14E 00, nothing changes it's value. However, a String '3.14 is a combination of the characters 3, ., 1 and 4 and has nothing to do with a number. Setting a cell format after the value is in the cell will not convert it to a number.

If you read your data into VBA, VBA will get the exact values from Excel and in your case, you will have to convert it into numbers manually, for example with the following routine. The On Error Resume Next will prevent a type mismatch if a cell doesn't contain something that can be converted into a number.

Sub ArrToNumber(ByRef arr)
    Dim i As Long, j As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            On Error Resume Next
            arr(i, j) = CDbl(arr(i, j))
            On Error GoTo 0
        Next
    Next
End Sub

Now just add a call to this routine to your code. If you want to have the numbers also in Excel, remove the comment sign from the last statement.

Dim MachineCapacitySmallestArray() As Variant
MachineCapacitySmallestArray = thisworkbook.worksheets(1).range("C25:D25")
ArrToNumber MachineCapacitySmallestArray 
SmallestCapacity = Application.Min(MachineCapacitySmallestArray)
' thisworkbook.worksheets(1).range("C25:D25") = MachineCapacitySmallestArray