CODEWITHSUNDEEP
vbaexcelexcel-2010
Gaffi
Gaffi

Reputation: 4367

Checking Data Types in a Range

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:

Dim vTempRange As Variant
Dim vCell As Variant

    vTempRange = DataRange.Value

    For Each vCell In vTempRange
        If Len(vCell) > 0 Then
            'Use TypeName(vCell)
            'Complete validation here
        End If
    Next vCell

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 Double, which is technically not incorrect. The problem is, the format of the selection will be used as part of the final output, so 12:00:00 should display as such, and not 0.50, which is currently the case.

I looked into using something like this in conjunction:

Dim vCell As Variant

    For Each vCell In DataRange
        If Len(vCell) > 0 Then
            'Use vCell.NumberFormat
            'Complete validation here
        End If
    Next vCell

But the NumberFormat is not consistent. e.g., a user may have a percent listed as 0% vs. 0.000% or a time as h:m:s vs. hh:mm:ss, so I see it as being difficult to correctly capture this value.

Is there a way to accurately determine without user intervention when a time is selected vs. one of the other types? Determining a percent value versus a 0<x<1 decimal value would also be nice, but not required.

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).

Upvotes: 4

Views: 19454

Answers (3)

SUNIL KUMAR
SUNIL KUMAR

Reputation: 135

VarType function Returns an Integer indicating the subtype of a variable, or the type of an object's default property.

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function

Ex1: using to write if.

Function DataType(x As Variant) As String
  If VarType(x) = vbDate Then
    DataType = "Date"
  ElseIf VarType(x) = vbString Then
    DataType = "String"
  '.....
  End If
End Function

Ex2: concatenate cells in range having value.

Function ConcatenateRange(cellRange As Range) As String
      Dim cel As Range, temp As String
      temp = ""
      For Each cel In cellRange
    'use VarType to check if the cell is empty.
    'if the cell is not empty concatinate it.

        If VarType(cel) <> vbEmpty Then 
             temp = temp & cel
    End If
      Next
      ConcatenateRange = temp
End Function

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149287

Try this. Paste this in a module. You can then use it as a Worksheet formula.

I had this code in my database which was picked up from here and I modified it to suit your needs.

Public Function CellType(c)
    Application.Volatile
    Select Case True
        Case IsEmpty(c): CellType = "Blank"
        Case Application.IsText(c): CellType = "Text"
        Case Application.IsLogical(c): CellType = "Logical"
        Case Application.IsErr(c): CellType = "Error"
        Case IsDate(c): CellType = "Date"
        Case InStr(1, c.Text, ":") <> 0: CellType = "Time"
        Case InStr(1, c.Text, "%") <> 0: CellType = "Percentage"
        Case IsNumeric(c): CellType = "Value"
    End Select
End Function

ScreenShot

enter image description here

You can further modify it to add an IF clause inside Case IsNumeric(c): CellType = "Value" to check for decimals, Scientific notation etc using INSTR

Upvotes: 5

David Zemens
David Zemens

Reputation: 53623

Declare vCell as Range and then do your check:

TypeName(vCell.Value)

That will accurately catch your dates.

YOu will likely need to add some if/then logic to capture "percents" since these are double-type values -- the "%" part is merely cell formatting, so you may be able to just check the Right(vCell.NumberFormat,1) = "%" .

Upvotes: 2

Related Questions