Forum Discussion

valentingiaufer's avatar
valentingiaufer
Copper Contributor
Oct 18, 2023

Automatically added @ in formulas

Hello 

I want to add a formula in my cell:

 

 

=SheetNameFunc()&""-""&ANNEE(AUJOURDHUI())&""-""&MOIS(AUJOURDHUI())

 

 

But each time the formula is added in the cell, excel automatically add @ in front of ANNEE and MOIS. Without the @, my formula works fine, but whenever they are added, it breaks.

 

Do I need to modify my formula or is there a way to not add the @ automatically ?

Thanks

  • HansVogelaar's avatar
    HansVogelaar
    Oct 19, 2023

    valentingiaufer 

    The Formula property expects a formula in English. You could use

     

    ThisWorkbook.Sheets(SheetName).Range("H1").Formula = "=TEXTJOIN(""-"",,""Export"",SheetNameFunc(),YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

     

    To specify it in your local language, use FormulaLocal:

     

    ThisWorkbook.Sheets(SheetName).Range("H1").FormulaLocal = "=JOINDRE.TEXTE(""-"";;""Export"";SheetNameFunc();ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))

  • valentingiaufer 

    I tried this with an Excel Lambda function rather than a VBA UDF and there was no problem.  Then I realised you are probably using an old Excel version and it is that which causes the potential array result from a UDF to be reduced to a single value with "@"?

     

    One idea that did occur to me though, is have you tried using TEXTJOIN rather than "&"s?

    = JOINDRE.TEXTE("-",,SheetNameFunc(), ANNEE(AUJOURDHUI()), MOIS(AUJOURDHUI()))

     

    • valentingiaufer's avatar
      valentingiaufer
      Copper Contributor

      PeterBartholomew1 

      Thanks for your reply,

      I tried to use the joindre method like this:

      ThisWorkbook.Sheets(SheetName).Range("H1").Formula = "=JOINDRE.TEXTE(""-"";;""Export"";SheetNameFunc();ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))"

      But it raises the 1004 error code : Application-defined or object-defined error

      Do you think about a solution that can resolve my problem ?

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        valentingiaufer 

        The Formula property expects a formula in English. You could use

         

        ThisWorkbook.Sheets(SheetName).Range("H1").Formula = "=TEXTJOIN(""-"",,""Export"",SheetNameFunc(),YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

         

        To specify it in your local language, use FormulaLocal:

         

        ThisWorkbook.Sheets(SheetName).Range("H1").FormulaLocal = "=JOINDRE.TEXTE(""-"";;""Export"";SheetNameFunc();ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    valentingiaufer 

     

    As workaround do everything in SheetNameFunc:

    Function SheetNameFunc()
        SheetNameFunc = ActiveSheet.Name & "-" & Year(Date) & "-" & Month(Date)
    End Function

     

    • valentingiaufer's avatar
      valentingiaufer
      Copper Contributor
      Thanks for your reply but I want it to be dynamic on the sheet so each time I print it, it displays the current date of the print

Resources