VBA Function - Argument Not Optional

Question

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?

possible duplicate of What does the keyword Set actually do in VBA? — Brad
– Brad, Commented May 9, 2014 at 21:18
The default 'value' of collection is .Item which requires an index. To assign the reference of the collection object to a variable you need to use the Set keyword at the start of the line. — Cor_Blimey
– Cor_Blimey, Commented May 9, 2014 at 21:31

Brad · Accepted Answer · 2014-05-09 21:16:04Z

44

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

set RETURN_Equipment = myCollection

answered May 9, 2014 at 21:16

Brad

12.4k4 gold badges51 silver badges82 bronze badges

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

1 Comment

Robomato Over a year ago

Worked like a charm, thank you! Now that you've pointed it out it makes complete sense.

user2023861 · Accepted Answer · 2016-07-08 19:15:35Z

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.

Tom Page · Accepted Answer · 2014-05-09 22:23:25Z

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

I did notice that the IsMissing() wasn't firing, but hadn't looked into it with my assignment error mucking everything up. Thanks for the tip you saved me some grief today. Regards,

Collectives™ on Stack Overflow

VBA Function - Argument Not Optional

3 Answers 3

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Linked

Related