title: How do I assign an Excel VBA variable to a Defined Name
body: <p>How do I return a value from a VBA Function variable to a Defined Name in Excel 2010?</p>
  
  <p>In the example below, I want <code>i</code> to be returned to Excel as a Defined Name <code>Value_Count</code> that can be reused in other formulas, such that if <code>MsgBox</code> shows 7, then typing <code>=Value_Count</code> in a cell would also return 7.</p>
  
  <p>Everything else below is about what I've tried, and why I'd like to do it. If it's inadvisable, I'd be happy to know why, and if there's a better method.</p>
  
  <pre><code>Function process_control_F(raw_data As Variant)
  Dim i As Integer
      i = 0
  For Each cell In raw_data
      i = i + 1
      Next cell
  MsgBox i
  End Function
  </code></pre>
  
  <p>My goal is to have the value returned by the MsgBox be returned instead to a Defined Name that can be reused in other forumulas.  However, I cannot get the value to show.  I have tried a variety of forms (too numerous to recall, let alone type here) similar to </p>
  
  <pre><code>Names.Add Name:="Value_Count", RefersTo:=i
  </code></pre>
  
  <p>I am trying to accomplish this without returning a ton of extra info to cells, just to recall it, hence the desire to return straight to a Defined Name.</p>
  
  <p>I'm using a Function rather than Sub to streamline my use, but if that's the problem, I can definitely change types.</p>
  
  <p>I am creating a version of a Statistical Control Chart. My desired end result is to capture a data range (generally about 336 values) and apply a series of control rules to them (via VBA), then return any values that fall outside of the control parameters to Defined Names that can then be charted or otherwise manipulated.  </p>
  
  <p>I've seen (and have versions of) spreadsheets that accomplish this with digital acres of helper columns leading to a chart and summary statistics.  I'm trying to accomplish it mostly in the background of VBA, to be called via Defined Names to Charts — I just can't get the values from VBA to the Charts.</p>
  
  <p>The interest in using a Function rather than a Sub was to streamline access to it.  I'd rather not design a user interface (or use one), if I can just keystroke the function into a cell and access the results directly.  However, as pointed out by Jean-François Corbett, this is quickly turning into a circuitous route to my goal.  However, I still think it is worthwhile, because in the long-term I have a lot of iterations of this analysis to perform, so some setup time is worth it for future time savings.</p>