wrong number or arguments or invalid property assignment

How To Fix “Invalid Property Assignment (Error 450)”

If you are testing the return value of a function in the immediate window in VBA and get the following error:

Wrong number of arguments or invalid property assignment (Error 450)

What you are doing is something like this:

Then in the immediate window typing:

The code appears to work fine, but the error is a mystery. The reason for the error is that the immediate window call expects a collection, but isn’t receiving one.

Therefore, to properly test your functions returning a collection data type create a test subroutine that calls the function and receives the collection returned. Such as:

Then in the immediate window enter the name of the subroutine just created:

You should then see the following output:

When testing functions that return a Collection data type instead of using the immediate window defer instead to calling them from a test subroutine. Then output the desired response to visually check your code works, or instead of printing to the immediate window, use Debug.Assert like this:

If there’s a problem with your function when the subroutine is ran in the immediate window it will break at the failed assertion test.

Either way, learning this has helped brush up my rusty Excel VBA skills as I jump back into Excel VBA 2016.

• 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 Thread starter JiangSH
• Start date Start date Dec 13, 2021
• Tags 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

Excel Facts

Well-known member.

• Dec 14, 2021

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

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

• Question Question
• Mar 12, 2024
• Patriot2879
• Jun 18, 2024
• Apr 10, 2024
• Oct 19, 2024
• Sep 11, 2024

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

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 or property assignment not valid

• 8 contributors

An assignment has been attempted that is not valid.

To correct this error

Check that the number of arguments you have supplied matches the number required by the target.

Check the property assignment.

• Assignment Operators

Additional resources

Word Top Contributors: Stefan Blom  -  Charles Kenyon  -  Suzanne S. Barnhill  -  Jim_ Gordon  -  Bob Jones AKA: CyberTaz  ✅

December 13, 2024

Word Top Contributors:

Stefan Blom  -  Charles Kenyon  -  Suzanne S. Barnhill  -  Jim_ Gordon  -  Bob Jones AKA: CyberTaz  ✅

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

Ask a new question

• Volunteer Moderator |
• Article Author

Wrong number of arguments or invalid property assignment error message in Ribbon customizaiton

I am trying to add a button that runs the macro ContinuousPageNumbers1 from a Ribbon button. The button shows up fine. The procedure is in the same template as the ribbon modification.

The onAction is "ContinuousPageNumbers1." When I click on the button I get the following error:

If I go into the vba editor to the macro, I can run it fine. Same with running it directly in the template using the Macros dialog.

I can also run the macro by attaching it to a QAT button (through the interface, not XML).

Report abuse

Reported content has been submitted​

Replies (1) 

Through another forum I found that I needed to have the argument (control as IRibbonControl) for the procedure.

How soon we forget!

(Now it will not run directly or through the QAT icon to the macro, but that is fine.)

This can be marked as answered.

2 people found this reply helpful

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.

Question Info

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

• Forgotten Your Password?

• Mark Forums Read
• Quick Links :
• What's New?
• Members List

Forum Rules

• Commercial Services
• Advanced Search

• Microsoft Office Application Help - Excel Help forum
• Excel Programming / VBA / Macros
• [SOLVED] wrong number of arguments or invalid property assignment

wrong number of arguments or invalid property assignment

Thread tools.

• Show Printable Version
• Subscribe to this Thread…

Rate This Thread

• Current Rating
• ‎ Excellent
• ‎ Average
• ‎ Terrible

I've been racking my brain on this for a while now and trying different things but can't for the life of me figure out why I'm getting an error. I'm getting the error "wrong number of arguments or invalid property assignment" after the first Else statement, so on the line: ActiveWorkbook.Worksheets(sht).Range.AutoFilter Field:=Column1, Criteria1:= _ ">=" & Threshold1, Operator:=xlAnd Any ideas as to why? Please Login or Register to view this content. new Clipboard(".copy2clipboard",{target:function(a){for(;a ? a.getAttribute?a.getAttribute?!/bbcode_description/.test(a.getAttribute("class")):null:null:null;)a=a.parentNode;for(var b=a.nextElementSibling;b?!b.classList.contains("bbcode_code"):null;)b=b.nextElementSibling;return b}});

Re: wrong number of arguments or invalid property assignment

Try first to replace Sheets with Worksheets in the For codeline …

Here's the questionable section with the code that throws the error in bold: Please Login or Register to view this content. new Clipboard(".copy2clipboard",{target:function(a){for(;a ? a.getAttribute?a.getAttribute?!/bbcode_description/.test(a.getAttribute("class")):null:null:null;)a=a.parentNode;for(var b=a.nextElementSibling;b?!b.classList.contains("bbcode_code"):null;)b=b.nextElementSibling;return b}});

Originally Posted by Marc L Try first to replace Sheets with Worksheets in the For codeline … I tried that, I'm still getting the same error?

Compare "<=" & Threshold1, Operator:=xlAnd Else ActiveWorkbook.Worksheets(sht).Range.AutoFilter Field:=Column1, Criteria1:= _ ">=" & Threshold1, Operator:=xlAnd End If To: "<=Threshold2", Operator:=xlAnd Else ActiveWorkbook.Worksheets(sht).Range.AutoFilter Field:=Column2, Criteria1:= _ ">=Threshold2", Operator:=xlAnd Etc.

Trevor Shuttleworth - Retired Excel/VBA Consultant I dream of a better world where chickens can cross the road without having their motives questioned 'Being unapologetic means never having to say you're sorry' John Cooper Clarke

Originally Posted by TMS Compare "<=" & Threshold1, Operator:=xlAnd Else ActiveWorkbook.Worksheets(sht).Range.AutoFilter Field:=Column1, Criteria1:= _ ">=" & Threshold1, Operator:=xlAnd End If To: "<=Threshold2", Operator:=xlAnd Else ActiveWorkbook.Worksheets(sht).Range.AutoFilter Field:=Column2, Criteria1:= _ ">=Threshold2", Operator:=xlAnd Etc. Yes I realized that for the rest of them the syntax was off. I changed those to match the syntax of the Factor1 etc... "If" statements (how you first said compare) but still get the error

Add ActiveWorkbook where you forgot it or remove it if not necessary …

Originally Posted by Marc L Add ActiveWorkbook where you forgot it or remove it if not necessary … I'm not sure what you mean? I don't know where I would have forgotten it, I tried to include it everywhere, and when I try removing it I still get an error :/

Inconstant code, like Sheets & Worksheets, why some sheets references are alone and why others use ActiveWorkbook before ? Do it manually with Excel features and once that works, go back to the previous state, activate the Macro Recorder and redo the same manual operations in order you can compare the working generated code with yours …

Ok I finally figured it out. Apparently since the data is in a table, in order to reference the table I needed to add "ListObjects(1)" after the Wrosksheets reference. So it becomes ActiveWorkbook.Worksheets(sht) .ListObjects(1) .Range.AutoFilter Field:=Column2, Criteria1:= _ ">=Threshold2", Operator:=xlAnd

Similar Threads

Wrong number of arguments or invalid property assignment vb6, [solved] wrong number of arguments invalid or property assignment error, compile error: wrong number of arguments or invalid property assignment .... help, wrong number of arguments invalid or property assignment error, [solved] wrong number of arguments or invalid property assignment, [solved] wrong number of arguments or invalid property assignment, wrong number of arguments or invalid property assignment, posting permissions.

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off
• ExcelForum.com