excel - Excel VBA - UDF 返回 0 或空或 #value

Question

我正在创建我的自定义功能。我编写了代码并将其作为“子”进行了测试，它运行良好。然后我将它转换为一个函数，以便能够在一般情况下使用它。我改变的事情是；添加函数声明，从 excel 单元格获取输入并指定函数输出。所有其他人保持不变。

我的函数只有一个输入，即从 Excel 表中选择的单元格。我希望该函数返回一个输出。但是，它返回 0。 • 函数声明。“函数 IbpBomLevel(ByVal Target As Range) As Variant • 作为选定单元格的函数输入。“ProductID = Target • 函数输出。“IbpBomLevel = 全文

我使用选项显式来避免不存在的功能。另外，我确定输入，函数确实将选定的单元格作为输入。但问题是在每个循环中“ProductID”都必须更改。但是，当我声明“IbpBomLevel（函数的输出）= ProductID 并看到 ProductID 是用户从单元格中选择的第一个参数时。这意味着循环不起作用。当我将其测试为“子”时，我得到的结果是我想要。我不确定是什么问题。

Option Explicit

Function IbpBomLevel(ByVal Target As Range) As Variant

Dim Wb As Workbook
Dim Ws As Worksheet
Dim MyRange As Range
Dim SourceID As Variant
Dim SourceID2 As Variant
Dim SourceID3 As Variant
Dim Product As Variant
Dim Item As Variant
Dim Location As Variant
Dim Resource As Variant
Dim I As Variant
Dim T As Variant
Dim Z As Variant
Dim X As Variant
Dim Y As Variant
Dim Index As Variant
Dim Index2 As Variant
Dim Index3 As Variant
Dim BomLevel As Variant
Dim FoundCell As Variant
Dim fullText As Variant

Dim ProductID As Variant
ProductID = Target

Set Wb = Workbooks("Kitap.xlsx")
Windows("Kitap.xlsx").Activate

On Error GoTo T_Error

Set Ws = Wb.Worksheets("Production Source Header")
Sheets("Production Source Header").Select

Set MyRange = Worksheets("Production Source Header").Range("B:C")

SourceID = CVar(Application.WorksheetFunction.VLookup(ProductID, MyRange, 2, False))

I = 1
T = 0
Z = 1

If IsEmpty(SourceID) = False Then

    Do While (IsEmpty(SourceID) = False) And (T = 0)

        BomLevel = Z

        Windows("Kitap.xlsx").Activate
        Set Ws = Wb.Worksheets("Production Source Header")
        Sheets("Production Source Header").Select
        Set MyRange = Worksheets("Production Source Header").Range("B:C")

        SourceID = CVar(Application.WorksheetFunction.VLookup(ProductID, MyRange, 2, False))

        Set FoundCell = ActiveSheet.Range("C:C").Find(What:=SourceID)

        If Not FoundCell Is Nothing Then
            Index = FoundCell.Row
            Location = Cells(Index, 1)
            Product = Cells(Index, 2)
        Else
        End If

        X = I
        I = I + 1

        Windows("Kitap.xlsx").Activate
        Set Ws = Wb.Worksheets("Production Source Item")
        Sheets("Production Source Item").Select
        Set MyRange = Worksheets("Production Source Item").Range("B:B")

        SourceID2 = CVar(Application.WorksheetFunction.VLookup(SourceID, MyRange, 1, False))

        Do While (IsEmpty(SourceID2) = False) And (I - X = 1)

            Set MyRange = Worksheets("Production Source Item").Range("B:B")

            SourceID2 = CVar(Application.WorksheetFunction.VLookup(SourceID, MyRange, 1, False))

            Set FoundCell = ActiveSheet.Range("B:B").Find(What:=SourceID2)

            If Not FoundCell Is Nothing Then
                Index2 = FoundCell.Row
                Item = Cells(Index2, 1)
                Windows("Kitap.xlsx").Activate
                Set Ws = Wb.Worksheets("Production Source Header")
                Sheets("Production Source Header").Select
            Else
            End If

            Y = I
            I = I + 1

            Windows("Kitap.xlsx").Activate
            Set Ws = Wb.Worksheets("Production Source Resource")
            Sheets("Production Source Resource").Select

            Set MyRange = Worksheets("Production Source Resource").Range("B:B")

            SourceID3 = CVar(Application.WorksheetFunction.VLookup(SourceID, MyRange, 1, False))

            Do While IsEmpty(SourceID3) = False And (I - Y = 1)

                Set MyRange = Range("B:B")
                    SourceID3 = CVar(Application.WorksheetFunction.VLookup(SourceID, MyRange, 1, False))
                Set FoundCell = ActiveSheet.Range("B:B").Find(What:=SourceID3)

                If Not FoundCell Is Nothing Then
                    Index3 = FoundCell.Row
                    Resource = Cells(Index3, 1)
                    Windows("Kitap.xlsx").Activate
                    Set Ws = Wb.Worksheets("Production Source Header")
                    Sheets("Production Source Header").Select
                Else
                End If

            I = I + 1

            Loop
        Loop

        fullText = fullText & " Location: " & Location & " // Header: " & Product & " // Item: " & Item & " // Resource: " & Resource
        Z = Z + 1

        ProductID = Item
        Set MyRange = Worksheets("Production Source Header").Range("B:C")

        SourceID = (Application.WorksheetFunction.VLookup(ProductID, MyRange, 2, False))

T_Error:
        If Err.Number = 1004 Then

            On Error Resume Next
            T = 1

        Else
        End If

    Loop

    IbpBomLevel = fullText

Else
    MsgBox ("Bom Missing")
End If

End Function

标签： excelvbauser-defined-functions