title: Checking Data Types in a Range
body: <p>I am trying to validate the data types of all cells in a user-selected range are the same, using a VBA function. I have the following code (simplified), which works for the most part:</p>
  
  <pre><code>Dim vTempRange As Variant
  Dim vCell As Variant
  
      vTempRange = DataRange.Value
  
      For Each vCell In vTempRange
          If Len(vCell) &gt; 0 Then
              'Use TypeName(vCell)
              'Complete validation here
          End If
      Next vCell
  </code></pre>
  
  <p>Sometimes a user may select a column of percentages, sometimes a column of decimal values, and sometimes a time value (not associated with a date). VBA seems to see all three of these as a <code>Double</code>, which is technically not incorrect. The problem is, the format of the selection will be used as part of the final output, so <code>12:00:00</code> should display as such, and not <code>0.50</code>, which is currently the case.</p>
  
  <p>I looked into using something like this in conjunction:</p>
  
  <pre><code>Dim vCell As Variant
  
      For Each vCell In DataRange
          If Len(vCell) &gt; 0 Then
              'Use vCell.NumberFormat
              'Complete validation here
          End If
      Next vCell
  </code></pre>
  
  <p>But the <code>NumberFormat</code> is not consistent. e.g., a user may have a percent listed as <code>0%</code> vs. <code>0.000%</code> or a time as <code>h:m:s</code> vs. <code>hh:mm:ss</code>, so I see it as being difficult to correctly capture this value.</p>
  
  <p>Is there a way to accurately determine <strong>without user intervention</strong> when a time is selected vs. one of the other types? Determining a percent value versus a <code>0&lt;x&lt;1</code> decimal value would also be nice, but not required.</p>
  
  <p>I have other options at my disposal, such as ignoring the formatting in the final output (really not desirable) or explicitly asking the user to identify the type (but this is neither as clean nor automatic as I would like).</p>