Excel VBA："compile error: sub or function not defined"，VBA 选择"results ="

Question

Public Function Myfunction(Roundnumber As Integer, teams As Integer) As Range

Dim i As IntegerDim j As IntegerDim Result() As IntegerReDim Result(0 To (teams - 1))

For i = 0 To (teams - 1)
    For j = 0 To (teams - 1)
         If Not IsError(Worksheets("Sheet2").Cells(j + 57, i + 3).Value) Then
            If Roundnumber = Worksheets("Sheet2").Cells(j + 57, i + 3).Value Then
     Result(i) = "yeah"
            End If
         End If
    Next j
Next i
Myfunction = Result

结束函数

Answer 1

由于您没有在任何地方声明Result与Dim，当您写:

Result(i) = i + j

则VBA将Result视为传递i值的子或函数。因为这个Sub或Function在你的代码中不存在，那么你就会得到你所看到的错误。

你需要在你的第一个For循环开始之前做这样的事情:

Dim Result() As Integer
ReDim Result(1 To Teams)

将Result声明为一个整数数组，并将ReDim声明为Teams的大小。你需要给它一个正确的尺寸，否则VBA不会知道它有多大，也无法在里面存储任何东西。

注意，ReDim是一个开销很大的操作，所以当您知道数组的大小时，只需要执行一次。

Answer 2

我让它像这样工作

Public Function Myfunction(Roundnumber As Range, teams As Variant) As Variant

Dim i As LongDim As LongDim Result()作为变量ReDim Result(0 To teams, 0)

For i = 1 To teams
    For j = 1 To teams
         If Not IsError(Worksheets("Sheet2").Cells(j + 56, i + 2).Value) Then
            If Roundnumber = Worksheets("Sheet2").Cells(j + 56, i + 2).Value Then
     Result(j - 1, 0) = j & "vs" & i
            End If
         End If
    Next j
Next i
Myfunction = Result

结束函数