Set value of named range with macro

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Set value of named range with macro


    Is it possible to set value of named range with macro?

    My logic:
    I can read cell value: activesheet()->range('')->value;
    I can write to cell: activesheet()->range('')->value = ;

    I can read named range value: activeworkbook()->names->item('')->value;
    Why I can't set the value of named range with: activeworkbook()->names->item('')->value = ;

    Any ideas, should it work or no?
  • You could use a helper function:

    Source Code

    1. function setRange( $namedCell, $newValue ) {
    2. // get cell to which named range refers
    3. $currentrange = activeworkbook()->names->item( $namedCell )->refers_to;
    4. // convert the retrieved cell refers
    5. $absolutecell = explode( "!", $currentrange );
    6. $currentcell = str_replace( "$", "", $absolutecell[1] );
    7. // set the source value as value for the retrieved cell reference
    8. activesheet()->range( $currentcell )->value = $newValue;
    9. }
    Display All

    And the call would be something like:

    Source Code

    1. setRange( 'Named Range', "New Value");
    The new value can be numeric or anthing else.