vba - 传入空日期的Excel VBA函数导致#VALUE!错误

我有一个VBA函数（DecTime），我称之为传递单元格的值。该单元格的格式设置为自定义hh：mm

在我的单元格中，公式为“ = DecTime（M6）”

如果M6是时间，例如01:05，那么它工作正常；如果它是null，那么我得到#VALUE！

我相信这是一个简单的解决方案，但是在过去的一个小时里，我从这里和Google尝试了很多事情，我感到困惑！

这是我的功能：

    Function DecTime(Optional time As Date = #12:00:00 AM#) As Single  'String

    Dim Hours As Integer
    Dim Minutes As Single
    Dim HoursStr As String
    Dim arrTime

'On Error Resume Next
'On Error GoTo error_handler

'   HoursStr = Format(time, "h:mm")

' DecTime = HoursStr

    If time = #12:00:00 AM# Then
'    If HoursStr = "12:00" Then
'    If IsEmpty(time) Then
'    If IsEmpty(time) = True Then
'    If IsNull(time) Then
'    If arrTime.Count = 0 Then
'    If InStr(0, time, ":") = 0 Then
'    If IsDate(time) = False Then
    DecTime = 88
'       DecTime = HoursStr
    Else

    arrTime = Split(time, ":")

    If arrTime(1) <= 0 Then
        Minutes = 0
    ElseIf arrTime(1) <= 5 Then
        Minutes = 0.1
    ElseIf arrTime(1) <= 10 Then
        Minutes = 0.2
    ElseIf arrTime(1) <= 15 Then
        Minutes = 0.3
    ElseIf arrTime(1) <= 20 Then
        Minutes = 0.3
    ElseIf arrTime(1) <= 25 Then
        Minutes = 0.4
    ElseIf arrTime(1) <= 30 Then
        Minutes = 0.5
    ElseIf arrTime(1) <= 35 Then
        Minutes = 0.6
    ElseIf arrTime(1) <= 40 Then
        Minutes = 0.7
    ElseIf arrTime(1) <= 45 Then
        Minutes = 0.8
    ElseIf arrTime(1) <= 50 Then
        Minutes = 0.8
    ElseIf arrTime(1) <= 55 Then
        Minutes = 0.9
    Else
        Minutes = 0
    End If

    Hours = arrTime(0)

    DecTime = Hours + Minutes
'       DecTime = HoursStr

    End If

'error_handler:
'    DecTime = 99
'Resume Next

End Function

从上面的代码中可以看到，我尝试了许多不同的选项来处理传入的空白参数，因此，如果有人可以告诉我我做错了什么，我将非常感激！

我是一名SQL程序员，所以对VB没有太多经验

最佳答案

假设您要在单元格为空或不包含日期的情况下返回0，则可以使用：

Function DecTime(Optional time = #12:00:00 AM#) As Double

    Dim Hours                 As Integer
    Dim Minutes               As Single
    Dim arrTime

    If Not IsDate(time) Then
        DecTime = 0
    ElseIf time = #12:00:00 AM# Then
        DecTime = 0
    Else

        arrTime = Split(time, ":")

        Select Case arrTime(1)
            Case Is = 0
                Minutes = 0
            Case Is <= 5
                Minutes = 0.1
            Case Is <= 10
                Minutes = 0.2
            Case Is <= 20
                Minutes = 0.3
            Case Is <= 25
                Minutes = 0.4
            Case Is <= 30
                Minutes = 0.5
            Case Is <= 35
                Minutes = 0.6
            Case Is <= 40
                Minutes = 0.7
            Case Is <= 50
                Minutes = 0.8
            Case Is <= 55
                Minutes = 0.9
            Case Else
                Minutes = 0
        End Select

        Hours = arrTime(0)

        DecTime = Hours + Minutes

    End If

End Function