vba - 引用 UDF 中的特定单元格

Question

UDF 目标:将范围的值与同一工作表上的特定单元格进行比较。

发生错误 #Value! .

我认为，将通行证设置为此单元格的问题 ThisWorkbook.ThisWorksheet .如何胜任？

Function Fav(Diapozon As Range) As Long
    Application.Volatile

    Dim n As Long

    For x = 1 To 4
        For y = 0 To 1
            If Diapozon.Value = ThisWorkbook.Thisworksheet.Cells(x + 29, y + 10).Value Or  _
               Diapozon.Offset(0, 1).Value = ThisWorkbook.Thisworksheet.Cells(x + 29, y + 10).Value Then
                n = 1
            End If
        Next y
    Next x

    Fav = n
End Function

Answer 1

正确的。也许您的意思是 Activesheet？

Public Function Fav(ByVal Diapozon As Range) As Long
    Application.Volatile
    Dim n As Long, x As Long, y As Long
    For x = 1 To 4
        For y = 0 To 1
            If Diapozon.Value = ThisWorkbook.ActiveSheet.Cells(x + 29, y + 10).Value Or Diapozon.Offset(0, 1).Value = ThisWorkbook.ActiveSheet.Cells(x + 29, y + 10).Value Then
                n = 1
            End If
        Next y
    Next x
    Fav = n
End Function

如果您仅在工作表中将其用作 UDF，则删除工作表引用:

Public Function Fav(ByVal Diapozon As Range) As Long
    Application.Volatile
    Dim n As Long, x As Long, y As Long
    For x = 1 To 4
        For y = 0 To 1
            If Diapozon.Value = Cells(x + 29, y + 10).Value Or Diapozon.Offset(0, 1).Value = Cells(x + 29, y + 10).Value Then
                n = 1
            End If
        Next y
    Next x
    Fav = n
End Function