IsMissing Function in Microsoft Excel

The IsMissing Excel Function is used to determine whether or not an optional value has been passed to a function call or not.

The Basics:

Function modules can be designed with Optional values set if no value(s) are passed to the module. For example, we can develop declare a function call to receive a text value, however if not text value is received a default value is assumed.

The declaration would look something like this

Function OurFunction(Optional ValueExpected As Variant)

Now, when this module is called we can use the IsMissing Function to determine if a value was passed or not. It is also important to know that the IsMissing Function is only useful to use with variant values.

Code

On Error Resume Next

If IsMissing(ValueExpected) Then
  ValueExpected = "No value was passed"
 End If

 MsgBox ValueExpected

Output

Code

'Declare variables

Dim StringToProcess As String'Variable string to process
Dim arrValues() As String'String Array
Dim A, B As Long'Value to use as a counter
Dim tmpValue As Variant
Dim Check As Boolean

StringToProcess = ActiveSheet.Cells(2, 1).Value 'Assign the information to process to our variable

arrValues() = Split(StringToProcess, " ") 'Parse the string into a string array using a space delimiter

Counter = 5

For A = LBound(arrValues) To UBound(arrValues)
  Check = IsNumeric(arrValues(A))
     If Check Then
  ActiveSheet.Cells(Counter, 1).Value = CDbl(arrValues(A))
  Counter = Counter + 1
 End If
Next

Output