UDF returns an array UNLESS it is put into the cell using code (.Formula)

[OaklandJim]

In a summary sheet I use UDFs to get specific data from two data sheets. Need UDFs because sheets accessed may change so I need to use data sheets' Code Name to determine which sheet a formula refers to.

Sometimes my UDFs just do not recalculate. OK, the workbook has lots of code, names, etc. I guess Excel gets bogged down sometimes.

So, I wrote code that iterates offending cells to "refresh" formulas (i.e. rewrite them). That works, sort of.

Except for one of the function calls there is one return value. When I refresh those formulas all works just fine, the correct value is calculated.

The other formula is supposed to return an array of 1 x 24 cells. When I enter the UDF into a worksheet cell "by hand" it works (i.e., spills as expected). BUT if I put the same formula into the same cell using code Excel adds the @ char and refuses to recognize the array, it just shows one -- the leftmost -- value. I even tried to calculate the offending range in code. Nope.

If I remove the @ char from the formula in the cell "by hand" the result spills as expected.

Any suggestions?

This won't help, I suspect, but here is the simple UDF involved.

Function GetValPortfolio(piIndex As Integer, psName As String) As Variant
    
    Dim sSheet As String
    
    Dim wsSource As Worksheet
    
    If piIndex > 2 Then Exit Function
    
    If piIndex = 1 Then Set wsSource = [Portfolio1]
    If piIndex = 2 Then Set wsSource = [Portfolio2]
    
    GetValPortfolio = ""
    
    On Error Resume Next
    GetValPortfolio = wsSource.Range(psName).Value
    On Error GoTo 0

End Function

 

[Fluff]

What is the code that inserts the formula into the cell?

 

[OaklandJim]

'       --- Portfolio Allocations Target ---
        sRangeName = "Header_" & sPortfolio & "Target"
        sFormula = "=GetValPortfolio(" & iPortfolioNum & "," _
                    & """" & "Allocations_Target" & """" & ")"
        With .Range(sRangeName).Offset(0, 1)
            .Formula = sFormula
            .Resize(1, 24).Calculate
        End With

 

[Fluff]

Thanks for that, just change .Formula to .Formula2& you should be fine.

 

[OaklandJim]

Thank you Fluff! I appreciate the assistance a lot. I was not aware of .Formula2, now I am.

 

[Fluff]

You're welcome & thanks for the feedback.
Formula2 (along with Formula2R1C1 etc) only appeared with the advent of the dynamic array functions, so hasn't been around long.