检查范围内的数据类型 [英] Checking Data Types in a Range

问题描述

我正在尝试使用VBA函数来验证用户选择范围内的所有单元格的数据类型是否相同.我有以下代码(简体)，大部分都可以使用:

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

有时，用户可能会选择一列百分比，有时会选择一列十进制值，有时会选择一个时间值(与日期无关). VBA似乎将所有这三个都视为Double，从技术上讲这不是错误的.问题在于，所选内容的格式将用作最终输出的一部分，因此12:00:00应该这样显示，而不是0.50显示(当前是这种情况).

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

但是NumberFormat不一致.例如，某个用户可能以0% vs. 0.000%列出了百分比，或者以h:m:s vs. hh:mm:ss列出了时间，因此我认为很难正确捕获此值.

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.

是否有一种方法可以准确地确定何时选择时间(相对于其他类型的时间)?确定百分比值与0<x<1十进制值的比较也不错，但不是必需的.

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.

我还有其他选择可以使用，例如忽略最终输出中的格式(这是绝对不希望的)，或者明确要求用户标识类型(但这既不干净也不像我想要的那样自动). /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).

推荐答案

尝试一下.将此粘贴到模块中.然后，您可以将其用作工作表公式.

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

您可以进一步修改它，以便在Case IsNumeric(c): CellType = "Value"内添加IF子句，以使用INSTR

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

这篇关于检查范围内的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！