This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Wrong number of arguments (Error 450)

  • 7 contributors

The number of arguments to a procedure must match the number of parameters in the procedure's definition. This error has the following causes and solutions:

The number of arguments in the call to the procedure wasn't the same as the number of required arguments expected by the procedure. Check the argument list in the call against the procedure declaration or definition.

You specified an index for a control that isn't part of a control array .

The index specification is interpreted as an argument but neither an index nor an argument is expected, so the error occurs. Remove the index specification, or follow the procedure for creating a control array. Set the Index property to a nonzero value in the control's property sheet or property window at design time .

You tried to assign a value to a read-only property , or you tried to assign a value to a property for which no Property Let procedure exists.

Assigning a value to a property is the same as passing the value as an argument to the object's Property Let procedure. Properly define the Property Let procedure; it must have one more argument than the corresponding Property Get procedure. If the property is meant to be read-only, you can't assign a value to it.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Submit and view feedback for

Additional resources

wrong number of arguments or invalid property assignment macro

Top Contributors in Excel: HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

February 13, 2024

Top Contributors in Excel:

HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

  • Search the community and support articles
  • Microsoft 365 and Office
  • Search Community member

Ask a new question

I am getting a compile error: Wrong number of arguments or invalid property assignment

I cannot get the "format" to show up as "Format" in this particular workbook.  

TempFilePath = Environ$("temp") & "\"     TempFileName = "Part of " & Sourcewb.Name & " " _                  & f ormat (Now, "dd-mmm-yy h-mm-ss")

this path works in all my other workbooks and they are all saved as XLSM.

Any suggestions?

Report abuse

Replies (14) .

* Please try a lower page number.

* Please enter only numbers.

  • Independent Advisor

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

It has all of that already... here is what it looks like.  The email, subject, and body are not up to date.  The are in bold needs to be in caps but keeps moving back to lowercase after I change it.

Sub Mail_ActiveSheet() ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010     Dim FileExtStr As String     Dim FileFormatNum As Long     Dim Sourcewb As Workbook     Dim Destwb As Workbook     Dim TempFilePath As String     Dim TempFileName As String     Dim OutApp As Object     Dim OutMail As Object     With Application         .ScreenUpdating = False         .EnableEvents = False     End With     Set Sourcewb = ActiveWorkbook     ' Next, copy the sheet to a new workbook.     ' You can also use the following line, instead of using the ActiveSheet object,    ' if you know the name of the sheet you want to mail :     ' Sheets("Sheet5").Copy     ActiveSheet.Copy     Set Destwb = ActiveWorkbook     ' Determine the Excel version, and file extension and format.     With Destwb         If Val(Application.Version) < 12 Then             ' For Excel 2000-2003             FileExtStr = ".xls": FileFormatNum = -4143         Else             ' For Excel 2007-2010, exit the subroutine if you answer             ' NO in the security dialog that is displayed when you copy             ' a sheet from an .xlsm file with macros disabled.             If Sourcewb.Name = .Name Then                 With Application                     .ScreenUpdating = True                     .EnableEvents = True                 End With                 MsgBox "You answered NO in the security dialog."                 Exit Sub             Else                 Select Case Sourcewb.FileFormat                 Case 51: FileExtStr = ".xlsx": FileFormatNum = 51                 Case 52:                     If .HasVBProject Then                         FileExtStr = ".xlsm": FileFormatNum = 52                     Else                         FileExtStr = ".xlsx": FileFormatNum = 51                     End If                 Case 56: FileExtStr = ".xls": FileFormatNum = 56                 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50                 End Select             End If         End If     End With     ' You can use the following statements to change all cells in the    ' worksheet to values.     '    With Destwb.Sheets(1).UsedRange     '        .Cells.Copy     '        .Cells.PasteSpecial xlPasteValues     '        .Cells(1).Select     '    End With     '    Application.CutCopyMode = False     ' Save the new workbook, mail, and then delete it.     TempFilePath = Environ$("temp") & "\"     TempFileName = "Part of " & Sourcewb.Name & " " _                  & format( Now, "dd-mmm-yy h-mm-ss")     Set OutApp = CreateObject("Outlook.Application")          Set OutMail = OutApp.CreateItem(0)     With Destwb         .SaveAs TempFilePath & TempFileName & FileExtStr, _                 FileFormat:=FileFormatNum         On Error Resume Next        ' Change the mail address and subject in the macro before        ' running the procedure.         With OutMail             .To = "blank for now"             .CC = ""             .BCC = ""             .Subject = "This is the Subject line"             .Body = "Hello World!"             .Attachments.Add Destwb.FullName             ' You can add other files by uncommenting the following statement.             '.Attachments.Add ("C:\test.txt")             ' In place of the following statement, you can use ".Display" to             ' display the mail.             .Send         End With         On Error GoTo 0         .Close SaveChanges:=False     End With     ' Delete the file after sending.     Kill TempFilePath & TempFileName & FileExtStr     Set OutMail = Nothing     Set OutApp = Nothing     With Application         .ScreenUpdating = True         .EnableEvents = True     End With End Sub

Question Info

  • Norsk Bokmål
  • Ελληνικά
  • Русский
  • עברית
  • العربية
  • ไทย
  • 한국어
  • 中文(简体)
  • 中文(繁體)
  • 日本語

MrExcel Message Board

  • Search forums
  • Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

  • If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
  • Question Forums
  • Excel Questions

Error in VBA(wrong number of arguments or invalid property assignment)

  • Thread starter JiangSH
  • Start date Dec 13, 2021
  • Tags debug error error vba
  • Dec 13, 2021

hi everyone! I was doing some exercise about Function in VBA when an error occurred(wrong number of arguments or invalid property assignment).Here is the picture. Really needs your help !! thx!!  

Attachments

Screenshot 2021-12-14 121349.png

Excel Facts

Well-known member.

  • Dec 14, 2021

stackoverflow.com

What is the easiest way to take two columns of data and convert to dictionary?

stackoverflow.com

MrExcel MVP, Moderator

Welcome to the MrExcel board! When asking questions about a particular code please post the actual code, not a picture of it. My signature block below has more help on how to do that. Also, if asking about an error, as well as giving the error message, tell us which line of code caused the error.  

A bit troublesome since code is short ? . I'm just guessing your problem here. This is not way to add data to Dictionary The Dict(KeyColumn(i, 1)) = ValueColumn(i, 1) should be Dict.Add KeyColumn(i, 1), ValueColumn(i, 1) However, your column has no range limit. When KeyColumn(i, 1) becomes rows of zero or blank, then you will have error.  

Zot said: This is not way to add data to Dictionary The Dict(KeyColumn(i, 1)) = ValueColumn(i, 1) Click to expand...
Peter_SSs said: Dict(SomeKeyValue) = SomeItemValue .. is a perfectly valid way to add an entry to a Dictionary. One thing that I noticed about the code is that "Key" and "Value" are used as variable arguments for the function and it is a very bad idea to use words as variables that vba already has special meanings for. There could also be an issue with the type of double quote marks used. Click to expand...
Zot said: Here is what I converted Click to expand...

It is not entirely clear what the OP is wanting to achieve, but this is my estimate. VBA Code: Sub test1() Dim dictl As Variant dictl = CreateDictForTwoColumns("a", "b") End Sub Function CreateDictForTwoColumns(sKey As String, sValue As String) Dim KeyColumn As Variant, ValueColumn As Variant Dim Dict As Object Dim i As Long Set Dict = CreateObject("scripting.dictionary") KeyColumn = ThisWorkbook.Worksheets(1).Range(sKey + ":" + sKey) ValueColumn = ThisWorkbook.Worksheets(1).Range(sValue + ":" + sValue) For i = 1 To UBound(KeyColumn, 1) Dict(KeyColumn(i, 1)) = ValueColumn(i, 1) Next CreateDictForTwoColumns = Array(Dict.Keys, Dict.Items) End Function  

Peter_SSs said: It is not entirely clear what the OP is wanting to achieve, but this is my estimate. VBA Code: Sub test1() Dim dictl As Variant dictl = CreateDictForTwoColumns("a", "b") End Sub Function CreateDictForTwoColumns(sKey As String, sValue As String) Dim KeyColumn As Variant, ValueColumn As Variant Dim Dict As Object Dim i As Long Set Dict = CreateObject("scripting.dictionary") KeyColumn = ThisWorkbook.Worksheets(1).Range(sKey + ":" + sKey) ValueColumn = ThisWorkbook.Worksheets(1).Range(sValue + ":" + sValue) For i = 1 To UBound(KeyColumn, 1) Dict(KeyColumn(i, 1)) = ValueColumn(i, 1) Next CreateDictForTwoColumns = Array(Dict.Keys, Dict.Items) End Function Click to expand...
Peter_SSs said: Welcome to the MrExcel board! When asking questions about a particular code please post the actual code, not a picture of it. My signature block below has more help on how to do that. Also, if asking about an error, as well as giving the error message, tell us which line of code caused the error. Click to expand...

Similar threads

SlinkRN

  • Oct 21, 2023
  • Mr_Ragweed2
  • Dec 19, 2023
  • Oct 26, 2023
  • Jan 5, 2024
  • Dec 18, 2023

Forum statistics

Share this page.

wrong number of arguments or invalid property assignment macro

We've detected that you are using an adblocker.

Which adblocker are you using.

AdBlock

Disable AdBlock

wrong number of arguments or invalid property assignment macro

Disable AdBlock Plus

wrong number of arguments or invalid property assignment macro

Disable uBlock Origin

wrong number of arguments or invalid property assignment macro

Disable uBlock

wrong number of arguments or invalid property assignment macro

IMAGES

  1. Solved: Wrong number of arguments or invalid property assignment

    wrong number of arguments or invalid property assignment macro

  2. Wrong number of arguments or invalid property assignment by Johnson

    wrong number of arguments or invalid property assignment macro

  3. How to fix Error 450 (Wrong number of arguments or invalid property

    wrong number of arguments or invalid property assignment macro

  4. iBrowse Error "Wrong number of arguments or invalid property assignment

    wrong number of arguments or invalid property assignment macro

  5. Wrong number of arguments or invalid property assignment error

    wrong number of arguments or invalid property assignment macro

  6. Solved: wrong number of arguments or invalid property assignment" on a

    wrong number of arguments or invalid property assignment macro

VIDEO

  1. wrong number lag gya 😂

  2. Top 10 List Why Anti-Evolutionists are WRONG

  3. Invalid Property Value Error Fixing in Barcode Label Software

  4. Select the incorrect statement

  5. Bad Arguments: False Analogy #Fallacy #12 Angry Men #logic #history #philosophic

  6. Macro Assignment Related Theory 0702310283

COMMENTS

  1. vba

    I have code that populates cells when double clicked on the cell. Here it is: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Ran...

  2. Wrong number of arguments (Error 450)

    Assigning a value to a property is the same as passing the value as an argument to the object's Property Let procedure. Properly define the Property Let procedure; it must have one more argument than the corresponding Property Get procedure. If the property is meant to be read-only, you can't assign a value to it.

  3. VBA Error wrong number of arguments or invalid property assignment

    Hello, I've found this bit of code during my searches on the web and this does work for me in other spreadsheets however, when I try and run this in...

  4. I am getting a compile error: Wrong number of arguments or invalid

    Dim OutMail As Object. With Application. .ScreenUpdating = False. .EnableEvents = False. End With. Set Sourcewb = ActiveWorkbook. ' Next, copy the sheet to a new workbook. ' You can also use the following line, instead of using the ActiveSheet object, ' if you know the name of the sheet you want to mail :

  5. VBA Wrong number of arguments or invalid property assignment

    VBA Wrong number of arguments or invalid property assignment. Thread starter mark hansen; Start date Jun 1 ... and sends that data to a data file. The main (starter) workbook is well used in many scenarios so I feel the VBA code is solid. The workbook was created so the only thing that need changing is the information that goes in the data ...

  6. excel

    Excel VBA: "Compile Error: Wrong Number of arguments or invalid property assignment" Hot Network Questions Show Sitecore form dropdown menu based on user logged in status

  7. Error in VBA(wrong number of arguments or invalid property assignment

    This is not way to add data to Dictionary. The Dict (KeyColumn (i, 1)) = ValueColumn (i, 1) Click to expand... Dict (SomeKeyValue) = SomeItemValue. .. is a perfectly valid way to add an entry to a Dictionary. One thing that I noticed about the code is that "Key" and "Value" are used as variable arguments for the function and it is a very bad ...

  8. vba

    Wrong number of arguments or invalid property assignment It is showing with SendMessage function. 'SendMessage recips, Subject, body, ccs, bccs, False, attachs

  9. Compile Error: Wrong number of arguments or invalid property assignment

    Easiest way to fix is to jump over to late binding by changing the way your olApp/Mail are declared and newed. Try changing to this: "Wrong number of arguments" means you are calling a subroutine with a different number of arguments (a.k.a. parameters) than the subroutine allows.

  10. Excel VBA: "Compile Error: Wrong Number of arguments or invalid

    First off, don't do that. You can do everything using VBA commands without moving the mouse and simulating clicks. If you read the documentation you will find that they say not to use mouse_event in favor of SendInput. Now the answer: Your code works in 64-bit Excel.

  11. excel

    I am trying to incorporate the excel vba split function in my code. However I get a "compile error, wrong number of arguments or invalid property assignment". I have tried the function in many diff...

  12. Wrong number of arguments or invalid property assignment

    Excel VBA: "Compile Error: Wrong Number of arguments or invalid property assignment" Hot Network Questions Minimizing the sum of the tangents of the half-angles of a triangle

  13. Excel vba: Class sub: Wrong number of arguments or invalid property

    However when I try to pass a dictionary there, I get a wrong number of arguments or invalid property . Stack Overflow. About; Products For Teams; Stack Overflow Public questions & answers; ... Wrong number of arguments or invalid property assignment in VBA. 0.

  14. excel

    Feb 18, 2016 at 18:53. Put in a break point at the beginning of the Sub (click in the line and press F9) then the focus will jump to your code when the Ribbon control calls it. Then F8 to step through. You should then see which line "hiccups". - Cindy Meister.

  15. excel

    I am having a problem with the Format() function in excel vba. I am trying to alter the formatting of a date formatted cell to change the way it is displayed. However, everytime i invoke Format to do that i get the error: "Compile Error: Wrong number of arguments or invalid property assignment." here is the code:

  16. vbscript

    Public Property Get repeatingval (i) repeatingval = m_repeatingval (i) End Property Public Property Let repeatingval (i, w) m_repeatingval (i) = w End Property End Class. You can't have a property where the setter takes an array and the getter returns an element of that array. To be able to assign an array and retrieve an element of that array ...