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]

I have this working now. I forgot that the columns were not always on the first row. I may have questions but please to not put effort into this question except for telling me of a better approach.
Thanks,
Kevin

 

[Domenic]

First, simply declaring a variable at the top of the module doesn't automatically make it public. In fact, your variable is actually a private variable. The following two statements are equivalent...

Dim dicStorageB As Object

and

Private dicStorageB As Object

To declare a public variable, you would need to use the keyword Public...

Public dicStorageB As Object

However, it's considered good practice to avoid public variables whenever possible. Sometimes, however, it's not possible, such as when working with event procedures.

Also, using a dictionary seems fine, especially since you're looking up values.

By the way, it's also a good idea to declare all variables. Adding the statement Option Explicit at the top of the module forces one to explicitly declare all variables, and will help catch errors.

Here's an example where the dictionary object is created within the calling procedure, then it passes it to a sub where it fills the dictionary with the appropriate key/value pairs, and then the calling procedure displays the corresponding value for the desired key.

Option Explicit

Sub keyValueCreateStorageB(ByVal dic As Object)

  Dim thisName As String
  Dim col As Long
 
  For col = 1 To Sheets("StorageB").UsedRange.Columns.Count
    thisName = Sheets("StorageB").Cells(1, col).Value
    dic.Item(thisName) = col
  Next
 
End Sub

Sub test()

    Dim dicStorageB As Object
    Set dicStorageB = CreateObject("Scripting.Dictionary")

    keyValueCreateStorageB dicStorageB
   
    MsgBox "trumpet is in column " & dicStorageB.Item("trumpet")
   
End Sub

Note that the dictionary object is case-sensitive. To make it case-insensitive, you can add the following line...

dicStorageB.CompareMode = 1 'TextCompare

Hope this helps!

 

[bklabel1]

Domenic,
I first learned GW BASIC at DeVry about 40 years ago. I used Various offshoots with Quick BASIC ,HP ALM and QTP, Visual BASIC, MS VBA word and Excel. It seems like a baby language compared to a language such as Java. Even after 40 years I still get very stuck at times. I don't know about many areas of VBA Excel. Bill Gates had a great idea when he made this language or at least promoted it.
I still run into questions such as why is there no exit break in VBA loops.

Option Explicit is a good safety feature.

I think in QTP or someplace else making a Dim before the first sub makes it public. I try to avoid Publics as much as possible. Your complete example is just what I need to improve my code.

I am glad that using dictionaries for my purpose is a reasonable idea.

I appreciate your thoughtful and well written reply.

Thanks,
Kevin

 

[Domenic]

Hi Kevin,

Actually, VBA does provide ways in which to exit loops.

You can use the Exit statement to exit a Do/Loop, For/Next, or For Each/Next loop.

For a Do/Loop, you can use Exit Do.

For a For/Next or For Each/Next loop, you can use Exit For.

Cheers!

 

[bklabel1]

I knew about the exit statements. I wrote the wrong thing.
What I meant to write is Continue statement.
I don't see a way to jump to the top of the loop in the middle of a loop.
What I do to get this feature is have an inner and outer loop. This is pseudo code:

do While condition
For x= 1 to 1
do stuff
Check condition and exit for if needed
do stuff
Check condition and exit for if needed
do stuff
Next
Loop

I can leave the inner loop and stay in the do while. I'd rather have continue in the language to make the code easier to read.

Thanks,

Kevin

 

[Norie]

Kevin

As far as I can see you could easily do that using If statements to skip code if conditions are met.

P.S. You could always through in some Goto statements for fun.

 

[bklabel1]

Kevin

As far as I can see you could easily do that using If statements to skip code if conditions are met.

P.S. You could always through in some Goto statements for fun.

I went with Go To statements but it looks so ugly with the labels on the left edge.
I am considering converting to the two level loop like I have above.
If I was near a MS Office Building I would want out side with a Picket sign saying "Continue Needed"
Thanks,
Kevin

 

[bklabel1]

First, simply declaring a variable at the top of the module doesn't automatically make it public. In fact, your variable is actually a private variable. The following two statements are equivalent...

Dim dicStorageB As Object

and

Private dicStorageB As Object

To declare a public variable, you would need to use the keyword Public...

Public dicStorageB As Object

However, it's considered good practice to avoid public variables whenever possible. Sometimes, however, it's not possible, such as when working with event procedures.

Also, using a dictionary seems fine, especially since you're looking up values.

By the way, it's also a good idea to declare all variables. Adding the statement Option Explicit at the top of the module forces one to explicitly declare all variables, and will help catch errors.

Here's an example where the dictionary object is created within the calling procedure, then it passes it to a sub where it fills the dictionary with the appropriate key/value pairs, and then the calling procedure displays the corresponding value for the desired key.

Option Explicit

Sub keyValueCreateStorageB(ByVal dic As Object)

  Dim thisName As String
  Dim col As Long

  For col = 1 To Sheets("StorageB").UsedRange.Columns.Count
    thisName = Sheets("StorageB").Cells(1, col).Value
    dic.Item(thisName) = col
  Next

End Sub

Sub test()

    Dim dicStorageB As Object
    Set dicStorageB = CreateObject("Scripting.Dictionary")

    keyValueCreateStorageB dicStorageB
  
    MsgBox "trumpet is in column " & dicStorageB.Item("trumpet")
  
End Sub

Note that the dictionary object is case-sensitive. To make it case-insensitive, you can add the following line...

dicStorageB.CompareMode = 1 'TextCompare

Hope this helps!

Should the CompareMode line go in the calling or called function?

 

[Domenic]

Should the CompareMode line go in the calling or called function?

I would suggest that you place it in the calling procedure, right after the dictionary object is created.