wrong number of arguments or invalid property assignment in vba

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?

Additional resources

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

VBA Wrong number of arguments or invalid property assignment

• Thread starter mark hansen
• Start date Jun 1, 2020

mark hansen

Well-known member.

• Jun 1, 2020

I have a workbook that I use as a template to create other workbooks where the user collects information 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 string, the VBA code does not need changing. All the information the code needs (Path and File name for the data file, etc) is in a "Configuration" worksheet. so, I'm pretty sure the Worksheet_selectionsChange event is good, that's where the problem is showing up. To help the user enter data I use a Worksheet_SelectionsChange event to allow them to click on various cells to enter text, or bring up a pop up to pick the data they need. That's where the problems is now. When I click on cell I get the above error in the subject on a line that says If (Left(range("AL" & ActiveCell.Row).value,1) = "R" then the word "Left" is highlighted. OK Here are the changes that may have caused this. To aid with data collection, I have the user collecting data from our main frame through the Reflections Workspace program. I have code, running in Excel, that looks at the screen and captures information in certain places, and enters it into specific cells so it will be included in the data string. In order to get that to work, I needed to add the following reflections libraries ---- "Reflections for Unix and Open VMS" and "Reflections for Unix and Open VMS ActiveX Control 1.0 Type Library" Any ideas on what would cause the "Left" function to stop working? Thanks for any insight. Mark  

Excel Facts

MrExcel MVP, Moderator

It sounds like one of those libraries has its own Left function. Use VBA.Left instead.  

That was it!!! Thanks Rory  

Similar threads

• May 30, 2024

• Mar 27, 2024
• afalexander
• Aug 17, 2024
• Jun 8, 2024

• Jun 12, 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

• Forgotten Your Password?

• Today's Posts
• 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] LEFT function error (excelVBA) - Wrong number of arguments or invalid property assignment

LEFT function error (excelVBA) - 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
• Linear Mode
• Switch to Hybrid Mode
• Switch to Threaded Mode

Hi im getting the above error when i use the left function to extract characters in my code and think its very strange. has anyone else had this problem? Heres is an excerpt of a select case statement where its used. The code is part of a worksheet change routine and the target cell value has already been checked to make sure its not blank as shown, so i dont know why i get the errors. I tried the RIGHT function which doesnt throw up an error but is unsuitable for this task. All help is appreciated. 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: LEFT function error (excelVBA) - Wrong number of arguments or invalid property assign

I think you must have other variable/routine/object with name Left. If you change code to use VBA.Left does it work?

Please remember to mark threads Solved with Thread Tools link at top of page. Please use code tags when posting code: [code] Place your code here [/code] Please read Forum Rules

Izandol...youre a genius! Thank you that worked perfectly. Im using reflections for regis graphics (virtual terminal software), but there is no other variable named left The only occurrence of "Left" is as a substring of one of the reflections commands - "rcVtLeftKey". Could this by itself be the culprit? Have a nice weekend

You are welcome. There must be something else that has exact name Left. Perhaps you may search with Edit - Find?

Thread Information

Users browsing this thread.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

[solved] wrong number of arguments invalid or property assignment error, compile error: wrong number of arguments or invalid property assignment .... help, [solved] compile error: wrong number of arguments or invalid property assignment, wrong number of arguments invalid or property assignment error, code error “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

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Excel vba: Class sub: Wrong number of arguments or invalid property assignment on vba

I have a class State and some sub inside it that takes a Scripting.Dictionary as an argument. However when I try to pass a dictionary there, I get a wrong number of arguments or invalid property assignment error. I can't figure out what's wrong.

At the same time

works fine.

What may be wrong here and why the second case works, while the first fails?

• 3 Remove the brackets: stateCopy.addSecondItems dict1 or use Call –  Rory Commented Sep 19, 2017 at 13:29
• 2 By using parentheses, you force the object to be passed by value, hence the error. –  Kostas K. Commented Sep 19, 2017 at 13:36
• @Rory Thank you kind person! You saved me from going irreversible desperate! You might post an answer and I'll approve it. –  Ans Commented Sep 19, 2017 at 13:56
• @Kostas What's wrong with a dictionary being passed by value though? It's not like I need to edit it or something. –  Ans Commented Sep 19, 2017 at 13:57
• 1 You can't pass an object by value, only by reference. Passing by value means creating and passing a copy where by reference, only a reference to the object is passed. –  Kostas K. Commented Sep 19, 2017 at 13:58

You should remove the brackets:

or use Call

Otherwise the brackets try to coerce the dictionary to a value by calling its default property, Item , which requires an argument, hence the error message.

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged vba excel or ask your own question .

• The Overflow Blog
• From PHP to JavaScript to Kubernetes: how one backend engineer evolved over time
• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Feedback requested: How do you use tag hover descriptions for curating and do...
• What does a new user need in a homepage experience on Stack Overflow?

Hot Network Questions

• Are there any original heat shield tiles on any of the retired space shuttles that flew to space?
• Multi Wire Branch Circuit for Kitchen Small Appliances Circuit, AFCI and GFCI required
• "Knocking it out of the park" sports metaphor American English vs British English?
• Will this be the first time, that there are more People an ISS than seats in docked Spacecraft?
• How to extract code into library allowing changes without workflow overhead
• Is there racial discrimination at Tbilisi airport?
• AC compressor receives power but doesn't engage
• Which point on a hyperbola is closest to a circle
• Does gluing two points prevent simple connectedness?
• The work-energy theorem in a resistor
• Video game where the hero gets transported to another world with his sister to aid a king in a war
• Are there any virtues in virtue ethics that cannot be plausibly grounded in more fundamental utilitarian principles?
• How can I address my colleague communicating with us via chatGPT?
• When testing for normally distributed data, should I consider all variables before running shapiro.test?
• If the Collatz conjecture is undecidable, then it is true
• Why did Jesus choose to pray after they tried making him king?
• The McDonald's Option
• If Miles doesn’t consider Peter’s actions as hacking, then what does he think Peter is doing to the computer?
• How should I respond to a former student from my old institution asking for a reference?
• Are automorphisms of matrix algebras necessarily determinant preservers?
• Capitalization and Proper Nouns
• Miracle Miracle Octad Generator Generator
• Jacobi two square's theorem last step to conclusion
• Thai word not breaking properly between lines, in LuaLaTeX