title: Function to count distinct values in a column range
body: <p>I am attempting to create a function in VBA that, when given a range of values, will return a Count Distinct of those values. For example:</p>
  
  <p><code>
  | Column A |
  |----------|
  | 1        |
  | 2        |
  | 3        |
  | 3        |
  | 3        |
  | 3        |
  | 4        |
  | 4        |
  | 5        |
  | 5        |
  | 6        |
  </code>
  Count of Rows = 11
  Distinct values = 6</p>
  
  <p>Here is the structure of the VBA code I'm trying to use to build a function I can call in Excel:</p>
  
  <pre><code>Function CountDistinct(dataRange As Range)
  
  Dim x As Double
  x = 0
  
  For i = 1 To dataRange.Rows.Count
  
  x = x + (1 / (CountIf(dataRange, dataRange(i))))
  
  Next i
  
  End Function
  </code></pre>
  
  <p>I'm completely new to VBA programming, so apologies for all of the obvious, glaring mistakes made in the code above, if it can even be called that.</p>
  
  <p>I know there are other ways to arrive at the correct answer, but I'm interested in learning how to create custom Excel functions. </p>
  
  <p>Also, the pseudo-logic behind my approach is as follows:</p>
  
  <ol>
  <li>Give the function <strong>CountDistinct</strong> a range of cells <em>dataRange</em></li>
  <li>Loop through the range</li>
  <li>For each cell in the range, perform a <strong>COUNTIF</strong> on that value across the range (so in the example above, rows 3-6 would each return <em>4</em>, since the number 3 appears 4 times in the range).</li>
  <li>For each cell in the range, add 1/(the result of step 3) to the result variable x</li>
  </ol>
  
  <p><code>
  | Values | CountIF(Value) | 1/CountIF(Value)            |
  |--------|----------------|-----------------------------|
  | 1      | 1              | 1                           |
  | 2      | 1              | 1                           |
  | 3      | 4              | 0.25                        |
  | 3      | 4              | 0.25                        |
  | 3      | 4              | 0.25                        |
  | 3      | 4              | 0.25                        |
  | 4      | 2              | 0.5                         |
  | 4      | 2              | 0.5                         |
  | 5      | 2              | 0.5                         |
  | 5      | 2              | 0.5                         |
  | 6      | 1              | 1                           |
  |        |                | SUM of 1/CountIF(Value) = 6 |
  </code></p>
  
  <p>This will return the Count of Distinct values in column A == 6. </p>