CODEWITHSUNDEEP
vbaexcel
user3968302
user3968302

Reputation:

Function Optional Parameter in VBA Not Working

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

Upvotes: 2

Views: 1331

Answers (3)

user3968302
user3968302

Reputation:

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()

Upvotes: 1

Munir
Munir

Reputation: 3612

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

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

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.

Upvotes: 6

Related Questions