vba 函数中使用DateValue()的Type Mismatch错误，如何排查？

Type Mismatch error in function using DateValue(), how to troubleshoot?

提问by user2601892

Warning: I'm a noob.

警告：我是菜鸟。

I've written a Sub to find cells with red text and alter them.

我写了一个 Sub 来查找带有红色文本的单元格并更改它们。

@ThinkerIV gave me a great function to put in a cell and drag the formula out into adjoining cells, but that won't work due to the number of sheets to work on. So I wrote my Sub, calling his function (see code below). I passed it a Range of one cell, so it seems to me it should work?

@ThinkerIV 给了我一个很好的功能，可以放入一个单元格并将公式拖到相邻的单元格中，但由于要处理的工作表数量，这不起作用。所以我写了我的 Sub，调用他的函数（见下面的代码）。我通过了一个单元格的范围，所以在我看来它应该可以工作？

But, it keeps throwing out Type Mismatch (run-time error code 13) on the line where the function calls the DateValue()! The passed range shows a value of 1 (which is the number in the cell it refers to) when I hover over it in the editor, but I don'rt know if that's the cell's contents or some other value 1 being shown.

但是，它不断在函数调用 DateValue() 的那一行抛出类型不匹配（运行时错误代码 13）！当我在编辑器中将鼠标悬停在传递的范围上时，传递的范围显示值 1（这是它所指的单元格中的数字），但我不知道这是单元格的内容还是显示的其他值 1。

So, I really don't know how to find out exactly why this is happening. Is it that the range I passed is somehow not the right kind? Please inform me of why this code won't work!

所以，我真的不知道如何确切地找出发生这种情况的原因。是不是我通过的范围在某种程度上不是正确的类型？请告诉我为什么此代码不起作用！

I tried to change that line to the comment line below it (and a couple other blind-guess changes), but that has the same error.

我试图将该行更改为它下面的注释行（以及其他一些盲目猜测的更改），但是有相同的错误。

Thanks in advance!

提前致谢！

    Sub redTextToRealDates()

    Dim dateTemp As Date
    Dim redCell As Range
    Dim foundCell As Range
    Dim thisSheetsRange As Range
    Dim busyCell As Range
    Dim redTextCells As Range
    Set thisSheetsRange = ActiveSheet.usedRange

    'Build a range containing all the cells in a sheet containing red text.
    '  well... all cells formatted to HAVE red text, anyway.
    '  Anyone want to tell me how to write this to skip empty cells?
    '  Because I don't need to grab empty cells into this range...

    For Each busyCell In thisSheetsRange
        If (busyCell.Font.ColorIndex()) = 3 Then
            If redTextCells Is Nothing Then
            Set redTextCells = busyCell
            Else: Set redTextCells = Union(redTextCells, busyCell)
            End If
        End If
    Next busyCell

    'Change unknown format cells to date cells populated with concantenated
    'string of original contents and the active sheet's name.

    For Each foundCell In redTextCells
        foundCell.NumberFormat = "@"
        foundCell = GetConcantDate(foundCell)

    Next foundCell

    redTextCells.NumberFormat = "dd/mm/yy"
    On Error Resume Next


    End Sub


    Function GetConcantDate(rng As Range) As Date
        'Original code supplied by ThinkerIV on StackOverflow.com
        Dim dtTemp As Date
        dtTemp = DateValue(rng.Range("A1").Value & " " & rng.Parent.Name)
         'dateTemp = DateValue(foundCell.Value & " " & ActiveSheet.Name)
        GetConcantDate = dtTemp
    End Function

EDIT I cant post my own answer yet, so I am adding this solution:

编辑我还不能发布我自己的答案，所以我添加了这个解决方案：

When feeding data to Format(), the contents of the first cell formatted for red were NOT in text form. I had not put in place any way to ensure that I passed the proper data type. So, the line to format the cell as text (foundCell.NumberFormat = "@") before passing it to the function is what fixed it.

向 Format() 提供数据时，第一个设置为红色的单元格的内容不是文本形式。我没有采取任何方法来确保我传递了正确的数据类型。因此，在将单元格传递给函数之前将单元格格式化为文本的行 (foundCell.NumberFormat = "@") 是修复它的原因。

The solution was actually already written when I copy/pasted the code into the question - I just wasn't aware that it had fixed it because of another error on a different Sub. (I'm a noob and was confused dealing with multiple errors in multiple subs) I thought I had tried it again with that new line, but HADN'T, so still thought it was not working.

当我将代码复制/粘贴到问题中时，该解决方案实际上已经编写好了 - 我只是不知道它已经修复了它，因为另一个 Sub 上的另一个错误。（我是一个菜鸟，对处理多个 subs 中的多个错误感到困惑）我以为我已经用那条新线路再次尝试过，但没有，所以仍然认为它不起作用。

Thanks to all who helped. I feel a bit of a fool now, having found it like that. Hope you forgive me for my rookie flubber - too many Subs and Functions in a huge list in the editor and I got 'dizzy'... At least I can post a solution in case some other noob needs it!

感谢所有帮助过的人。我现在觉得有点傻，发现它是这样的。希望你原谅我的菜鸟笨拙 - 编辑器中一个巨大的列表中有太多的 Subs 和 Functions，我感到“头晕”......至少我可以发布一个解决方案，以防其他菜鸟需要它！

回答by majjam

Ok, I think there are two things here. Firstly, the DateValue function takes a string representation of a date, e.g. "01/01/2013", when you pass through an excel date from a range, you are passing through a number, like 41275. This throws the run time error 13.

好的，我认为这里有两件事。首先，DateValue 函数采用日期的字符串表示形式，例如“01/01/2013”​​，当您从一个范围内传递一个 excel 日期时，您传递的是一个数字，例如 41275。这会引发运行时错误 13 .

However, if you already have a date, why bother converting it? You seem to want all red cells to be converted to a date + the sheetname. To do this you'll have to have strings e.g. "01/01/2013 Sheet1", so you couldn't use DateValue here. Instead perhaps try something like this:

但是，如果您已经有了约会对象，为什么还要转换它呢？您似乎希望将所有红色单元格转换为日期 + 工作表名称。为此，您必须拥有字符串，例如“01/01/2013 Sheet1”，因此您不能在此处使用 DateValue。相反，也许尝试这样的事情：

Public Function GetConcatDate(rng As Range) As String
    Dim dtTemp As String
    dtTemp = Format(rng.Range("A1").Value, "dd/mm/yyyy") & " " & rng.Parent.Name
    GetConcatDate = dtTemp
End Function