(Solved) – Public Function not returning correct string


I have a Public Function that takes in values from a worksheet and then converts them to short form and returns a concatenation of the values in the way of a String. Code below:

Public Function Campaign_Name(advertiser As String) As String

    'volatile off for UDF!
    Application.Volatile False
    Application.DisplayAlerts = False

    If advertiser = "" Then Exit Function

    Dim arr_map() As Variant, arr_plan() As Variant, arr_client() As Variant
    Dim ws As Worksheet
    Dim x_map As Long, y_map As Long
    Dim i_client As Long
    Dim temp_name As String: temp_name = ""

    Set ws = Application.caller.Parent
    Debug.Print ws.Name
    arr_map = wsCampaignNameMapping.Range("tbCampaignNameMapping[#All]").Value
    arr_plan = ws.Range(ws.ListObjects(2).Name & "[#All]").Value

    With ThisWorkbook.Sheets("NC - " & advertiser).Cells.Find(What:="Campaign Name", LookAt:=xlWhole)
        arr_client = Range(.Offset(0, 1), .End(xlToRight)).Value
    End With

    'loop first to find client column
    For x_map = LBound(arr_map, 2) To UBound(arr_map, 2)
        If arr_map(1, x_map) = advertiser Then
            'advertiser column found
            'loop through client campaign name order
            For i_client = LBound(arr_client, 2) To UBound(arr_client, 2)
                'now loop through y
                'mapping table y dimension should equal plan table y dimension
                For y_map = LBound(arr_map, 1)   1 To UBound(arr_map, 1)
                    'if value is non-blank in map table then return the value in col 2 of plan table
                    'make sure using the relevant client order
                    If arr_map(y_map, x_map) = arr_client(1, i_client) Then
                        'use map table item for reference for lookup table
                        'use plan table item for lookup value
                        If arr_map(y_map, 1) = "Campaign Description" Then
                            temp_name = temp_name & arr_plan(y_map, 2) & "_"
                        ElseIf arr_map(y_map, 1) = "Start Date" Then
                            temp_name = temp_name & Format(arr_plan(y_map, 2), arr_map(y_map, x_map)) & "_"
                            On Error Resume Next
                            temp_name = temp_name & Application.WorksheetFunction.VLookup(arr_plan(y_map, 2), wsCampaignNameMapping.Range("tb" & advertiser & arr_map(y_map, x_map)), 2, False) & "_"
                        End If
                        Debug.Print temp_name
                        Exit For

                    End If
                Next y_map
            Next i_client

            Exit For

        End If

        'return error message if client not found
        If x_map = UBound(arr_map, 2) Then
            Campaign_Name = "Client not found in data base. Please contact developer to get it added"
            Exit Function
        End If

    Next x_map

    Campaign_Name = Left(temp_name, Len(temp_name) - 1)

    Application.EnableEvents = True
    Application.DisplayAlerts = True

End Function

Now, when I run this in the VBE it returns the correct String, but when it is called from a cell on the sheet, it returns 0. I have checked the cell type and ensured that it is type General.

Example return string: UK_NES_BEV_AER_BLA_032020

Is there any reason why this Public Function would return 0 from a cell but return correctly in the VBE?

Many thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *