none
Names - Access and update RRS feed

  • Question

  • Hi

    I have a name defined in sheet 2 at cell D10 called TESTNAME with an initial value of 1.2

    I have code associated with the Worksheet Change event in sheet1.

    If , in the Sheet1 code, I want to 'read'  the value in TESTNAME it seems I can just do Evaluate("TESTNAME") and this will return 1.2

    If I now want to update the value to say 1.3 I can only get this to work if I do:

    Sheet2.Range("TESTNAME").Value = 1.3

    I have to explicitly name the sheet and range and I am not sure there is must advantage in me having a name. I might as well

    say Sheet2.Range("D10").Value = 1.3

    Is this just how VBA works or is there a slicker way to use NAMES in VBA.

    thank you.

    Peter

      

    Monday, November 21, 2016 11:50 AM

Answers

  • Hi Hans

    I have checked my scope and the name is declared at the workbook level - but it still only works if I specify the sheet name.

    Not to worry , not a big issue.

    thanks

    Peter

    • Marked as answer by py1 Monday, November 21, 2016 4:14 PM
    Monday, November 21, 2016 4:14 PM
  • The line

    ActiveWorkbook.Names("TESTNAME").RefersToRange.Value = 1.3

    should work without specifying the sheet name if the name has workbook scope...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Tuesday, November 22, 2016 8:58 AM
    Monday, November 21, 2016 8:53 PM

All replies

  • If TESTNAME has been defined as a workbook-level name, you should be able to use

    Range("TESTNAME").Value = 1.3

    as long as the workbook in which the name has been created is the active workbook. If it is a worksheet-level name, you must specify the sheet.

    You can check the scope in the Formulas > Name Manager dialog:

    Another way to do this is

    ActiveWorkbook.Names("TESTNAME").RefersToRange.Value = 1.3


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 21, 2016 3:37 PM
  • Hi Hans

    I have checked my scope and the name is declared at the workbook level - but it still only works if I specify the sheet name.

    Not to worry , not a big issue.

    thanks

    Peter

    • Marked as answer by py1 Monday, November 21, 2016 4:14 PM
    Monday, November 21, 2016 4:14 PM
  • The line

    ActiveWorkbook.Names("TESTNAME").RefersToRange.Value = 1.3

    should work without specifying the sheet name if the name has workbook scope...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Tuesday, November 22, 2016 8:58 AM
    Monday, November 21, 2016 8:53 PM