title: How to use relative names in Excel VBA
body: <p>Many "advanced" (aka: VBA) excel tutorials on the web or even excel's vba help encurage us to use the</p>
  
  <pre><code>Range("B2:B10")
  </code></pre>
  
  <p>method (to be precise: object) for selecting cells or getting values. In the same place they often add it's totally ok to use predefined names as well:</p>
  
  <pre><code>Range("valuesabove")
  </code></pre>
  
  <p>On the other hand I fell in love with the incredible power of relatively defined cell names. They make it so much easier to write and handle big composite formulas, and basically to refer to nearly anything.</p>
  
  <p>However, <strong>relative names don't work in the <em>Range("valuesabove")</em> method the way we are used to it.</strong></p>
  
  <p>Usually (when used on the worksheet) relative names are relative to the currently selected cell or to the cell in which they are used.</p>
  
  <p>In VBA's Range() object this is not true. <em>Range</em> is relative to a WorkSheet object, by default to the ActiveSheet. But ActiveSheet is represenetd by its leftupper cell, A1. And this is what Range turns out to be relative to. And this is why absolute names <em>($C$23)</em> do work with it, and relative ones <em>("one column to the left, two rows up")</em> don't.</p>
  
  <p>So my question is:
  How can I harness the power of relative names in VBA then?</p>
  
  <hr>
  
  <h2>EDIT:</h2>
  
  <p>Realising that my question was rather unclear (thx's go to you guys commenting tirelessly) let me try to put it in a specific form and clarify terms:</p>
  
  <p>IMHO on an excel worksheet it is <em>very comfortable</em> to use names in order to refer to cells or define calculated values by functions based on cell values. </p>
  
  <p><a href="https://support.office.microsoft.com/en-US/article/Next-50598462-63f0-476d-a523-f664c64d5dbb" rel="nofollow noreferrer">In excel <strong>a reference to a cell can be either relative, absolute, or mixed</strong>.</a> This is true also when creating names. Thus we can speak about absolute, relative or mixed names (in terms of referring of course).</p>
  
  <p>Here an <strong>absolute name</strong> is used a couple times (created using excel's Trace Dependents function):
  <img src="https://i.stack.imgur.com/d6zpx.png" alt="absolute name"></p>
  
  <p>Name "name" = <code>$D$2</code></p>
  
  <p>A <strong>relative name</strong> is used a couple times here:
  <img src="https://i.stack.imgur.com/LFmY1.png" alt="enter image description here"></p>
  
  <p>Name "upright24" <em>while, e.g. cell A7 is selected</em> = <code>C3</code> (without $ signs!). But this changes constantly according to the selected cell or region. You can check it in the name manager! (Ctrl+F3)</p>
  
  <p>And this is what we can consider as a <strong>mixed name</strong>:
  <img src="https://i.stack.imgur.com/P8Q4v.png" alt="mixed name"></p>
  
  <p>Name "rel_serialnumber" <em>while, e.g. cell C6 is selected</em> = <code>$B6</code>. The row of which (<em>6</em>) changes constantly according to the selected cell or region.</p>
  
  <p>The creation of a relative or a mixed name is explicitly based on the active cell at the moment of creating the name. The creation of an absolute name naturally doesn't rely on the cursor position.</p>
  
  <p>Note, that</p>
  
  <ul>
  <li><p>absolute names mean a dinamic offset from the referenced cell, which is one and only</p></li>
  <li><p>relative names mean a static offset from the referenced cell, which thus changes always corresponding to the place where the name is used</p></li>
  <li><p>mixed names mean a mixed (or half-dynamic) offset from the referenced cell, the row or column of which thus changes always corresponding to the place where the name is used while the other remains always the same (the offset in one or the other direction remains zero).</p></li>
  </ul>
  
  <hr>
  
  <p>Okay, now here is the thing. I have a database-like excel sheet where I handle the rows like records and the columns as fields for properties. The user uses this thing as follows: he "selects a record" by placing the cursor in any cell of the row of the desired record. Then he presses a big <a href="http://cdn.extendoffice.com/images/stories/doc-excel/change-button-color/doc-change-button-color1.png" rel="nofollow noreferrer">command button</a> which starts my VBA macro. This intends to open a prepared skeleton file and fill some specific cells in it (which are btw defined by absolute names) with some values (which are defined by mixed names) from the selected record.</p>
  
  <p>Since <code>Range("name")</code> is considered ok to use in VBA (see above) I thought <code>Range("relativename")</code> or <code>Range("mixedname")</code> will work just as fine while automatically relying on the active cell.<br>
  <strong>I couldn't be worse.</strong><br>
  <strong>Only <code>Range("absolutename")</code> works in the way one would expect! Explanation see above.</strong></p>
  
  <p>So I'm after a function / method / object that is possibly as comfortable to use with a <code>"relativename"</code> or a <code>"mixedname"</code> as <code>Range("absolutename")</code> is.</p>