How to rename range using VBA in excel 2007

Question

I've 8+ yes professional programming experience in PHP and Java but none in VBA. Learning it right now.

I'm trying to make a Home budget sheet (Just for VBA learning purpose). For that, I'd done following

* In new excel (2007) file, rename sheet 1 as 'Forms' And Sheet2 as 'CatAcc'
* In sheet 'CatAcc', I'm using (planning) column A for categories and B for Account
* Row 1 is heading (A1 = "Categories", B1="Account"
* Using forms sheet (cell C2) & VBA button, I want to add a new category,
  sort it alphabetically and then rename range to add newly added row.

I wrote following code for that (Recorded testmacro to check how to name a range)

Sub AddCat_Click()
    'Copy data as last row
    Worksheets("CatAcc").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("Forms").Cells(2, "C").Value

    'Find total Rows in categories
    Dim totalRows
    totalRows = Worksheets("CatAcc").Range("A2").End(xlDown).Row

    'Define Range
    Dim rng
    rng = "A2:A" & totalRows
    'MsgBox rng

    'Select the range - Getting error in following line.
    Worksheets("CatAcc").Range(rng).Select

    'Name the range
    ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:="=CatAcc!R2C1:R3C1"

    'Sort range alphabetically

    'Apply range as drop-down options
End Sub
Sub testmacro()
    Range("A2:A3").Select
    ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:= _
        "=CatAcc!R2C1:R3C1"
End Sub

While selecting the range, I got following error

Run-time error '1004'
Select method of Range class failed

I'm unable to understand what that error mean and why I'm getting that by just adding worksheet name.

Again, What does following line mean? What is happening there? I could not understand meaning of R2C1:R3C2. This values came from recorded macro when I named A2:A3 cells as 'categories'

ActiveWorkbook.Names.Add Name:="categories", RefersToR1C1:="=CatAcc!R2C1:R3C1"

You should declare your variables with specific types, e.g., `Dim rng as String` and `Dim totalRows as Long`, etc. Although this does not address your problem, it would be a good habit to start as you begin to learn VBA. — David Zemens, Apr 16 '13 at 21:20
The `R2C1:R3C1` means Row2/Column1 to Row3/Column2, or in other words, the same as "A2:A3", just a different way of writing it. (I personally prefer using the A1 reference style to the R1C1) — David Zemens, Apr 16 '13 at 21:23
Thanks for pointing it out @DavidZemens I agree any bad habit should be stopped as early as possible. I'll keep your point in mind in future. — Kapil Sharma, Apr 17 '13 at 08:34

score 1 · Accepted Answer · answered Apr 16 '13 at 20:29

1

You do not need to select the range Range("A2:A3").Select. The "=CatAcc!R2C1:R3C1" in the next line is automatically taking care of it.

Explanation

"=CatAcc!R2C1:R3C1"

CatAcc is the Sheet where the range is
R2C1 means Row 2 Col 1 which is nothing but A2
Similarly R3C1 is Row 3 Col 1 which is nothing but A3

So the above can also be written as

ActiveWorkbook.Names.Add NAME:="categories", RefersTo:="=CatAcc!$A$2:$A$3"

answered Apr 16 '13 at 20:29

Siddharth Rout

147,039
17
206
250

Thanks for quick reply Sidd. My bad I could not understand it as row/col. New thing for me. – Kapil Sharma Apr 16 '13 at 20:32
You are welcome :). You might also want to check out R1C1 notation in MSDN? – Siddharth Rout Apr 16 '13 at 20:32
1

See R1C1 Reference Style here http://msdn.microsoft.com/en-us/library/office/ee264226%28v=office.12%29.aspx (Midway down the page) – Siddharth Rout Apr 16 '13 at 20:37
Thanks for the link. I'm going through it, ebooks, internet tutorials etc. Need to learn a lot for VBA basics. CAn you suggest best way to learn VBA? – Kapil Sharma Apr 16 '13 at 20:40
See this post by @brettdj: http://stackoverflow.com/questions/7523102/what-is-the-best-way-to-master-vba-macros-for-ms-office-applications/7524173#7524173 – Siddharth Rout Apr 16 '13 at 20:41

How to rename range using VBA in excel 2007

1 Answers1