vba invalid property assignment

• 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

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
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語