Checking the input of a function?

[jxb]

to all,

rather than writing a "complicated" formula in a cell, I am writing a function. I am trying to understand wha tis the best way of checking if a value as been specified for an input.
I set up my test with IsMissing() but the input must be a Varian. I am working with Double

In my test I wan to make sure that the input has been specified or it's not zero. The zero part works

Function test1(inputvalue As Double) As String

Dim msg As String

    If inputvalue = 0 Then 'IsMissing(inputvalue) only work for Variant
        Debug.Print ; "value is missing"
        Debug.Print ; inputvalue
        msg = "value is missing"
    Else
        msg = "value is: " & inputvalue
    End If

    MsgBox msg
    test1 = msg

End Function

 

[Rick Rothstein]

Zero being easy to do. is the "nothing" that I am struggling with

Like others, I am having trouble understanding what you are looking for here. If an argument is not marked as Optional, it cannot be omitted under any circumstances, so it can never be "missing"... if it is, Excel (if it is being used as a UDF) or VB (if it is being called from other VB code) will raise the compile error "Argument not optional".

 

[jxb]

looks like I may have to review the function(s) and define the inputs as Variant. I know that all the inputs will be numbers hence me defining all my inputs as Double
I was just trying to understand the best of capturing having no data at all specified. If the user specified say -5.2 (for a given input) then I can catch this easily

if input < 0 then input=abs(input)

what I wasn't sure if there is nothing specified at all
It's like with a excel built-in function, if no argument is specified then one get a (lengthy) error message in a window displayed. I cannot have windows popping all over the place

as sgh pointed, 0 is a valid as any other numbers but on some instances I cannot have zero hence my original thinking

if IsMissing(input) or input = 0 Then
'default to a value
input = 0.3
else
input= abs(input) 'just to make sure and force the value
end if

in my head a missing value is not the same as zero

 

[Rick Rothstein]

looks like I may have to review the function(s) and define the inputs as Variant.

Unless you make those arguments Optional, changing the data type will not make any difference. If a variable is not marked as Optional, it cannot be omitted and still have your code execute... VB will stop things before you code gets a chance to do anything with the "Argument not optional" compile error.

 

[jxb]

thanks to all for the the inputs/suggestions. Looks like I had in mind something which might not be possible. Having look at the xls document I am am putting together I think it 'shighly unlikely that a value will be missing. So I have decided to "ignore" this check. I am setting up UDF to avoid lengthy formulas which end up being difficult to check.

Thanks again