+ Reply to Thread
Results 1 to 16 of 16

Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function

    Help in understanding “Dictionaries in VBA”. General advice, Pros and Cons

    Hi, Just looking for some help to clear up a few things about “Dictionaries”
    Aka having Fun with my Fuking Dik Object...Lol .

    Question _1) A more specific Question. What is the difference between Dictionary and Scripting.Dictionary ? (If there is one, probably is not lol )

    To erlaberate: So I thought I knew what a Microsoft Scripting Runtime Dictionary ( MSRD) was and how to use it. ( Maybe I still do, lol.... **** ). I have used it quite a bit, mainly up to now by answering Threads using it as a way to learn about it.

    I have a file from an OP I am attempting to help. The Exact File origin is unknown ( No point asking the OP as I needed 6 month to teach him how to spell VBA in English and he has no idea.... about.... well anything I think, but he is nice, I like him lol... )
    It uses a “Dictionary” a lot, that is to say makes a lot of New instances of a thing which goes by the name of Dictionary. This appears to work similarly to a Microsoft Scripting Runtime Dictionary.

    So I am just trying to clear up if there is a subtle difference in the two, as I do see a minor difference in the Late binding use of it, or attempted use of it.

    Below is referenced a simple demo code. Anyone with an idea about Microsoft Scripting Runtime Dictionary will see I think exactly what it is doing.

    Rem 1 is a simple usage of a Microsoft Scripting Runtime Dictionary. (Scripting.Dictionary) ( I have it currently in Late binding, but it will work in early binding also if you comment out lines 80 and 90, and put back in lines 50 an 60 )

    Rem 2 is the same again but using the Dictionary, which to all intents and purposes seems to work like a Microsoft Scripting Runtime Dictionary in Early binding.

    ( It is a standalone code so you can run it without further a do...)
    _.....

    So Dictionary seems to work to all intents and purposes like an Early binding-ed Microsoft Scripting Runtime Dictionary(Scripting.Dictionary). And sure enough it fails at line 180 if I take off ( Uncheck ) the reference to the library Microsoft Scripting Runtime

    ***** As often , _....
    _...in carefully preparing my Post I may have the answer..... How’s this:
    Is the answer no more than Excel
    somehow guesses right in line 180 and 190 of my demo code that I mean
    Scripting.Dictionary ,
    whereas for some strange reason it will not guess that in line 197 ? ( In this Line 197 I try to do the typical second line of late binding
    = CreateObject("Scripting.Dictionary") ' No Problem here - it always works as I expect
    _..But_...._..
    = CreateObject("Dictionary") ' Run-time error '429': ActiveX component can't create object
    _......_....._...._ Does not work!! ===Minor difference===

    I note that after setting up Early binding, ( checking Microsoft Scripting Runtime in Extras, etc.. ) intellisense will offer me ( after I type = New ) both
    .Scripting.Dictionary
    and
    .Dictionary
    _ ... is this then that intellisense just offers what maybe / could be / perhaps, - but it is not an exact thing
    So bottom line to question 1). Is this the answer: There is no difference. Just VBA Syntax being a bit inconsistent in how it handles the two “Words”, -- “Dictionary” and “Scripting.Dictionary” ?

    _................::::..............

    Question _2)Help in understanding “Dictionaries in VBA”. General advice, Pros and Cons
    Is there really any advantage of using Dictonary , rather than for example a simple Array to do the same.

    I can see that Maybe some of the Properties of a dictionary are very handy/ convenient.., and that Adding increases automatically the size of a Dictionary by a “row” / record. , using a simple If __ Exists, rather than a .Match attempt to check for uniqueness... Etc.. etc... etc...
    There are lots of good references, for example.. ##
    To do all those Dictionary Methods and Properties with an Array involves, amongst other things, the Re- Dim Preserve stuff., which can get messy! .. But I guess one could do all that with Functions (I have !!!! – and it was messy, LOL.. !), or even make a Class, call it a Dictionary and make your own Add method, and all the other Methods and Properties .

    Is the point that a Dictionary references a Library and that telling me it uses something “outside” VBA that is particularly efficient, quick etc.??
    I lack the experience or time and resources to do extensive checks on this in the case of the Dictionary, but occaisionally I have seen that with simpler things, the theoretical advantage such as efficiencies seem to have been lost as computer speed has got so good. It is not that I am so enthusiastic about writing and maintaining the complicated Functions i have done to do similar things, but if , at the end of the Day with ever increasing computer speed they work as well, then at least I can see a bit more of what is going on and feel more “_...In control.... “ “_...lol !”.. )
    _................................................

    Question _3) Am I correct in saying that if I make a Function Declared as a Dictionary, then I must Early Bind
    because this:
    Fuction Dik( ) As Scripting.Dictionary
    Is pseudo doing this ( pseudoAlly ):
    Dim Dik As ( New ) Scripting.Dictionary __ ????( this is the first line of a typical 2 line way of declaring a variable in Early Binding , the second line would then be in the Function)

    _3)b).. ****EDIT: I have answered this..I think..
    And that will only work when I Early bind.. as in my code lines from line 250.......
    _...... Hmm .. I can late or Early bind within the Function though.... now there’s a Thought...

    Thanks
    Alan

    ****EDIT: I did a late Binding Function Also....( Edit Code from Line 300) so forget I Question _3

    But then , following on from that.. Question 3b) instead
    _ so then..
    Question _3b) as I demo in my code... there is no reason to Dim ( or is there ?? ) a variable, say Dik, to Take from a Function returning a dictionary itself As a dictionary ?
    I mean like this is OK to get a Dictionary from a function which returns a Dictionary
    Dim Dik As Object
    Set Dik = Fuk(___) ' In this case not New .. not a new instance
    Where Fuk can be defined as an Object an within that Function a Dictionary is created through late binding
    Question _C(ii) The thing receiving the Dictionary returned from a Function creating a Dictionary can be Dim ed As an Object. But I guess it is better practice generally to Dim that as a Dictionary ( .... or Scriptiung.Dictionary.... .. : )

    Just a last clarity here to answer my last ( 3) ) question....The answer is... I do not need to Dim a Function returning a Dictionary as Scripting.Dictionary ( or Dictionary )... ( but I guess it is probably more efficient to do that.. ?? )
    _.................................

    Demo Code Here:


    http://www.excelforum.com/showthread...70#post4431170

    Thanks again for reading
    Alan
    Last edited by Doc.AElstein; 07-12-2016 at 02:00 PM. Reason: Answered Question 3 I think maybe... mostly.... :)
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Hi,

    I'll try and take these questions one at a time if I may.

    In this case Dictionary and Scripting.Dictionary are the same. You can be sure of that because removing the reference to the Scripting runtime makes the Dictionary code fail. When you declare a variable as Dictionary, the compiler will check the available references to locate the correct object. There is no native VBA.Dictionary incidentally, though it is of course possible to create your own class called Dictionary, which is why I used the phrase "in this case".
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Question 2

    It is certainly possible to create your own Dictionary class or to replicate the functionality using arrays, hashtables or other objects. Unless you have good reason to do so, such as using a Mac version of Excel, I don't know why you would reinvent the wheel. For key/value pairs a Dictionary is typically faster than a native VBA Collection.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Question 3

    I think you have answered this yourself?

    Question 3b

    Also answered?

    I will merely note that a function declaration such as this:
    Please Login or Register  to view this content.
    is functionally, if you'll excuse the pun, equivalent to this:
    Please Login or Register  to view this content.
    rather than this:
    Please Login or Register  to view this content.
    Last edited by xlnitwit; 07-12-2016 at 06:08 AM. Reason: Clarity

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Question C(ii)

    If your function returns a specific object type, there is no benefit to late binding your variable. You may as well avail yourself of the benefits of early binding. If, on the other hand, the function returns a generic object, you would have to consider why it does so before you can determine whether to late or early bind the receiving variable.

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    ......... There is no native VBA.Dictionary incidentally, though it is of course possible to create your own class called Dictionary, which is why I used the phrase "in this case".
    Thanks. That clears that up. I expected that was the answer, but nice to get a confirmation,

    -....
    (_.....
    Quote Originally Posted by xlnitwit View Post
    ........... can be sure of that because removing the reference to the Scripting runtime makes the Dictionary code fail. When you declare a variable as Dictionary, the compiler will check the available references to locate the correct object. ...
    I guess I was thinking that there might be two similar Objects in the Microsoft Scripting Runtime Library, that is probably talking rubbish ? .
    .._ )


    _......



    So it follows that the decrepancy I got was just, as I thought, that VBA sometimes recognises Dictionary as Scripting.Dictionary and sometimes not.
    But as you say, it ls always exactly the same thing ( Object )
    Thanks
    Alan

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    Question 2
    It is certainly possible to create your own Dictionary class or to replicate the functionality using arrays, hashtables or other objects. Unless you have good reason to do so, such as using a Mac version of Excel, I don't know why you would reinvent the wheel. For key/value pairs a Dictionary is typically faster than a native VBA Collection.
    Thanks again.

    The reason for me to "reinvent the wheel" was, I guess,

    _ gettting a real feel for what was going on, a learning excersise I suppose.....but I will get over it.... if I ever learn... Lol

    Also
    _ I like not to rely on too many things..._....
    _..._....But I guess it is safe to say that as long as VBA is available, then the MSRD will also be available? .....or probably the MSRD might out live VBA ? - I guess the MSRD is a more fundamental library used possibly with a lot of other Applications, not just Excel) ( Sorry I am a complete Computer "Nit Wit" Lol )
    Thanks
    Alan

  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    Question 3
    I think you have answered this yourself?
    Question 3b
    Also answered?

    I think, yes , I have that answered, if you agree with my suggested answer.
    Thanks.

    _........................

    Quote Originally Posted by xlnitwit View Post
    ......
    I will merely note that a function declaration such as this:
    Please Login or Register  to view this content.
    is functionally, if you'll excuse the pun, equivalent to this:
    Please Login or Register  to view this content.
    rather than this:
    Please Login or Register  to view this content.
    I agree.
    Adding that ( New ) was an oversight by me and confused the issue a bit , the
    Set = New_____, comes in in the ( typical ) second line, which would be ( is ) in this case in my Functions.

    I added that ( New ) later, I do not know why now exactly..
    ( Possibly I was getting confused with the often used "shorthand" Early Binding of like using

    Dim objData As New dataobject

    instead of the "two line" version

    Dim objData As dataobject
    Set objData = New dataobject
    _...........)





    Thanks for pointing that stupidity out.
    Alan

    _....

    P.s.

    pun for fun as much as you like,, I say !

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    .....
    If your function returns a specific object type, there is no benefit to late binding your variable. You may as well avail yourself of the benefits of early binding. .
    Thanks,
    I understand generally, I think, the advantages and disadvantages of Late and Early binding. I allways use Early Binding myself.
    I tend to use Late Binding mostly just when I give Codes to OPs. ( The OPs I am capable of helping know often even less than me... which is saying something!, - so trying to explain to them about adding a library refference can be problamatic ( But I allways include the Early Binding equivalent with explanations 'commented out.....) )

    _.............................


    Quote Originally Posted by xlnitwit View Post
    ........ If, on the other hand, the function returns a generic object, you would have to consider why it does so before you can determine whether to late or early bind the receiving variable.
    _....
    _..........I only have a vague idea about what you are saying here. I have seen in the practice that only one or the other works sometimes. But i am getting out of my depth ( I am not a Programmer, BTW, - I know virtually nothing about Computing and am just learning to help use it in a personal project )

    I am afraid this is foreign languuage to me ......"....the function returns a generic object...."......
    If you had time to translate that into Layman's terms I would be greatful...... but that is not too important -- I may start getting out of my depth and drown... lol

    _...................................




    Thanks Again
    Last edited by Doc.AElstein; 07-12-2016 at 07:48 AM.

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    Question C(ii)

    ........

    I think what I was trying to suggest in Question C(ii) was that if my Function is returning a Dictionary, then it might be more efficint to Dim the variable I assign it to as a Dictionary, just as if a Function returns a whole Number , then it is more efficint to Dim the variable I assign it to as a Long...._...
    _..._....._.. But I expect the anaölogy may not be so straight forward with Objects -.... I have already done my head in trying to understand the special way VBA handles Objects when passing to Functions... lol.....
    http://www.excelforum.com/showthread...101544&page=10
    http://www.excelforum.com/showthread...=9#post4381274




    _.............................................




    Thanks very much for all your answers "xlnitwit" ( clearly you are not a XL Nit Wit lol ), you have been very helpful. I am very grateful
    Alan

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by Doc.AElstein View Post
    But I guess it is safe to say that as long as VBA is available, then the MSRD will also be available? .....or probably the MSRD might out live VBA ? - I guess the MSRD is a more fundamental library used possibly with a lot of other Applications, not just Excel)
    It is a library that I believe originated with Internet Explorer. It is therefore probably safe to say that any Windows computer will have it available. It is not uncommon for scripting to be disabled in some large enterprise environments as a security measure, but I believe that affects the windows script host rather than this particular library.

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by Doc.AElstein View Post
    I am afraid this is foreign languuage to me ......"....the function returns a generic object...."......
    I refer to the difference between the generic:
    Please Login or Register  to view this content.
    and the type-specific:
    Please Login or Register  to view this content.
    If the function declaration is the latter case, it is sensible also to declare the receiving variable as Scripting.Dictionary rather than as Object.

  13. #13
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    It is a library that I believe originated with Internet Explorer. It is therefore probably safe to say that any Windows computer will have it available. It is not uncommon for scripting to be disabled in some large enterprise environments as a security measure, but I believe that affects the windows script host rather than this particular library.
    Thanks again,
    _... looks like I should get a bit more used to using it then.
    Alan
    Last edited by Doc.AElstein; 07-12-2016 at 08:05 AM.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    I do not know if it will help or hinder here, but the difference between Dictionary and Scripting.Dictionary is the same as that between Range and Excel.Range

    It is preferable to use the full class identifier to avoid any potential confusion with other objects of the same name, such as Word's Range object.

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    ....

    If the function declaration is the latter case, it is sensible also to declare the receiving variable as Scripting.Dictionary rather than as Object.
    Thanks, I thought that was probably the case... I was confusing possibly with the Passing of Objects where the "Pointer" is passed, resultinng in not much differnce between the two. - but that is a differnt "story"..

    I guess an anology approximately is then here is
    Variant as opposed to, say, Long

    like

    Object as opposed to, say, Scripting.Dictionary
    Last edited by Doc.AElstein; 07-14-2016 at 01:27 AM. Reason: Typo on "Pointer"

  16. #16
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Func

    Quote Originally Posted by xlnitwit View Post
    ..... but the difference between Dictionary and Scripting.Dictionary is the same as that between Range and Excel.Range
    It is preferable to use the full class identifier to avoid any potential confusion with other objects of the same name, such as Word's Range object.
    Yes, that does make sense. Generally Ido not rely on the Implicit Defaults, which the original OP code I had was .. probably... the OP has no idea what as Implicit etc... is lol.. bless him : )
    That may help explain a bit the intellisense discrepancy... I guess there may be other Dictionary 's - which was my original concern was that I may have been accessing with .Dictionary
    something other than the
    Scripting.Dictionary,
    _.. which you have confirmed that i am not.

    You have been very helpful in me getting this very clear in my head

    Great and quick help
    Thank you again
    Alan
    Last edited by Doc.AElstein; 07-12-2016 at 09:31 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Scripting dictionary help
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2016, 09:32 PM
  2. [SOLVED] VBA Scripting.Dictionary Code Late Binding Error
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-20-2015, 03:06 AM
  3. Scripting dictionary help
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-14-2014, 09:51 AM
  4. need help with scripting dictionary
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 10-04-2014, 04:33 PM
  5. [SOLVED] Using Dictionary in VBA scripting
    By vnzerem in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-04-2014, 02:54 PM
  6. Scripting Dictionary
    By Tendla in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2013, 05:41 AM
  7. [SOLVED] Scripting Dictionary help
    By williams485 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-13-2012, 08:22 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1