Excel VBA返回有色单元格的最大值

I'm trying to create a function go through the sheet and return the highest value of all the green (user input color) colored cells. I'm very new to VBA and I cannot get the function to output anything besides "#VALUE!". Here is what my code looks like:

我正在尝试创建一个函数遍历工作表并返回所有绿色(用户输入颜色)彩色单元格的最高值。我对VBA很新,除了“#VALUE!”之外我无法输出任何功能。这是我的代码的样子:

Function ColorMax(Color As Integer) As Single
Dim array1()
Dim x As Integer
Dim y As Integer
Dim n As Integer
n = 1
For x = 1 To 1000
    For y = 1 To 1000
        If Cells(x, y).Interior.ColorIndex = Color Then
            array1(n) = Cells(x, y).Value
            n = n + 1
        End If
    Next
Next
ColorMax = Application.WorksheetFunction.max(array1)
End Function

Is there a way to fix this in order to get my desired output?

有没有办法解决这个问题,以获得我想要的输出?

1 个解决方案

#1

Arrays are not dynamic, you must set the size progamatically.

数组不是动态的,您必须以progamatically方式设置大小。

In this case there is no need for the array, it adds no benefit, just test it and replace the variable if value is larger:

在这种情况下,不需要数组,它没有任何好处,只需测试它并在值更大时替换变量:

Function ColorMax(Color As Integer) As Single

Dim x As Integer
Dim y As Integer
ColorMax = 0
With Application.Caller.Parent
    For x = 1 To 1000
        For y = 1 To 1000
            If Cells(x, y).Interior.ColorIndex = Color And .Cells(x, y).Value > ColorMax  Then
                ColorMax = .Cells(x, y).Value
            End If
        Next
    Next
End With

End Function

#1