title: Excel 2010: VBA convert custom function code to a module with macro shortcut
body: <p><strong>Info:</strong>  Excel 2010<br>
  <strong>Notes:</strong> The code works exactly how I need, I am now wanting to automate it a little</p>
  
  <p>I recently came across this code, it's for a custom function, however I can not create a button for it (like a macro), I would like to convert some of this code, however I don't know what to do or how to go about it.  I want to have a shortcut/button on my ribbon.</p>
  
  <p><a href="https://stackoverflow.com/a/17337453/2337102">https://stackoverflow.com/a/17337453/2337102</a>  </p>
  
  <pre><code>Function listUnique(rng As Range) As Variant
  Dim row As Range
  Dim elements() As String
  Dim elementSize As Integer
  Dim newElement As Boolean
  Dim i As Integer
  Dim distance As Integer
  Dim result As String
  
  elementSize = 0
  newElement = True
  
  For Each row In rng.Rows
      If row.Value &lt;&gt; "" Then
          newElement = True
          For i = 1 To elementSize Step 1
              If elements(i - 1) = row.Value Then
                  newElement = False
              End If
          Next i
          If newElement Then
              elementSize = elementSize + 1
              ReDim Preserve elements(elementSize - 1)
              elements(elementSize - 1) = row.Value
          End If
      End If
  Next
  
  distance = Range(Application.Caller.Address).row - rng.row
  
  If distance &lt; elementSize Then
      result = elements(distance)
      listUnique = result
  Else
      listUnique = ""
  End If
  End Function  
  </code></pre>
  
  <p>Results with the ability to:  </p>
  
  <blockquote>
    <p>Just enter =listUnique(range) to a cell. The only parameter is range
    that is an ordinary Excel range. For example: A$1:A$28 or H$8:H$30.</p>
  </blockquote>
  
  <p><strong>I would like the following:</strong>
  Create a macro button with an a popup <code>Inputbox</code> to ask for a range.</p>
  
  <p><strong>Usage:</strong></p>
  
  <p>1)  I am in the cell where I require the list to begin (BA9)<br>
  2)  I click the custom module/macro button &amp; popup box asks me the range (G$8:G$10000)<br>
  3)  The result then autofills in column (BA)  </p>
  
  <p>Lastly, can the code be amended so that the restriction of "The first cell where you call the function must be in the same row where the range starts." be removed so that I can use a reference from another sheet within the same workbook.  </p>
  
  <p>I apologise if I should have gone direct to the coder, the thread that it was in is old &amp; I thought given the amount of change I'm asking for it may be better suited in its own question.  </p>
  
  <p>Thank you in advance.</p>