64 Bit Array Formula

[somesoldiers]

Hi, I have an issue where array formulae in a wb are being opened by someone in 2016 but instead of the {} the formula are preceded by _xlfn.SINGLE and the cell reads is just #NAME?

I was thinking i could write an on open event to remove the _xlfn.SINGLE and force the formula to array where the end user still has excel 2016.
The problem is there will be various formula not known at the start

this works fine for the formula < 255 characters

I am running into trouble at .FormulaArray = Formula1 either
i include the = at the start and it says unable to set the formula array property of the range class
or i leave the equals out and I get the correct formula string in the cell but its just a string not formula which i have to manually change thereby defeating the purpose

To get around the 255 character limit I have done the below but am guessing I am misunderstanding the replace part after the formula array and each individual part of needs to be a working formula in itself rather than stitching them all together as i have done?
This was my interpretation of Roys response here long array vba issue
but as i said the make up of the formula are not know at the start so I cant really do it that way

I have set the Range as H65 for testing but it could be anywhere on the workbook

Any suggestions please?
Thanks
Noel

Public newformula As String

Sub UpdateFormula()

Sheets("Portfolio").Select

For Each sh In ThisWorkbook.Sheets
shName = ActiveSheet.Name

    Set rgFound = Cells.Find(What:="_xlfn.Single", After:=ActiveCell, LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
      
        If rgFound Is Nothing Then
        Else
            curFormula = Range(rgFound.Address).Formula
            If Left(curFormula, 13) = "=_xlfn.SINGLE" Then
                newformula = WorksheetFunction.Substitute(curFormula, "=_xlfn.SINGLE", "")
              
                If Len(newformula) > 255 Then
                    'MsgBox "Too Long " & Len(newformula) & " characters"
                    Call LongFormulaStraight
                    Else
                    Range(rgFound.Address).FormulaArray = "=" & newformula
                End If
            End If
        End If
Next

End Sub


Sub LongFormulaStraight()

    'newformula = "=" & newformula
  
    Formula1 = Trim(Mid(newformula, 1, 200)) & "FX1FX2FX3FX4"
    Formula2 = Trim(Mid(newformula, 201, 200))
    Formula3 = Trim(Mid(newformula, 401, 200))
    Formula4 = Trim(Mid(newformula, 601, 200))
    Formula15 = Trim(Mid(newformula, 801, 200))
  
    Application.DisplayAlerts = False

    With ActiveWorkbook.Sheets("Portfolio").Range("H65")
         .FormulaArray = Formula1
         .Replace "FX1", Formula2, lookat:=xlPart
         .Replace "FX2", Formula3, lookat:=xlPart
         .Replace "FX3", Formula4, lookat:=xlPart
         .Replace "FX4", Formula5, lookat:=xlPart
      End With
    
    Application.DisplayAlerts = True
 
End Sub

 

[RoryA]

each individual part of needs to be a working formula in itself rather than stitching them all together as i have done?

Yes, the formula needs to make sense as initially entered. (and it's Rory, not Roy )

Are these formulas actually array-entered in the workbook to start with?

 

[somesoldiers]

oops apologies Rory

yes below is an example of how it reads currently in 2016 (not my design...)

=_xlfn.SINGLE(IF($C65="","",IF($C65="Duration",SUMIFS(Portfolio_Data!Weighted_Duration,Portfolio_Data!Adjusted_L_S,H$63,Portfolio_Data!Asset_Class,$J65,Portfolio_Data!Sub_Asset_Class,IF($C64=$J64,"[NULL]",$C64)),
IF($C65="WAT",SUMIFS(Portfolio_Data!WAT,Portfolio_Data!Adjusted_L_S,H$63,Portfolio_Data!Asset_Class,$J65,Portfolio_Data!Sub_Asset_Class,IF($C64=$J64,"[NULL]",$C64)),
IF($C65="10-year eq",SUMIFS(Portfolio_Data!_10_yr_Equiv,Portfolio_Data!Adjusted_L_S,H$63,Portfolio_Data!Asset_Class,$J65,Portfolio_Data!Sub_Asset_Class,IF($C64=$J64,"[NULL]",$C64)),
SUMIFS(Portfolio_Data!Notional_Value,Portfolio_Data!Adjusted_L_S,H$63,Portfolio_Data!Asset_Class,$J65,Portfolio_Data!Sub_Asset_Class,IF($C65=$J65,"[NULL]",$C65)))))))

 

[RoryA]

I would not expect to see that unless the formula was entered using @ in the later version. If it had been actually array-entered, it should remain the same.

 

[somesoldiers]

it seems i may have completely misunderstood and misstated the problem when it was put to me and just wasted a day with the array stuff.
it was actually just the @ in 2021 that were causing the _xlfn.single in 2016 so my first idea of replacing this or even a simple replace all on the workbook would seem to work in 2016

thanks for pointing me in the right direction or rather for pointing out that my question made no sense

 

[RoryA]

Just glad you have a solution!