Compile Error Argument Not Optional Excel Vba
Contents |
resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums Blogs Channel argument not optional when calling function 9 Documentation APIs and reference Dev centers Retired content Samples We’re sorry. excel vba compile error byref argument type mismatch The content you requested has been removed. You’ll be auto redirected in 1 second. Visual Basic Reference excel vba compile error variable not defined Visual Basic Language Reference Error Messages Error Messages Argument not optional (Visual Basic) Argument not optional (Visual Basic) Argument not optional (Visual Basic) '#ElseIf' must be preceded by excel vba compile error method or data member not found a matching '#If' or '#ElseIf' '#Region' and '#End Region' statements are not valid within method bodies/multiline lambdas '
Excel Vba Compile Error Sub Or Function Not Defined
directly '
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more
Excel Vba Compile Error User-defined Type Not Defined
about Stack Overflow the company Business Learn more about hiring developers or posting ads excel vba compile error in hidden module with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow excel vba compile error object library invalid is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up VBA Function - Argument Not Optional up vote 3 down vote favorite Public https://msdn.microsoft.com/en-us/library/102s19ky.aspx 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 http://stackoverflow.com/questions/23574083/vba-function-argument-not-optional = "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? excel vba share|improve this question edited May 9 '14 at 21:22 GSerg 47.8k775128 asked May 9 '14 at 21:14 user3403175 32117 possible duplicate of What does the keyword Set actually do in VBA? –Brad May 9 '14 at 21:18 stackoverflow.com/q/5965593/11683 –GSerg May 9 '14 at 21:20 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 May 9 '14 at 21:31 add a comment| 3 Answers 3 active oldest votes up vote 7 down vote accepted Anytime you assign an object you need to use the set keyword. set RETURN_Equipment = myCollection share|improve this answer answered May 9 '14 at
Forums Excel Questions Compile Error: Argument not optional Results 1 to 9 of 9 Compile Error: Argument not optionalThis is a discussion on Compile Error: http://www.mrexcel.com/forum/excel-questions/572362-compile-error-argument-not-optional.html Argument not optional within the Excel Questions forums, part of the Question Forums category; Good Morning, Afternoon, Night I'm back on VBA in Excel and I'm trying to get a change-event to fire off ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear compile error Mode Switch to Hybrid Mode Switch to Threaded Mode Aug 17th, 2011,06:02 AM #1 DoosanRuss New Member Join Date Aug 2010 Posts 25 Compile Error: Argument not optional Good Morning, Afternoon, Night I'm back on VBA in Excel and I'm trying to get a change-event to fire off a Function set in Module1 Code for Function: Code: Function CableSizer(Volts, length, Current) excel vba compile ' If Volts is zero then exit. If Val(Volts) = 0 Then Exit Function End If ' If Length is blank or zero, assume length is as defined M6 m. If Val(length) = 0 Then length = Worksheets("POWER CABLE LIST").Range("M6") End If ' Line Current is zero then exit. If Val(Current) = 0 Then Exit Function End If 'Look up tables 4E2a and 4E2B to establish minimum acceptable 'cable size' ' I Line current ' L Cable Length ' V Volts ' CableSize minimum acceptable cable size ' Rating rating from Table 4E2B for minimum acceptable cable size ' Limit Voltage drop for minimum acceptable cable size ' ListRow Current item in Cable List ' incr Number of time we have stepped up table 4E2B Call Lookup(Val(Current), Val(length), Val(Volts), Size, Rating, VoltageDrop, ListRow, incr) CableSizer = Size End Function Sub Lookup(I, L, V, CableSize, Rating, VoltageDrop, ListRow, incr) Dim OK As Boolean With ActiveWorkbook.Worksheets("Electrical Data") LastRow = 20 ' Set Rowfound to the smallest cable size available (ie at row 7) RowFound = 5 ' Work from largest to smallest cable size