Dictionary to hold column numbers

[bklabel1]

I want to store each column number into a dictionary. So I can say on sheet "Music" the cell with value "trumpet" is 7. If I ask the dictionary what column is "trumpet" it would return 7 so I know that trumpet is in the seventh column from the left.

Since I don't know of another way to do this I was trying with a Dictionary object. Please let me know if there is an easier way to do this. I will place my code here so that I find out what I did wrong. Even if there is a better way I will learn about using the data dictionary object in VBA.

I declare the object at the top before any sub or function. I do this to make it public. I don't know if it is a good idea to make them public but I don't know how to pass the dictionary into between sub/function

Dim dicStorageB As Object

___________________________________________________

Function keyValueCreateStorageB()
  Set dicStorageB = CreateObject("scripting.dictionary")
  For col = 1 To Sheets("StorageB").UsedRange.Columns.Count
    thisName = Sheets("StorageB").Cells(1,col).Value
    dicStorageB.Item(thisName) = col
  Next
End Function

I use the immediate window and ?dicStorageB.item(thisName) shows that the dictionary contains the value

This is called with

KeyValueCreateStorageB()

After leaving the function the immediate window shows that the dictionary object value is empty.

The dictionary is declared as public because it is declared before the first sub/function. (i don't know if this is correct). I am not returning a variable or object so maybe I can use a sub instead of a function.

The dictionary looses its contents when it leaves the function and I do not know why.

 

[bklabel1]

Domenic,
Appreciated about the location of CompareMode.

Why does:
Sub keyValueCreateStorageB(ByVal dic As Object)
work?

I was expecting it to be ByRef. I have to be wrong because it works.

My thinking was that if the dictionary object is taken in to the sub that any changes to the dictionary object would be lost once the sub is done with its work. It does not work like that so what is wrong with my understanding?

Thanks,

Kevin

 

[Domenic]

Have a look at the following link for a detailed explanation...

Passing Variable ByRef And ByVal

www.cpearson.com

 

[bklabel1]

Domenic,

I just woke up and looked at the link this morning. I have to come back to this. It's information overload right now. I quickly noticed that there is a section for passing objects. It says that they are always passed ByRef and I see examples of using ByVal. I need to go back to see why they have ByVal if it is always passed ByRef.

I appreciate the link.