EXCEL: What is proper "Type" for Dictionary Key [RESOLVED - Variant!]
Esteemed Forum Participants and Lurkers:
===============================
Excel 2003, Microsoft Runtime Scripting Library
I can't figure out what the proper "Type" is for Dictionary Keys. The following code runs without Option Explicit, but how do I "Dim" a key variable so that I can turn Explicit back on?
Code:
Sub junk()
'Dictionary requires Microsoft Scripting Runtime in the "Tools > References" List
Dim aList As Dictionary
Dim aKey ' As ???????? '<< What is proper Type?
Set aList = New Scripting.Dictionary
aList.Add "Magic", "Treasure"
For Each aKey In aList.Keys
Debug.Print aKey, aList.Item(aKey)
Next
End Sub
Thank you for any and all suggestions, comments, and assistance.
Re: EXCEL: What is proper "Type" for Dictionary Key ???
Re: EXCEL: What is proper "Type" for Dictionary Key ???
Thanks DKenny ... but SORRY!!!
With "Dim aKey as Object":
"For Each aKey In aList.Keys" generates "Run-time error '424': Object required."
We both have something more to learn!
Re: EXCEL: What is proper "Type" for Dictionary Key ???
I think I figured out how to coerce VBA into revealing the some of the nature of its automatic assignments when "Explicit" is off.
* Turn off "Explicit"
* Put a breakpoint at the end of the code or some other appropriate point
* Turn on the "Locals" window
* Run the code
When the breakpoint is reached and the program pauses, look in the locals window. In this case, I found:
Code:
Expression Value Type
========== ========== ==========
aKey "Magic" Variant/String
Variant works fine as the data type for aKey with Explicit turned on. I'm wondering if this will work for even nastier data types?
Re: EXCEL: What is proper "Type" for Dictionary Key [RESOLVED - Variant!]
As a variant or Object. Depending on how your using the info later, you can convert to the proper data type: Cstr(var), Clng(Var), etc....
VB Code:
Option Explicit
Sub junk()
'No reference required this method
Dim aList As Object
Dim aKey As Variant
Set aList = CreateObject("Scripting.Dictionary")
aList.Add "Magic", "Treasure"
For Each aKey In aList.Keys
Debug.Print aKey, aList.Item(aKey)
Next
End Sub