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

 

[MrTeeny]

You can convert the data type to variant using

[COLOR=#333333]If [/COLOR][COLOR=#000080][FONT='inherit']CVar([/FONT][/COLOR][COLOR=#333333]inputvalue) = 0[/COLOR]

Type Conversion Functions

 

[jxb]

You can convert the data type to variant using

[COLOR=#333333]If [/COLOR][COLOR=#000080][FONT='inherit']CVar([/FONT][/COLOR][COLOR=#333333]inputvalue) = 0[/COLOR]

Type Conversion Functions

Thanks for the suggestion. How does on define the variable then ? As Double?
one still needs to specify zero as an input to work. I would like to catch the case where nothing as been specific or zero. Zero being easy to do. is the "nothing" that I am struggling with

 

[JackDanIce]

Why test if it's a double? Why not test if it's a number or not and then exclude 0? For your code, what difference is there with an input value of 1 vs 1.0?

 

[MrTeeny]

Thanks for the suggestion. How does on define the variable then ? As Double?
one still needs to specify zero as an input to work. I would like to catch the case where nothing as been specific or zero. Zero being easy to do. is the "nothing" that I am struggling with

YOu can use something like this if you're trying to check for empty variable etc

[COLOR=#333333]If  inputvalue=""[/COLOR][COLOR=#333333] Or [/COLOR][COLOR=#333333]inputvalue = 0 [/COLOR][COLOR=#333333]Then[/COLOR]

 

[Jaafar Tribak]

You are missing the Optional argument in case no argument is provided .

How abouth this :

If inputvalue = Empty Then

 

[shg]

Function test1(Optional inputvalue As Variant) As String
  If IsMissing(inputvalue) Then
    MsgBox "value is missing"
  Else
    MsgBox "value is: " & inputvalue
  End If
End Function

 

[jxb]

thanks. 'inputvalue' cannot be optional. user must specified something
The Real function will have multiple inputs one of which will be optional

 

[Joe4]

Note that if you want to test to see if a certain input is valid, you will want to use "Variant", as the user could have input anything, and you will want to capture that.
If you limit the input for your test1 function to "Double", you are already assuming one of the things you want to check for (is it a number?), which kind of defeats the purpose of checking it in the first place!

 

[shg]

thanks. 'inputvalue' cannot be optional. user must specified something
The Real function will have multiple inputs one of which will be optional

Reckon I'm missing what you're trying to do, then; 0 is as valid as any other value for a number.