How to add an error message in a function subroutines on VBA

Question

I am really a freshman to study the VBA. I am confused about how to add an error message in a function subroutines.

Here is my problem, when I finished identify a function, how can I add an error message like this: "Please enter the value in an increasing order"?

e.g: If I type =triangular(3,2,1), where the number is in a decreasing order, I should get an error message.

Here is my code:

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

stackoverflow.com/questions/14783616/…

Robin Mackenzie
– Robin Mackenzie

2017-02-20 00:04:11 +00:00
Commented Feb 20, 2017 at 0:04 — Robin Mackenzie
– Robin Mackenzie, Commented Feb 20, 2017 at 0:04

brettdj · Accepted Answer · 2017-02-20 07:46:26Z

2

You can test for incorrect order, or also an invalid entry directly in your function and return that rather than use error handling

Changed variable names to help avoid errors and confusion with existing function
Use a variant function to hold either the result or one of the two customised error messages
You may as well use Doubles rather than Singles

code

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

edited Feb 20, 2017 at 7:46

answered Feb 20, 2017 at 0:31

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3598756 Over a year ago

the "consistency" check doesn't account for dbMinimum being > dbMaximum nor it handles allowable equalities: it could be recoded as If Not (dbMinimum <= dbMostlikelyvalue And dbMostlikelyvalue <= dbMaximum And dbMaximum > dbMinimum) Then. Furthermore the dbCnt check should be either avoided (the previous check avoids division by zero issue) or recoded to check for any single value not allowed to be zero and, in any case, its message should be reworded to "at least one value is zero" since missing values would error out right at the function call

brettdj Over a year ago

@user3598756 if the max exceeds the mid, and the mid exceeds the min, then by logical extension the max has to be bigger than the min. that check is redundant. The equality check is also handled appropriately already. Agree on the missing value part though - the calling function would have errored before it was called.

user3598756 Over a year ago

@brettdj, if you are referring to my proposed check then please note that it has "equals" in the first two checks, so that the 3rd one is still necessary to avoid division by zero

Hexxed · Accepted Answer · 2017-02-19 23:59:45Z

1

Try this

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

answered Feb 19, 2017 at 23:59

Hexxed

6831 gold badge10 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to add an error message in a function subroutines on VBA

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related