Workbook.Names not working

[maymar]

Hi Community,

something strange here, both of these simplest of commands do not work:


Sub Test()

ThisWorkbook.Names.Count

ActiveWorkbook.Names.Add Name:="Test"

End Sub


The .Count statement results in "improper use of a property" (actual error message is in German, so I don't know the exact english wording of the error message).

The .Names.Add results in "application or object defined error".

Excel 2019, Windows 10, VBA.

What am I doing wrong?


Thanks
Markus

[TMS]

Try:

Dim lNameCount As Long
lNameCount = ThisWorkbook.Names.Count

[TMS]

https://docs.microsoft.com/en-us/off...workbook.names

https://docs.microsoft.com/en-us/off...el.names.count

[protonLeah]

ActiveWorkbook.Names.Add Name:="Test", RefersToR1C1:= "=Sheet1!R1C1"

[maymar]

Silly me, of course the .count has to have put somewhere.
I had .refersto but did not think of R1C1
Thanks, works now.

But the same in vb.net via ExcelDNA does not work:

Dim tempRng As Range = SheetEquipment.Cells(2, 2)

Debug.Print(tempRng.Rows.Count)
Debug.Print(Wb.Names.Count)

Wb.Names.Add(Name:="Test", RefersTo:=tempRng)
'Wb.Names.Add(Name:="Test", RefersToR1C1:=tempRng)
'Wb.Names.Add(Name:="Test", RefersToR1C1:="=Equipment!Z2S2")
'Wb.Names.Add(Name:="Test", RefersToR1C1:="=Equipment!R2C2")

The counts both work (0 for wb.names.count and 1 for tempRng.Rows.Count) but none of the .Add statements.

What am I missing?

[TMS]

Deleted by author.