Compile Error Argument Not Optional Vba
Contents |
SQL Server 2014 Express 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 compile error argument not optional vba excel 9 Documentation APIs and reference Dev centers Retired content Samples We’re vba argument not optional sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Ask a vba compile error wrong number of arguments question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Excel VBA "Complie Error: Argument not optional" Microsoft ISV Community Center compile error byref argument type mismatch vba > Visual Basic for Applications (VBA) Question 0 Sign in to vote I have written a program, using VBA in Excel 2007, and it runs perfect. I have been reading that it is good practice to 'compile' your program, not something I have previously done. When I click compile, I get the following error: "Complie Error: Argument not
Vba Compile Error Sub Or Function Not Defined
optional". What does this mean? The error occurs on this line of code: Call InsertVaRSummaryFigure Which calls this function (which works fine): Function InsertVaRSummaryFigure(SummarySheet As String) Worksheets(SummarySheet).Select strFindAddressClearSection = Range("C1:C1000").Find("Total Core Rates GB", Range("C1"), xlValues, xlWhole, xlByColumns, xlNext).Address Range(strFindAddressClearSection).Offset(0, 2).Formula = "=-VaRData!D11" End Function Can anyone enlighten me? Wednesday, September 29, 2010 9:33 AM Reply | Quote Answers 0 Sign in to vote Hi Gazza101, If you write this: Function InsertVaRSummaryFigure(SummarySheet As String) This means, that the parameter SummarySheetis not optional. You must give that to the function. And if you write that: Call InsertVaRSummaryFigure you have no transfer parameter, so the compiler said: "Complie Error: Argument not optional" You can try this: Function InsertVaRSummaryFigure(Optional SummarySheet As String = "NameOfYourSheet") or this: Call InsertVaRSummaryFigure("NameOfYourSheet") or both if you want a standard Sheet, or another different: Function InsertVaRSummaryFigure(Optional SummarySheet As String = "Sheet1") Call InsertVaRSummaryFigure or Call InsertVaRSummaryFigure("SecondSheetInBla") So, try and have a look @http://msdn.microsoft.com/en-us/library/f25e2b6b.aspx Please 'Mark as Answer' if I helped. This helps others who have the same problem! M
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
Vba Compile Error Method Or Data Member Not Found
Us Learn more about Stack Overflow the company Business Learn more about hiring vba compile error user-defined type not defined developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the vba compile error expected = Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Strange “Argument not optional” error in https://social.msdn.microsoft.com/Forums/en-US/4c7402a2-de68-4dce-8a1b-b433e708c87a/excel-vba-complie-error-argument-not-optional?forum=isvvba VBA up vote 13 down vote favorite Private Sub importClipboard_Click() Dim data As Collection data = getClipboardData() ...do something... End Sub Function getClipboardData() As Collection ...do something... End Function I am getting an "Argument not optional" compile error on the line: data = getClipboardData() What am I doing wrong? There no arguments to the getClipboardData() function - so how can I be missing one? vba compiler-errors http://stackoverflow.com/questions/1032819/strange-argument-not-optional-error-in-vba share|improve this question asked Jun 23 '09 at 14:15 jwoolard 2,49562534 add a comment| 3 Answers 3 active oldest votes up vote 26 down vote accepted It's a bad error message, but your problem is a common one - you've got to put Set data = getClipboardData() as you're returning an object. share|improve this answer answered Jun 23 '09 at 14:22 Joel Goodwin 4,0641724 add a comment| up vote 0 down vote I know this is going to sound crazy, but it has happened to me before. Is it possible that you have declared another function with the same name? share|improve this answer answered Jun 23 '09 at 14:17 Ben Jones 764 add a comment| up vote 0 down vote Also, where (sometimes) I have copied a function, although I rename the function itself, and its calling line, sometimes I forget to rename the Return Parameter Assignment within the code. Then you get this "Argument not optional". share|improve this answer answered Jun 26 '14 at 13:42 Kenson Gurney 1 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email
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: Argument not optional http://www.mrexcel.com/forum/excel-questions/572362-compile-error-argument-not-optional.html 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 ... http://www.ozgrid.com/forum/showthread.php?t=18091 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 Mode Switch to Hybrid Mode compile error 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) ' If Volts is zero then exit. If vba compile error 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 in table 4E2A ' Until rating is acceptable. For Row = LastRow To 5 Step
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General "Argument not optional" how do i deal with this Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 6 of 6 Thread: "Argument not optional" how do i deal with this Thread Tools Show Printable Version Search Thread Advanced Search January 30th, 2004 #1 bourassa View Profile View Forum Posts I agreed to these rules Join Date 27th January 2004 Posts 16 i keep getting this error when i am tring to call this function Function totalitems(totalitems) UserRowCounter = UserStartRow While Worksheets("Log-Import").Cells(UserRowCounter, UserStartCol) <> "" UserRowCounter = UserRowCounter + 1 Wend totalitems = UserRowCounter End Function 'function to be the do all end all find logoff for each coresponding logon. Function Findlogoff(totalitems) itemcount = 0 totalitems (totalitems) For counter = 1 To totalitems If LogCol = "logoff" Then If UsernameCol = username Then If DayCol = UserDayCol Then Worksheet(username).Cells(userrow, UserLogoffCol).Value = Worksheets("log-import").Cells(logrow, LogCol).Value itemcount = itemcount + 1 End If End If End If Next counter If itemcount = 0 Then MsgBox "there were no logoffs found for & username & on that day. would you like to continue." End If End Function 'Call a logoff function here Findlogoff (totalitems) please help this is the l