Forum Discussion
valentingiaufer
Oct 18, 2023Copper Contributor
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
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()))
- PeterBartholomew1Silver Contributor
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()))
- valentingiauferCopper Contributor
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 ?
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()))
- LorenzoSilver Contributor
As workaround do everything in SheetNameFunc:
Function SheetNameFunc() SheetNameFunc = ActiveSheet.Name & "-" & Year(Date) & "-" & Month(Date) End Function
- valentingiauferCopper ContributorThanks 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
What does SheetNameFunc return?
- valentingiauferCopper ContributorThe name of the Activesheet where the sub is called
Thanks. I'm afraid I don't know what causes the problem; I hope that someone else will have a helpful suggestion for you.