dcsimg
Results 1 to 8 of 8

Thread: Can't create dictionary in Excel VBA

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2008
    Posts
    17

    Can't create dictionary in Excel VBA

    Hello:

    I’m getting the following error when compiling code in Excel VBA:

    “User-defined type not defined”

    The offending code is:
    Code:
     Public Function ArrayToDictionary(Arr As Variant, Dict As Scripting.Dictionary) As Boolean
    Specifically, it’s the part “Scripting.Dictionary”. Obviously, I don’t have the reference tied in. The problem is that I don’t have any of the two suggested (according to what I found out there) that are listed in the references for the VBA project: “Microsoft Scripting Library” or “ASP.VBS MultiDictionary library 1.1.10”. I’ve followed a few suggestions and downloaded and re-installed the Windows Script 5.6 and Windows Script 5.7 files. I’ve also added the scrrun.dll file to the VBA references and then looked for anything that might apply as far as scripting or dictionary. There’s supposed to be a file “nDict.Dll” that contains the “MultiDictionary” but I can’t find it anywhere even after re-installing the script engines. I’ve searched this site as well and didn’t find anything relevant. Can anyone help?

    Thanks.

    Etude.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,654

    Re: Can't create dictionary in Excel VBA

    Scripting:ictionary object is one of two key objects that are part of the Microsoft Scripting Runtime Library (scrrun.dll). The other scrrun.dll object is the well-known Scripting::FileSystemObject. Given how frequently used this latter object is, it's strange that the former is so little known. Dictionary's obscurity is probably because of its name, which can be confusing to administrators without a development background. . . .
    are you adding reference to scrrun.dll? whatever it may be called.
    are you creating a dictionary variable to pass to your function?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    May 2008
    Posts
    17

    Re: Can't create dictionary in Excel VBA

    are you adding reference to scrrun.dll? whatever it may be called.
    are you creating a dictionary variable to pass to your function?
    Hello westconn1:

    Answer: 1) Yes. 2) Not yet.

    1) I used the Tools-->References-->Browse and selected scrrun.dll. It appeared to add several things to the “Available References:” list. However, I didn’t find anything I could check to add with reference to “dictionary”.
    2) At this point, I’m not calling the function. I get the error when I simply try “Debug-->Compile VBA Project”.

    If I can get pass that error, I could then make a call with the right parameters. Making a call before linking the appropriate reference yields the same error. I guess I’m missing what to link as a reference from the loaded scrrun.dll or I just don’t recognize it by name.

    Etude.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,654

    Re: Can't create dictionary in Excel VBA

    i made a similar sample in an excel module, no errors on compile
    when you select scrrun, make sure the box is checked
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    May 2008
    Posts
    17

    Re: Can't create dictionary in Excel VBA

    Thanks:

    Actually, I decided to re-load scrrun.dll. This time, I found “Microsoft Scripting Runtime”. It wasn’t there before. I think it appeared after I installed either the 5.6 or the 5.7 Windows Scripting Engine and after I rebooted. I thought what I needed to install, look for and check off as a reference would be “Microsoft Scripting Library”.

    Compounding this problem may involve the fact that when I installed the scripting engines, some “.ocx” files appeared to no longer be registered. I had to go and fetch “vbusc.exe” to fix that problem, which was telling me: “License information for this component not found. You do not have an appropriate license to use this functionality in the design environment”; this after I created a user command under the Tools menu.

    With this change, I’m no longer getting that error on the dictionary function. I received a different error when I called the function:
    Code:
    DictResult = ArrayToDictionary(TestArr, Dict)
    The error had a reference to passing an incorrect value by reference at the (second parameter). I can no longer reproduce the error because the damned thing is working. It populated the dictionary, since I had declared “Dict” as public before passing it.

    Notice that I had to change the function to say “…Dict As Dictionary” instead of “…Dict As Scripting.Dictionary” in order to avoid a different error. This was the source of my confusion, since everywhere else I searched the references made were to the “scripting” library. Maybe I just happen to hit on something that works. But that makes me wonder what other people have that I don’t that makes the original code work. Can anybody explain that?

    Etude.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,654

    Re: Can't create dictionary in Excel VBA

    no idea, since i have not seen the original code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Junior Member
    Join Date
    May 2008
    Posts
    17

    Re: Can't create dictionary in Excel VBA

    Hi westconn1:

    Well, I got the source from Pearson Software Consulting Services, but there isn’t much to see as far as the code is concerned. The reason I say this is because regardless of what the code does, the problem is that it wouldn’t even compile on the function declaration where the missing reference is (see my original message). The code simply creates a dictionary and stores values from a 2-dimentional array. The problem is that it would never fly because of the parameter clause “…Dict As Scripting.Dictionary”. I have no doubt now that the problem was due to a missing reference. What I can’t figure out is what reference makes the original code work. Oh well, I appreciate your input.

    Thanks.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,654

    Re: Can't create dictionary in Excel VBA

    i downloaded the function, then called like this, no errors
    Code:
    Dim dic As Scripting.Dictionary
    ArrayToDictionary myarr, dic
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width