CODEWITHSUNDEEP
excelvba
Robomato
Robomato

Reputation: 277

VBA Function - Argument Not Optional

Public Function RETURN_Equipment(Optional category As String) As Collection
    Dim config As classConfiguration
    Set config = New classConfiguration

    Dim item As classItem
    Set item = New classItem

    Dim myCollection As Collection
    Set myCollection = New Collection

    For Each config In Configurations
        For Each item In config.colItems
            If IsMissing(category) Then   
                myCollection.add item
            ElseIf InStr(category, "mainframe") <> 0 And item.category = "mainframe" Then
                myCollection.add item
                MsgBox "Fired!"                
            ElseIf category = "accessory" And item.category = "accessory" Then
            Else
            End If
        Next
    Next

    RETURN_Equipment = myCollection
End Function

I keep getting

Compile error:
Argument not optional

I get the error on the last line

RETURN_Equipment = myCollection

I understand the error message, its telling me I did not fill out a parameter. But I only have one parameter, and I've declared it optional. It looks like the code thinks I'm trying to call the function from the function?

What gives?

Upvotes: 18

Views: 117683

Answers (3)

user2023861
user2023861

Reputation: 8208

I was getting this error because I was using the wrong function name when trying to return a result from a function. I was doing this:

Function MyFuncA(arg as String)
    MyFuncB = arg 'The problem is I'm using MyFuncB instead of MyFuncA
End Function

This happened because I copied a function from somewhere else and changed the name, but not the return statement. This is not the OP's problem, but I was getting the same error message.

Upvotes: 9

Tom Page
Tom Page

Reputation: 1241

Because you've specified the Optional Parameter as a string it will default to an empty string if you've not specified a value.

This means it can't be missing

If you'd specified it as 

Public Function RETURN_Equipment(Optional category) As Collection

It would be a variant and that could be missing, although you'd also be able to mess things up by passing non string variants as the category parameter

The best course of action is probably to replace 

If IsMissing(category) Then

with

If category = "" Then

And as Brad has pointed out you'll need to use Set

Set RETURN_Equipment = myCollection

For full details check this http://msdn.microsoft.com/en-us/library/office/gg251721%28v=office.15%29.aspx

Upvotes: 0

Brad
Brad

Reputation: 12245

Anytime you assign an object you need to use the set keyword.

set RETURN_Equipment = myCollection

Upvotes: 44

Related Questions