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
___________________________________________________
I use the immediate window and ?dicStorageB.item(thisName) shows that the dictionary contains the value
This is called with
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.
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
VBA Code:
Dim dicStorageB As Object
VBA Code:
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
VBA Code:
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.