wrong number of arguments or invalid property assignment vba dictionary

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?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

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

• Mar 27, 2024
• Feb 21, 2024
• Apr 24, 2024

• Feb 24, 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

Contribute to the Microsoft 365 and Office forum! Click  here  to learn more  💡

April 9, 2024

Contribute to the Microsoft 365 and Office forum!

Click  here  to learn more  💡

Excel Forum Top Contributors: HansV MVP  -  Ashish Mathur  -  Andreas Killer  -  Jim_ Gordon  -  Riny_van_Eekelen   ✅

May 10, 2024

Excel Forum Top Contributors:

HansV MVP  -  Ashish Mathur  -  Andreas Killer  -  Jim_ Gordon  -  Riny_van_Eekelen   ✅

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

Ask a new question

Split Function Error. i get and error while trying to run the following code. "Compile Error Wrong Number of Arguments or Invalid property assignment".

I have tried using code from other coders from internet but everytime i get the same error.

Compile Error

Wrong Number of Arguments or Invalid property assignment

Sub Split()

Dim TextString As String, WArray() As String, Counter As Integer, Strg As String

TextString = Range("A2").Value

WArray = Split(TextString, "_")

For Counter = LBound(WArray) To UBound(WArray)

Strg = WArray(Counter)

Cells(2, Counter + 1).Value = Trim(Strg)

Next Counter

Report abuse

Reported content has been submitted​

This is because you have named the macro Split. This conflicts with the Split function. Rename the macro, for example to SplitText.

Also, you're missing the line

just above End Sub.

1 person 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.

Replies (2) 

Question info.

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

• Mark Forums Read
• View Site Leaders
• Knowledgebase
• Consulting Services
• PayPal Donation
• Advanced Search
• VBA Code & Other Help
• [SOLVED:] Help with syntax for nested dictionaries
• 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.

Thread: Help with syntax for nested dictionaries

Thread tools.

• Show Printable Version
• View Profile
• View Forum Posts

Help with syntax for nested dictionaries

I have the following code, which stops at the line Debug.Print (CStr(key1) + " - " + CStr(stoppForVeke(key1)) + " - " + CStr(stoppForVeke(key1)(key2))) with the error "Run-time error '450': Wrong number of arguments or invalid property assignment. I suspect the problem is how I refer to the "innermost" dictionary, though I thought the syntax I was using is correct? Can someone please explain to me what I am doing wrong? Is there some other (and better) way to referring to that dictionary? Option Explicit Sub finnverdier() ' Oppretting av variabler Dim områdeÅSøkeI As Range, c As Range Dim vekenr As Long, stanstid As Long, maskin As String Dim key1 As Variant, key2 As Variant Dim stoppForVeke As Dictionary, stoppForMaskin As Dictionary Set stoppForVeke = New Dictionary ' Ferdig med initialisering With Innlimt Set områdeÅSøkeI = Range(.Range("C3"), .Range("C1048576").End(xlUp)) If Not Intersect(.Range("C2"), områdeÅSøkeI) Is Nothing Then Exit Sub End If End With For Each c In områdeÅSøkeI vekenr = c.Offset(0, -2): stanstid = c.Offset(0, 2): maskin = c If stoppForVeke.Exists(vekenr) Then ' Treng ikkje å lage ny ordbok for veka, legg berre til den gamle If stoppForVeke(vekenr).Exists(maskin) Then ' Treng berre å legge til tida for den eksisterende maskina stoppForVeke(vekenr)(maskin) = stoppForVeke(vekenr)(maskin) + stanstid Else ' Må opprette legge til både element og tid for den eksisterande veka stoppForVeke(vekenr).Add maskin, stanstid End If Else ' Må lage nye ordbøker for maskina Set stoppForMaskin = New Dictionary stoppForMaskin.Add maskin, stanstid stoppForVeke.Add vekenr, stoppForMaskin End If Next For Each key1 In stoppForVeke For Each key2 In stoppForVeke(key1) Debug.Print (CStr(key1) + " - " + CStr(stoppForVeke(key1)) + " - " + CStr(stoppForVeke(key1)(key2))) Next Next End Sub

For j=1 to stoppForVeke.count Debug.Print j & " - " & stoppForVeke.keys()(j) Next You could have known.... http://www.snb-vba.eu/VBA_Dictionary_en.html#L_15

more suggestions ...

Thanks for your reply. However trying trying to replace For Each key1 In stoppForVeke For Each key2 In stoppForVeke(key1) Debug.Print (CStr(key1) + " - " + CStr(stoppForVeke(key1)) + " - " + CStr(stoppForVeke(key1)(key2))) Next Next with For j=1 To stoppForVeke.count Debug.Print j & " - " & stoppForVeke.keys()(j) Next gives me a Run-time error '9': Subscript out of range. And yeah I could probably have read a bit more on the subject and did a bit more searching before I came here. I'm not entirely sure what it is I am to take away from lesson 15 on your webpage though. Has it to do with how you describe putting all the variables into a string/variable? Cause I can't really see a strong similarity between what you are doing there and the code example you provided above. I am not a very proficient VBA coder though, so it could just be a lack of understanding the code Anyway, looking at the code again today, I see that I was simply a derp yesterday, and tried printing the actual dictionary object. Changing the print-line to Debug.Print (CStr(key1) + " - " + CStr(key2) + " - " + CStr(stoppForVeke(key1)(key2))) fixed everything.

Rather obvious For j=1 To stoppForVeke.count Debug.Print j & " - " & stoppForVeke.keys()(j-1) Next

It is The output from the two for loops is still quite dissimilar though? Originally Posted by Mine 49 - Tankrensk - 120 51 - Venting SAG - 48 53 - Venting Ovn - 600 1 - Tankrensk - 60 3 - SIR - 330 5 - Vogner - 45 5 - Venting Ovn - 150 5 - Tankrensk - 60 5 - Venting Sag - 120 7 - Venting Ovn - 120 7 - Former - 60 7 - Venting SAG - 45 7 - CF - 180 7 - Tankrensk - 210 8 - Former - 40 8 - Venting Ovn - 60 9 - Tilrigging - 90 9 - Venting Ovn - 63 10 - Venting Ovn - 30 10 - Former - 20 13 - Tilrigging - 120 15 - Venting Ovn - 60 16 - Venting SAG - 110 16 - Venting Sag - 40 17 - Venting Ovn - 200 17 - Omrørar - 60 17 - SIR - 180 18 - Utrenning - 120 18 - Venting Sag - 180 18 - Venting SAG - 60 19 - Venting SIR - 40 20 - Støypebord - 450 21 - Former - 200 21 - Utrenning - 240 21 - Støypebord - 120 21 - Tankrensk - 400 21 - Kran - 1200 21 - Venting sag - 120 21 - Venting sir - 40 21 - Venting SAG - 180 21 - Tilrigging - 240 23 - Venting Ovn - 500 23 - Støypebord - 180 23 - Former - 30 24 - Støypebord - 720 25 - Støypebord - 840 26 - Venting SAG - 80 26 - Venting SIR - 480 27 - Kran - 565 27 - Støypebord - 230 27 - Venting Ovn - 240 27 - Renner - 180 27 - Vogner - 330 27 - Utrenning - 330 28 - Støypebord - 60 28 - Venting Ovn - 120 29 - Venting Ovn - 240 30 - Venting Ovn - 100 30 - Venting Sag - 120 30 - Støypebord - 20 31 - Venting SAG - 240 31 - Venting Sag - 80 31 - Manglande forbruksmateriell - 20 32 - Venting Sag - 30 32 - Venting SAG - 240 Originally Posted by Yours 1 - 49 2 - 51 3 - 53 4 - 1 5 - 3 6 - 5 7 - 7 8 - 8 9 - 9 10 - 10 11 - 13 12 - 15 13 - 16 14 - 17 15 - 18 16 - 19 17 - 20 18 - 21 19 - 23 20 - 24 21 - 25 22 - 26 23 - 27 24 - 28 25 - 29 26 - 30 27 - 31 28 - 32

I'm not a clairvoyant..... Without a smple file....

I figured the syntax for outputting the contents of the dictionaries to the Immediate window would be the same no matter the contents they collected from the worksheet, and that I therefore didn't need to create one.

No need to make helpers comfortable....

Sorry, maybe I read your previous post as more snippy than it was. I really appreciate the help you and a lot of other forum members provide here.

• Private Messages
• Subscriptions
• Who's Online
• Search Forums
• Forums Home
• Announcements
• Introductions
• How to Get Help
• Non English Help
• Access Help
• SUMPRODUCT And Other Array Functions
• Outlook Help
• PowerPoint Help
• Office 2007 Ribbon UI
• Integration/Automation of Office Applications Help
• Other Applications Help
• Project Assistance
• Testing Area
• Mac VBA Help
• Other Mac Issues
• Book Reviews

Tags for this Thread

View Tag Cloud

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 On
• [VIDEO] code is On
• HTML code is Off

Forum Rules

• VBA Express
• Privacy Statement

• 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] 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

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?

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