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

[JiangSH]

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!!

 

[Zot]

Are you trying to do this?

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

I have a worksheet with data in columns A and B. I am looking for a convenient way to take these columns and convert to dictionary where the cell in column A is the key and column B is the value,

stackoverflow.com

 

[Peter_SSs]

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.

 

[Zot]

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.

 

[Peter_SSs]

This is not way to add data to Dictionary
The Dict(KeyColumn(i, 1)) = ValueColumn(i, 1)

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.

 

[Zot]

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.

Yes, you are right. Value can be added that way too. My bad. Here is what I converted

Option Explicit
Sub testl()
'Dim columnA
'coluinnA = ReadColumn("a")
'ThisWorkbook. Worksheets (1). Range ("D:D") = coluinnA

Dim dictl
dictl = CreateDictForTwoColumns("a", "b")
End Sub
Function CreateDictForTwoColumns(Key As String, Value As String)
Dim KeyColumn, ValueColumn
Dim Dict As Object
Set Dict = CreateObject("scripting.dictionary")
Dim i
KeyColumn = ThisWorkbook.Worksheets(1).Range(Key + ":" + Key)
ValueColumn = ThisWorkbook.Worksheets(1).Range(Value + ":" + Value)
For i = 1 To UBound(KeyColumn, 1)
Dict(KeyColumn(i, 1)) = ValueColumn(i, 1)
Next
CreateDictForTwoColumns = Dict

End Function

 

[Peter_SSs]

Here is what I converted

.. but did you test it?

 

[Peter_SSs]

It is not entirely clear what the OP is wanting to achieve, but this is my estimate.

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

 

[JiangSH]

It is not entirely clear what the OP is wanting to achieve, but this is my estimate.

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

you estimate is right! That is excactly what i want ! thinks!!

 

[JiangSH]

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.

ok I will keep this in mind! really appreciate your instructions!