vba 在VBA中的数组中找到最小正值（大于0）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13383208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
finding minimum positive value (greater than 0) in an array in VBA
提问by user1155299
I have an array (AlphaVector) with the following values:
我有一个具有以下值的数组 (AlphaVector)：
-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.
我想从上面的数组中选择正值并将其放入另一个名为 Alpha 的数组中，以便我可以从 Alpha 中选择最小的正值。我的目标是从上面的数组中获取值 0.1。这是我到目前为止的代码。由于 Msgbox 指示正确的值，Alpha 被填充好，但我得到的返回值是 0 而不是 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.
你能告诉我如何解决吗？另外，如果有更高效的写法，也许不引入Alpha，请告诉我。谢谢。
回答by Olle Sj?gren
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.
您对 array 使用了错误的索引Alpha。它是从零开始的，并且由于您使用 填充它i，从 1 开始，您保留Alpha(0)默认值，即 0。
WorksheetFunction.Min(Alpha)returns the smallest value, which you now know always will be 0. :-)
WorksheetFunction.Min(Alpha)返回最小值，您现在知道该值始终为 0。:-)
You need to redesign your function to handle this. Example will follow shortly.
你需要重新设计你的函数来处理这个问题。示例将很快跟进。
EDIT - code sample - updated to work as a UDF
编辑 - 代码示例 - 更新为 UDF
I finished this sample before I saw your comments, so in my code AlphaVectoris an array. Anyway, it is always better to explicitly declare any variables and avoid the Varianttype if you can, which is used if you don't declare variables. This is why I'm using Option Explicit. You should too. :-)
我在看到你的评论之前完成了这个示例，所以在我的代码中AlphaVector是一个数组。无论如何，最好显式声明任何变量并尽可能避免使用Variant类型，如果您不声明变量，则使用该类型。这就是我使用Option Explicit. 你也应该。:-)
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
回答by A. Webb
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
仅供参考，您可以使用数组函数在 Excel 的单个单元格中执行此操作。如果您的示例编号在 A1:A6 中，请使用
=MIN(IF(A1:A6<=0,"",A1:A6))
but make sure you enter it with Ctrl-Shift-Enterto make it an array formula. VBA not required.
但请确保您输入它Ctrl-Shift-Enter以使其成为数组公式。不需要 VBA。
回答by markblandford
The problem here is how you are setting the dimensions of your Alphavariable array. In your code, I assume your module does not have Option Base 1declared. As a result, Alphahas the dimensions Alpha(0 to 6)and as it is a Doublearray, the first, zero element defaults to 0. The easy change is to change your code to:
这里的问题是如何设置Alpha变量数组的维度。在您的代码中，我假设您的模块尚未Option Base 1声明。因此，Alpha有维度Alpha(0 to 6)，因为它是一个Double数组，第一个零元素默认为 0。简单的更改是将代码更改为：
ReDim Alpha(1 to N) As Double
回答by InContext
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
