Function Optional Parameter in VBA Not Working

Question

I have the following function which, in an Excel cell, is written as =FACING_CHANGE(live, optimal, [override]):

    Function FACING_CHANGE(live As Integer, opt As Integer, _
      Optional override As Range) As String

        If override.Value <> "" And IsNumeric(override.Value) = True Then
            opt = override.Value
        End If
        If live = opt Then
            FACING_CHANGE = "SAME " & live
        ElseIf live > opt And opt = 0 Then
            FACING_CHANGE = "DELETED"
        ElseIf live > opt And opt > 0 Then
            FACING_CHANGE = "DECREASED"
        ElseIf live < opt And live = 0 Then
            FACING_CHANGE = "ADDED"
        ElseIf live < opt And live > 0 Then
            FACING_CHANGE = "INCREASED"
        End If
    End Function

Which returns a string result. I'm having trouble with the optional override parameter. It's of type range right now because a blank cell returns a 0 if override were an integer, which is valid. I need it as an optional param because in most but not all cases it will be used with the 3rd parameter.

When I insert a breakpoint and step through, the function exits automatically at the first if statement (If override.Value...). Below are some results. Any help would be appreciated!

enter image description here

I'll take a look and let you know if I'm having trouble! Thanks for the link! — user3968302
– user3968302, Commented Dec 4, 2014 at 16:16
Would adding a default value for the optional parameter help your case? — GER
– GER, Commented Dec 4, 2014 at 16:22

Mathieu Guindon · Accepted Answer · 2014-12-04 16:22:46Z

6

When your Range optional parameter isn't specified, your function receives it as Nothing.

Then when this line runs:

If override.Value <> ""

I bet VBA is blowing up with an "object reference not set" error, because you're accessing a property on an object reference that's Nothing.

Solution is to simply check whether override Is Nothing before accessing it.

answered Dec 4, 2014 at 16:22

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

Comments

Munir · Accepted Answer · 2014-12-04 16:33:14Z

You just need to verify if the override range object is defined or not. Try this:

Function FACING_CHANGE(live As Integer, opt As Integer, Optional override As Range) As String

    If Not override Is Nothing Then
        If override.Value <> "" And IsNumeric(override.Value) = True Then
            opt = override.Value
        End If
    End If
    If live = opt Then
        FACING_CHANGE = "SAME " & live
    ElseIf live > opt And opt = 0 Then
        FACING_CHANGE = "DELETED"
    ElseIf live > opt And opt > 0 Then
        FACING_CHANGE = "DECREASED"
    ElseIf live < opt And live = 0 Then
        FACING_CHANGE = "ADDED"
    ElseIf live < opt And live > 0 Then
        FACING_CHANGE = "INCREASED"
    End If
End Function

score 1 · Accepted Answer · 2014-12-05 02:52:37Z

1

The link posted in the comments under the original question by @vba4all.com was really helpful. Here's the revised first if statement:

    If IsMissing(override) = False And VarType(override) = vbInteger Then
        If IsNumeric(override) = True Then opt = override
    End If

Also change Optional params to Variant if using IsMissing()

edited Dec 5, 2014 at 2:52

answered Dec 4, 2014 at 16:29

user3968302

2 Comments

Mathieu Guindon Over a year ago

Note that this will still blow up if the function is passed Nothing - in which case the parameter will not be missing, but will not hold a valid Range reference either.

Tim Hall Over a year ago

Note: IsMissing only works for Variant types, not simple types or Object (Range in this case). See: msdn.microsoft.com/en-us/library/office/…

Collectives™ on Stack Overflow

Function Optional Parameter in VBA Not Working

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related