title: Excel VBA - UDF returns 0 or empty or #value
body: <p>I am creating my customized function. I wrote the code and tested it as “sub and it worked well. Then i converted it into a function to be able to use it in general. Things that i changed are; adding a function declaration, taking input from excel cell and specifying function output. All others remained same.</p>
  
  <p>My function only has one input which is a selected cell from an excel sheet. And i expect that function returns one output. However, it returns 0.
  •   function declaration. "Function IbpBomLevel(ByVal Target As Range) As Variant
  •   input of function as selected cell. "ProductID = Target
  •   output of function. "IbpBomLevel = fullText</p>
  
  <p>I used option explicit to avoid non-exist functionalities. Also, I am sure about the input, function really takes the selected cell as input. But the problem is that in each loop “ProductID must be changed. Hovewer, when i declared that "IbpBomLevel (output of the function) = ProductID and saw that ProductID is the first parameter that user selected from a cell. It means loop not works. When i test it as “sub, i got the result that i want. I am not sure what the problem is.</p>
  
  <pre><code>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 &amp; " Location: " &amp; Location &amp; " // Header: " &amp; Product &amp; " // Item: " &amp; Item &amp; " // Resource: " &amp; 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
  </code></pre>