如何在VBA上的函数子程序中添加错误消息

Question

我真的是一个学习VBA的大一学生。我对如何在函数子例程中添加错误消息感到困惑。

这就是我的问题，当我完成识别一个函数时，我如何添加这样的错误消息：“请按递增顺序输入值”？

例如:如果我输入=triangular(3,2,1)，其中数字是按降序排列的，我应该会得到一条错误消息。

下面是我的代码：

Public Function triangular(Minimum As Single, mostlikelyvalue As Single, maximum As Single) As Single

    Dim uniform As Single
    Dim d As Single

    Randomize
    Application.Volatile

    d = (mostlikelyvalue - Minimum) / (maximum - Minimum)
    uniform = Rnd

    If uniform <= d Then
        triangular = Minimum + (maximum - Minimum) * Sqr(d * uniform)
    Else
        triangular = Minimum + (maximum - Minimum) * (1 - Sqr((1 - d) * (1 - uniform)))
    End If

End Function

Answer 1

您可以测试不正确的顺序，或者直接在函数中测试无效条目，并返回该条目，而不是使用错误处理

• 更改了变量名以帮助避免错误和与现有函数的混淆
• 使用变体函数来保存结果或两个自定义错误消息之一
• 您也可以使用双精度而不是单精度

代码

Public Function triangular(dbMinimum As Double, dbMostlikelyvalue As Double, dbMaximum As Double)

    Dim uniform As Double
    Dim d As Double
    Dim dbCnt As Double


    dbCnt = dbMinimum * dbMostlikelyvalue * dbMaximum
    If dbCnt = 0 Then
        triangular = "at least one value is zero"
        Exit Function
    End If

    If dbMostlikelyvalue > dbMaximum Or dbMinimum > dbMostlikelyvalue Then
        triangular = "values not sorted"
        Exit Function
    End If

    Randomize
    Application.Volatile

    d = (dbMostlikelyvalue - dbMinimum) / (dbMaximum - dbMinimum)
    uniform = Rnd

    If uniform <= d Then
        triangular = dbMinimum + (dbMaximum - dbMinimum) * Sqr(d * uniform)
    Else
        triangular = dbMinimum + (dbMaximum - dbMinimum) * (1 - Sqr((1 - d) * (1 - uniform)))
    End If

End Function

Answer 2

尝尝这个

Public Sub Sample
On Error Goto Err
'call your function here
'some more codes here

Exit Sub 'if all goes well code ends here

Err: 'Error handler
MsgBox Err.Description

End Sub