UDF/Custom Function Working 1st Time, but Recalculating 2nd Time and Returning #VALUE... ??

[pawest]

Hello VBA and UDF world,
I have a UDF/Custom Function that works the 1st Time it's ran, but when I try to use the data in a pivot table the function attempts to recalculate and returns #VALUE. I have researched this issue, came to some conclusions, but can't figure it out entirely...

I found out that UDFs sometimes return #VALUE when there is potential confusion between data types. I have strings and integers that are being combined with this function and this was a potential issue. So, I fixed it by assigning these variables as variants. There seems to be other issues going on, too...

Here's what happens: I have a set of data and I'm adding an additional column for my UDF. I set the UDF equal to the applicable cells and it works perfectly. I then try to use this data to calculate multiple pivot tables.
On the first pivot table calculation, it works.
On the second pivot table calculation, the UDF function changes to #VALUES and the UDF module opens amidst the executing pivot table creating code and tries rerunning/recalculating.

Any thoughts on what's going wrong here?

Here's my UDF:

Function FTSTING(t As Variant, cDate As Variant) As String

Dim Month As Variant

'cDate format = YYYYMM

    t= Trim(t)
    
    Month = Right(cDate, 2)
    
        Select Case Month
        
            Case "01"
                Month = "G"
            Case "02"
                Month = "S"
            Case "03"
                Month = "U"
            Case "04"
                Month = "K"
            Case "05"
                Month = "K"
            Case "06"
                Month = "S"
            Case "07"
                Month = "H"
            Case "08"
                Month = "I"
            Case "09"
                Month = "L"
            Case "10"
                Month = "Q"
            Case "11"
                Month = "C"
            Case "12"
                Month = "R"
        
        End Select
    
    'Year
    cDate = Mid(cDate, 4, 1)
    
    'Building the fuction
    FTSTRING= t& Month & cDate

End Function

 

[RoryA]

What is the code that creates the pivot tables?

 

[pawest]

Hi Rory,
I run this code once, and then I'll call it again under a given circumstance (first deleting the old pivot table). On the second time being run, the UDF issue previously mentioned occurs.

Sub CreatePivTab()


Dim ptCache As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Dim wsTrades As Worksheet
Set wsTrades = Worksheets("Activity")


' Create the cache
Set ptCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Sheets("TRANS").Range("A1").CurrentRegion)


' Create the pivot table
Set pt = ActiveSheet.PivotTables.Add( _
PivotCache:=ptCache, _
TableDestination:=Range("T11"))

' Specify the fields
With pt
    
    .PivotFields("Account").Orientation = xlRowField
    .PivotFields("FTSTING").Orientation = xlRowField
    .PivotFields("Qty").Orientation = xlDataField
    ' no field captions
    .DisplayFieldCaptions = True
End With

 

[pawest]

I've found a work around to this option. Thanks for your consideration at helping.