Excel VBA - 函数的最小值最大值范围

Excel VBA - function min max of a range

提问by hornetbzz

I've got 2 very similar functions, that were working before I switched my code to the Option Explicitfor debugging puposes (success !). Since then, the Maxfunction does not workanymore and I can't elaborate the reason why and solve it as an xl vba perfect noob.

我有 2 个非常相似的函数，在我将代码切换到用于调试目的的Option Explicit之前，它们正在工作（成功！）。从那时起，该Max功能不再起作用，我无法详细说明原因并将其作为 xl vba 完美菜鸟解决。

• The Max function(does not work):

  Function MaxAddress(The_Range) As Variant
  ' See http://support.microsoft.com/kb/139574
  
  Dim MaxNum As Variant
  Dim cell As Range
  
    ' Sets variable equal to maximum value in the input range.
    MaxNum = Application.Max(The_Range)
    ' Loop to check each cell in the input range to see if equals the
    ' MaxNum variable.
    For Each cell In The_Range
       If cell.Value = MaxNum Then
          ' If the cell value equals the MaxNum variable it
          ' returns the address to the function and exits the loop.
          MaxAddress = cell.Address
          Exit For
       End If
    Next cell
  
  End Function
  

• The runtime error:

  I receive "error 91" at the runtime, with the Xmax valuing : "Nothing" Error 91 stands for : undefined object or With block variable

• The minfunction (works)

  Function MinAddress(The_Range) As Variant
  ' See http://support.microsoft.com/kb/139574
  
  Dim MinNum As Variant
  Dim cell As Range
  
    ' Sets variable equal to maximum value in the input range.
    MinNum = Application.Min(The_Range)
    ' Loop to check each cell in the input range to see if equals the
    ' MaxNum variable.
    For Each cell In The_Range
       If cell.Value = MinNum Then
          ' If the cell value equals the MaxNum variable it
          ' returns the address to the function and exits the loop.
          MinAddress = cell.Address
          Exit For
       End If
    Next cell
  
    End Function
  

• Max 函数（不起作用）：

  Function MaxAddress(The_Range) As Variant
  ' See http://support.microsoft.com/kb/139574
  
  Dim MaxNum As Variant
  Dim cell As Range
  
    ' Sets variable equal to maximum value in the input range.
    MaxNum = Application.Max(The_Range)
    ' Loop to check each cell in the input range to see if equals the
    ' MaxNum variable.
    For Each cell In The_Range
       If cell.Value = MaxNum Then
          ' If the cell value equals the MaxNum variable it
          ' returns the address to the function and exits the loop.
          MaxAddress = cell.Address
          Exit For
       End If
    Next cell
  
  End Function
  

• 在运行时错误：

  我在运行时收到“错误 91”，Xmax 值：“无”错误 91 代表：未定义的对象或块变量

• 该分函数（作品）

  Function MinAddress(The_Range) As Variant
  ' See http://support.microsoft.com/kb/139574
  
  Dim MinNum As Variant
  Dim cell As Range
  
    ' Sets variable equal to maximum value in the input range.
    MinNum = Application.Min(The_Range)
    ' Loop to check each cell in the input range to see if equals the
    ' MaxNum variable.
    For Each cell In The_Range
       If cell.Value = MinNum Then
          ' If the cell value equals the MaxNum variable it
          ' returns the address to the function and exits the loop.
          MinAddress = cell.Address
          Exit For
       End If
    Next cell
  
    End Function
  

How I call both functions :

我如何调用这两个函数：

Set rng = ws_source.Range("3:3")
X_min = MinAddress(rng)
X_max = MaxAddress(rng) ' returns : X_max = Nothing

The data are in the row 3, containing formatted numbers and text.

数据位于第 3 行，包含格式化的数字和文本。

采纳答案by whytheq

(not an answer but too big for a comment)

（不是答案，但评论太大了）

I have the following in a normal module and it works fine:

我在普通模块中有以下内容，并且工作正常：

Function MaxAddress(The_Range) As Variant
' See http://support.microsoft.com/kb/139574

Dim MaxNum As Variant
Dim cell As Range

  ' Sets variable equal to maximum value in the input range.
  MaxNum = Application.Max(The_Range)
  ' Loop to check each cell in the input range to see if equals the
  ' MaxNum variable.
  For Each cell In The_Range
     If cell.Value = MaxNum Then
        ' If the cell value equals the MaxNum variable it
        ' returns the address to the function and exits the loop.
        MaxAddress = cell.Address
        Exit For
     End If
  Next cell

End Function

Sub xxx()
Dim rng As Range
Dim X_max As String
Set rng = ThisWorkbook.Sheets(1).Range("3:3")
X_max = MaxAddress(rng)
MsgBox (X_max)
End Sub

回答by Abe Gold

Not sure why min works, but I believe it's supposed to be

不知道为什么 min 有效，但我相信它应该是

Application.WorksheetFunction.Max

&

&

Application.WorksheetFunction.Min