VBA最小和最大功能总是返回0

发布于 2025-02-05 00:08:14 字数 327 浏览 1 评论 0原文

您好，我试图从数组中获取最小值和最大值，尽管如此，它总是返回“ 0”。我的代码：

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

SmallestCapacity = Application.Min(MachineCapacitySmallestArray)

在范围内，我有自然数量

，我尝试将这些单元格格式化为数字等。但是没有任何作用。我犯了什么错误以及如何解决？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

罪#恶を代价 2025-02-12 00:08:14

根据评论，看来您的问题是您的数据，您的单元格中可能有字符串，而不是

已经提到的数字（也许是某种程度上导入？），更改单元格格式不会更改单元格的内容，它只是定义如何显示数据。数字3.14可以显示为3，3.140000，00003.14或3.14e+00，没有任何变化的值。但是，字符串'3.14是字符的组合3，。，1和4，与数字无关。在之后设置单元格式该值在单元格中不会将其转换为数字。

如果您将数据读取到VBA中，VBA将获得Excel的确切值，在您的情况下，您必须手动将其转换为数字，例如使用以下例程。如果单元格不包含可以转换为数字的东西，则on错误简历下一个将防止类型不匹配。

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

现在，只需将此例程添加到您的代码中即可。如果您也想在Excel中使用数字，请从上一个语句中删除注释符号。

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

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

回复收藏 0 原文

~没有更多了~