CODEWITHSUNDEEP
excelvba
aoswald
aoswald

Reputation: 75

Naming a cell using VBA in excel

I have this code:

ActiveWorkbook.Names.Add Name:="BuBatch", RefersToR1C1:= _
    "='Business Units'" & ActiveCell.Address

I am using "ActiveCell.Address because the cell changes. But I get an error saying there is an error in the formula. I don't know what the error is. Appreciate any help.

Upvotes: 0

Views: 5670

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

Range.Address is a parameterized property. You can peek at its definition using the Object Browser (just hit F2):

VBE Object Browser showing Range.Address definition

As you can see, the definition goes as follows:

Property Address([RowAbsolute], [ColumnAbsolute], [ReferenceStyle As XlReferenceStyle = xlA1], [External], [RelativeTo]) As String

Square brackets denoting optional parameters, when you do this:

foo = ActiveCell.Address

You're actually doing something like this:

foo = ActiveCell.Address( _
        RowAbsolute:=True, _
        ColumnAbsolute:=True, _
        ReferenceStyle:=xlA1, _
        External:=False, _
        RelativeTo:=Nothing)

Note the xlA1 value of the ReferenceStyle argument; the other possibility for it is xlR1C1.

If you want to assign RefersToR1C1, you need to supply a R1C1 address, which involves supplying a non-default value to the ReferenceStyle parameter:

ActiveWorkbook.Names.Add _
    Name:="BuBatch", _
    RefersToR1C1:="='Business Units'!" & ActiveCell.Address(ReferenceStyle:=XlR1C1)

Or, use the default addressing style, and assign to RefersTo instead:

ActiveWorkbook.Names.Add _
    Name:="BuBatch", _
    RefersTo:="='Business Units'!" & ActiveCell.Address

I'd question the use of ActiveCell here: it implies that somewhere else in your code, you're activating that specific cell. Don't. Work with the object model instead:

Dim targetBook As Workbook
Set targetBook = Application.Workbooks.Open(path)

Dim target As Range
Set target = targetBook.Worksheets("Sheet1").Range("A1") 'whatever the actual target cell is

targetBook.Names.Add Name:="BuBatch", RefersTo:="='Business Units'!" & target.Address

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

Assuming that the ActiveCell is on sheet "Business Units" (and that you aren't trying to set a name for the cell on sheet "Business Units" that is in the some location as the current cell on some other sheet), you can simplify your code to:

ActiveCell.Name = "BuBatch"

Fixing the existing formula requires the removal of R1C1 reference type (as mentioned in a comment by Scott Craner) and the insertion of the ! to separate the sheet name from the address, i.e.

ActiveWorkbook.Names.Add Name:="BuBatch", RefersTo:= _
    "='Business Units'!" & ActiveCell.Address

Upvotes: 1

Related Questions