Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4444
  • Last Modified:

Excel VBA Question

Is there anything like a Java HashMap in Excel VBA?

I'm looking for a quick way to store and index values based on a string.
0
tmonteit
Asked:
tmonteit
  • 5
  • 3
  • 2
  • +1
3 Solutions
 
leonstrykerCommented:
The closest thing you would find is the Dictionary object

http://www.kamath.com/tutorials/tut009_dictionary.asp

Leon
0
 
Patrick MatthewsCommented:
Depending on what you're doing, a Collection might work too...
0
 
tmonteitAuthor Commented:
Problem with the Dictionary Object.  

I get an error.  "Compile Time error.  USer defined type not defined. "

Alt-F11 from MS Excel

Sub myFunction
  Dim dict as Dictionary

end Sub

When I read the instructions in the tutorial, it tell me:  'Add the dictionary object references by opening the project references from the Project menu and adding the Microsoft Scripting Runtime. The following diagram should help you out with this."

The Alt-F11 VB Editor doesn't have a project menu.

What's my next step?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Patrick MatthewsCommented:
tmonteit,

To add a reference in VBA:

Go to Tools|References from the menu.  Select the libraries you need references for.

Regards,

Patrick
0
 
tmonteitAuthor Commented:
In Excel VBA, Under the "Tools" menu,  the "References" item is greyed out.   Grrrrrr!!!  Frustrating.

  What's causing this?  I would like to try the dictionary object it sounds exactly like what i need.

-t
0
 
Patrick MatthewsCommented:
t,

Make sure you are not in break mode.  Select Run|Reset from the menu, and then try the references again.
0
 
leonstrykerCommented:
> In Excel VBA, Under the "Tools" menu,  the "References" item is greyed out.

You also need to have a worksheet open and at least one module added to it.

Leon
0
 
Patrick MatthewsCommented:
Leon said:
>>You also need to have a worksheet open and at least one module added to it.

Perhaps I am misunderstanding, but I am not sure I agree.

1) In Excel, create new workbook
2) Go to VB Editor
3) Add a reference to some library

I can do all of those worksteps, without needing to add a module...

Patrick
0
 
leonstrykerCommented:
In the past I found that if you have Excel open, and absolutly no Workbooks or XLA loaded, you could not establish a reference. Opening a module, which is also imposible without an opened workbook, is a simple way to insure that this does not happen.

Leon
0
 
Patrick MatthewsCommented:
Leon,

Well yes, if no workbook nor add-in is open, then of course you cannot add a reference--
nothing to add it to.

:)

Patrick
0
 
Rory ArchibaldCommented:
You could also skip the reference and late bind it:
Dim dict as Object
Set dict = CreateObject("Scripting.Dictionary")

FWIW
Rory
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now