CODEWITHSUNDEEP
functionvbaexcel
user1155299
user1155299

Reputation: 927

finding minimum positive value (greater than 0) in an array in VBA

I have an array (AlphaVector) with the following values:

-0.2
-0.7
0
0.4
0.3
0.1

I want to pick the positive values from the above array and place it in another array named Alpha so that I can pick the minimum positive value from Alpha. My goal is to get the value 0.1 from the above array. Here's the code I have so far. Alpha gets populated ok as the Msgbox indicates the correct values, but the return value I am getting is 0 instead of 0.1.

Function FindMin(AlphaVector)
Dim iCount As Long
Dim N As Integer, i As Integer
N = AlphaVector.Cells.Count
Dim Alpha() As Double
ReDim Alpha(N) As Double
For i = 1 To N
    If AlphaVector(i) > 0 Then
        Alpha(i) = AlphaVector(i)
    Else
        Alpha(i) = 100000000000#
    End If
    MsgBox ("Alpha(i)= " & Alpha(i))
    MsgBox ("AlphaVector(i)= " & AlphaVector(i))
Next i
FindMin = WorksheetFunction.Min(Alpha)
End Function

Can you please tell me how to fix it? Also, if there is a more efficient way to write it, perhaps without introducing Alpha, please let me know. Thanks.

Upvotes: 1

Views: 14578

Answers (4)

Olle Sjögren
Olle Sjögren

Reputation: 5385

You are using the wrong index for array Alpha. It is zero-based, and since you populate it by using i, starting from 1, you leave Alpha(0) to be the default, which is 0.

WorksheetFunction.Min(Alpha) returns the smallest value, which you now know always will be 0. :-)

You need to redesign your function to handle this. Example will follow shortly.

EDIT - code sample - updated to work as a UDF

I finished this sample before I saw your comments, so in my code AlphaVector is an array. Anyway, it is always better to explicitly declare any variables and avoid the Variant type if you can, which is used if you don't declare variables. This is why I'm using Option Explicit. You should too. :-)

There are probably a lot of ways to do what you want, but this is one:

Option Explicit

Function FindMin(AlphaVector)
    Dim iNew As Integer, i As Integer
    Dim iCount As Integer
    Dim Alpha() As Variant

    iCount = AlphaVector.Cells.Count

    '***** Use size of AlphaVector
    ReDim Alpha(0 To iCount - 1)
    iNew = 0

    For i = 1 To iCount
        '***** Only save values greater than 0
        If AlphaVector(i) > 0 Then
            Alpha(iNew) = AlphaVector(i)
            '***** Keep track of how many values you save
            iNew = iNew + 1
        End If
    Next i

    '***** Remove any empty items in the Alpha array
    ReDim Preserve Alpha(0 To iNew - 1)

    '***** Reture result of the Min function
    FindMin = WorksheetFunction.Min(Alpha)
End Function

Upvotes: 3

A. Webb
A. Webb

Reputation: 26446

Just an FYI, you can do this in a single cell in Excel with an array function. If your example numbers are in A1:A6 use

 =MIN(IF(A1:A6<=0,"",A1:A6))

but make sure you enter it with Ctrl-Shift-Enter to make it an array formula. VBA not required.

Upvotes: 2

InContext
InContext

Reputation: 2501

you could change to the below, a bit cleaner:


Function FindMinUpdated(AlphaVector As Range)



Dim cell As Range
Dim lowValCell As Double



lowValCell = Abs(AlphaVector(1))

For Each cell In AlphaVector

    lowValCell = IIf(cell < lowValCell And cell > 0, cell, lowValCell)

Next cell




FindMinUpdated = lowValCell



End Function

Upvotes: 0

markblandford
markblandford

Reputation: 3193

The problem here is how you are setting the dimensions of your Alpha variable array. In your code, I assume your module does not have Option Base 1 declared. As a result, Alpha has the dimensions Alpha(0 to 6) and as it is a Double array, the first, zero element defaults to 0. The easy change is to change your code to:

ReDim Alpha(1 to N) As Double

Upvotes: 1

Related Questions