none
Using VBA to Name Ranges Permenantly per Session RRS feed

  • Question

  • I have a macro, let's call it macro1 (what it does is not important here), and I want to insert the some new code into the macro that gives a name to every other column.  For example,

        ActiveWorkbook.names.Add Name:="FirstCol", RefersTo:="=$B:$B"
        ActiveWorkbook.names.Add Name:="SecondCol", RefersTo:="=$D:$D"

    etc. up to 78 columns.

    The purpose of naming the columns in my scenario is so that a user can insert new columns into the worksheet and macro1 will still reference whatever data was in column B prior to inserting the column(s). 

    The problem is the code above is written into macro1 which means that when the user runs the macro, the columns are named and the rest of the macro is then performed.  When the user inserts rows (let's say to the left of column B) and runs the macro again, column B, which is now completely different than before, becomes "FirstCol".  Therefore, I need a way for each column(s) to maintain its original name (the name given during the first time macro1 is run).  Each subsequent run of macro1 must not rename the columns.

    Sorry for the long-winded question.....hope this makes sense.  Thank you!

    Monday, June 11, 2012 10:34 PM

Answers

  • I suggest the statement which creates names,put them in a conditional statement.Wrap your statement as below

    It first checks a name (The Name Object of Excel) if it exists then it does not execute any statement (The FirstCol etc).If does not exist then it executes your statement and creates an odd name which no user will use.(Reasonably).When this macro will run 2nd time in same wb the macro will find the name and ignore your statement.

    On Error Resume Next
    If ActiveWorkbook.Names("z1x8x7z6z5z") Is Nothing Then
        'Your statements
        '----------------
        
        ActiveWorkbook.Names.Add Name:="z1x8x7z6z5z", _
            RefersTo:="Vital", Visible:=False
    End If
    On Error GoTo 0

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    • Marked as answer by Looshsmoot Friday, June 15, 2012 6:59 PM
    Wednesday, June 13, 2012 7:15 AM
    Answerer

All replies

  • The names should be assigned from a diff macro which ideally should run once.

    Tuesday, June 12, 2012 6:05 AM
    Answerer
  • Or, name your columns once from outside the macro while creating the workbook / template.

    The Data Specialist (Blog)

    Tuesday, June 12, 2012 10:54 AM
  • The macro needs to be simple and easy to use, since others within the company use it besides me. I don't want to add a second macro into the mix. It all needs to be in a single click. First I thought to use an event, such as when the workbook is opened then name the columns, but I would need it to be only for workbooks with "crosstabs" somewhere in the filename. Any ideas about how to do this?

    Don't be afraid to provide a detailed answer, as I am a novice in VBA.

    Thanks!


    • Edited by Looshsmoot Tuesday, June 12, 2012 3:48 PM
    Tuesday, June 12, 2012 3:48 PM
  • I suggest the statement which creates names,put them in a conditional statement.Wrap your statement as below

    It first checks a name (The Name Object of Excel) if it exists then it does not execute any statement (The FirstCol etc).If does not exist then it executes your statement and creates an odd name which no user will use.(Reasonably).When this macro will run 2nd time in same wb the macro will find the name and ignore your statement.

    On Error Resume Next
    If ActiveWorkbook.Names("z1x8x7z6z5z") Is Nothing Then
        'Your statements
        '----------------
        
        ActiveWorkbook.Names.Add Name:="z1x8x7z6z5z", _
            RefersTo:="Vital", Visible:=False
    End If
    On Error GoTo 0

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    • Marked as answer by Looshsmoot Friday, June 15, 2012 6:59 PM
    Wednesday, June 13, 2012 7:15 AM
    Answerer
  • Thanks, this is helpful.  I think it will be the answer, but I need to test it out first.  Can you tell me, what is "Vital"?

    I'm having trouble running this code:

        ActiveWorkbook.names.Add Name:="5zQ4z9z77zPi4z2", RefersTo:="=Vital", Visible=False

    I get a message "Compile error: Eexpected: named parameter", and it highlights the "Visible" field.  Did I write the code wrong?




    • Edited by Looshsmoot Friday, June 15, 2012 6:34 PM
    Friday, June 15, 2012 6:17 PM
  • Thanks, this is helpful.  I think it will be the answer, but I need to test it out first.  Can you tell me, what is "Vital"?

    I'm having trouble running this code:

        ActiveWorkbook.names.Add Name:="5zQ4z9z77zPi4z2", RefersTo:="=Vital", Visible=False

    I get a message "Compile error: Eexpected: named parameter", and it highlights the "Visible" field.  Did I write the code wrong?




    Nevermind the second question (figured it out on my own), but I'd still like to know what "Vital" is.
    Friday, June 15, 2012 7:00 PM
  • Hmmm.It is not so Vital.It is just there.

    Actually Name should point to something.As your FirstCol refers to Column.I juts used the text vital  for filling the arguments.

    [Except the range addresses name can refer to text,formula etc]

    As we are checking only the existence of 5zQ4z9z77zPi4z2 name ,we dont need to use any property of tis name object.

    Hope it is clear.

    [Although you solved it.NAmed arguments must be asisgned with := not only with =]

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    Saturday, June 16, 2012 6:28 AM
    Answerer