@ showing up in name for named range

[GeeWhiz7]

Hi there,
I have a section of a much longer bit of code that selects a range of dates and makes a named range that gets used in a number of places.

Set dateRef = Cells(rCount, 1).Offset(-175).Resize(176, 1)
ThisWorkbook.Names.Add Name:="_Dates", RefersTo:=dateRef

Range("a2:a" & rCount + 1).NumberFormat = "yyyy-mm-dd;@"

For a while this code worked just fine, but I moved the workbook to another computer and now when it runs, the named range that gets added/created is not _Dates, but @_Dates and it does not work correctly.
If I hand delete the @ then it works. I'm probably supposed to know this, but why does the @ show up and how do I get rid of it?

 

[jasonb75]

Looking at your screen capture, it appears that it not the way that the named range is created causing the problem, but rather the way that the formula referring to the named range is being entered into the worksheet.

Have a look at this link, see if it helps.

Implicit intersection operator: @ - Microsoft Support

Learn about the implicit intersection operator (@), which returns a single value using logic known as implicit intersection. The implicit intersection operator (@) may return a value, single cell range, or an error. The implicit intersection operator (@) is in a class of functions called dynamic...

support.microsoft.com

 

[cmowla]

I've had this problem too (but not with named ranges), but I have no idea why it happens.

Assuming that no cells VALUES contain "@", then you can call this very fast sub at the end of the sub which creates named ranges for that sheet. (Or just call it by itself if this fix needs to be done once.)

Sub Test__Remove_AtSymbols_In_Sheet()
Call Remove_AtSymbols_In_Sheet(ActiveSheet.Name)
End Sub
Sub Remove_AtSymbols_In_Sheet(sheetName As String)
With Sheets(sheetName).Cells
    .Replace What:="@", Replacement:="", LookAt:=xlPart, FormulaVersion:=xlReplaceFormula2
End With
End Sub

If you DO have "@" (somewhere) in some of the cell VALUES, then you will probably need to a Range.Find search and replace sub, should you want the "fix" to be fast (for very large data).

 

[GeeWhiz7]

Looking at your screen capture, it appears that it not the way that the named range is created causing the problem, but rather the way that the formula referring to the named range is being entered into the worksheet.

Have a look at this link, see if it helps.

Implicit intersection operator: @ - Microsoft Support

Learn about the implicit intersection operator (@), which returns a single value using logic known as implicit intersection. The implicit intersection operator (@) may return a value, single cell range, or an error. The implicit intersection operator (@) is in a class of functions called dynamic...

support.microsoft.com

Now I have a better understanding of why the @ exists. Thanks for sharing the article.

So far simply deleting the @ in the formula cell works even when I update input data and as long as I don’t run the bit of code to delete the old and make a new named range.

Cmowla’s code is a good way to clear out other cases I’ve seen pop up when I’ve used .value instead of .value2 in vba to assign a formula to a cell. Thanks to you both for the suggestion.

 

[jasonb75]

How are you entering the formulas into the cells initially?

I don't have access to an older version of excel for testing, but I have it in mind that committing the formula to a single cell as a legacy array (Ctrl Shift Enter), then copying that cell to the rest of the range requiring the formula prevents it from happening. Please bear in mind that it is a couple of years since I last encountered this problem and my memory on the exact cause and remedy is more than a little fuzzy.

Note that committing as a legacy array will mean that the formula range will not expand and contract automatically in Office 365 as a dynamic array would.

 

[cmowla]

@jasonb75 ,
I know you weren't speaking to me, but I went back to the project (on this site) that this happened to me, and I found the root of the problem. It agrees with that article, as I used a user-defined language. Below is a very simple example that causes the @ to appear in the formula (if you don't use .Formula2 instead of .Formula):

Sub Test_Sub()
Range("A1").Formula = "=IF(RowHeight(B1)>=10,0,1)"
End Sub
Function RowHeight(cell As Range) As Double
RowHeight = cell.RowHeight
End Function

 

[jasonb75]

Thanks @cmowla I can see where you're coming from with that, I've had a quick glance over some info explaining the difference between .formula and .formula2, while I haven't read it in detail or tested anything (so could be missing some crucial details) it looks to me as if .formula2 is the vba equivalent to the process that I suggested for manual formula entry.

It may be that they're happy with doing a quick fix as and when needed but the main point behind my suggestion was an effort to find a way that the OP can set the formulas in place correctly so that they don't need to be 'fixed' after running the code that changes the named range definition. They did mention .value2 earlier so it may simply be that they meant .formula2 and my follow up is moot anyway if the issue is already resolved.