I use it for a wide variety of purposes. Sometimes I use it like a pseudo-index. Say I have a large number of records (I routinely have lists of 90,000 rows) and I'm attempting to report on a series of entries. looping through these rows multiple times is very time consuming. I used to load the records into an array and search the array (which is much faster, but still time consuming). If I load the dictionary with a keyword, the "value" can be an array of row numbers. Let's say the records are transaction records in which the invoice number appears several times. (when it is created, each update and when closed) I loop through the records, and the first time an invoice is found, add it (and the row) to the dictionary: if it already has been loaded, append the Row Number: If (not dict_inv.exists(invNo)) then dict_inv.add invNo, RowNo else dict_inv.item(invNo) = dict_inv.item(InvNo) & "|" & RowNo end if once I've loaded the data into my dictionary, then I need to find the rows for the specific invoice, they are found quickly. ------------------------------ Another application: I have machine data (names, location, etc) (16,000 records) and manufacturing part numbers (22,000) in an Oracle database. during some reporting functions, the database is queried multiple times for information from these tables. Some reports may include loops that may result in 10-20,000 queries to these tables. This doesn't necessarily pose a problem with "hard" network connections, but wireless connections sometimes are very slow. If I load these tables into a Dictionary once, then the repeated queries do not depend on the network connection to the database. -------------- For the most part, just about any case in which I may have used an array, I prefer to use a Dictionary Object. unless, duplicates are allowed. hope this helps.
Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- >________________________________ > From: Eugene Bernard <eugene.bern...@gmail.com> >To: excel-macros@googlegroups.com >Sent: Monday, April 14, 2014 12:39 PM >Subject: Re: $$Excel-Macros$$ Need help to find the missing emp id on >particular date > > > >Thank you paul, for exposing us to the scripting.dictionary object. >Can you please share, some more details and uses of the dictionary Object. > > >Regards >Eugene > > > >On Tue, Apr 8, 2014 at 7:33 PM, Paul Schreiner <schreiner_p...@att.net> wrote: > >I did it two different ways. >>You IMPLIED that you wanted a macro (hence, the button) >>So, I used a technique in which I loaded a Dictionary object with all empid's >>for the date specified, then looked through the list of all empid's and >>checked for those that did not exist in the Dictionary object. >> >>Next, >>I simply used a Countifs() function and had it check for the empid and date >>and place a "X" in the column adjacent to the missing id's. >>Using a Filter, you can display the missing id's. >> >>If you wanted, you could create a change event to monitor the date cell. >>When the date is changed, the countifs update, and the filter is re-applied. >> >>Paul >>----------------------------------------- >>“Do all the good you can, >>By all the means you can, >>In all the ways you can, >>In all the places you can, >>At all the times you can, >>To all the people you can, >>As long as ever you can.” - John Wesley >>----------------------------------------- >> >> >> From: Ganesh N <ganeshg...@gmail.com> >>>To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> >>>Sent: Tuesday, April 8, 2014 9:14 AM >>>Subject: Re: $$Excel-Macros$$ Need help to find the missing emp id on >>>particular date >>> >>> >>> >>>Hi Team, >>> >>> >>>Any updates on the request ?? >>> >>> >>>Thanks & Regards, >>>Ganesh >>> >>> >>> >>>On Tue, Apr 8, 2014 at 1:15 PM, Ganesh N <ganeshg...@gmail.com> wrote: >>> >>>Hi Team, >>>> >>>> >>>>Need a help in find the missed emp id list. >>>> >>>> >>>>Sheet1 have the actual employee ids and date. In sheet2 have overall >>>>employee ids in that i need to find the employee missed on particular date. >>>>Please find the attachment for your reference. >>>> >>>> >>>>Thanks & Regards, >>>>Ganesh N -- >>>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>>https://www.facebook.com/discussexcel >>>> >>>>FORUM RULES >>>> >>>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>>Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>>>will not get quick attention or may not be answered. >>>>2) Don't post a question in the thread of another member. >>>>3) Don't post questions regarding breaking or bypassing any security >>>>measure. >>>>4) Acknowledge the responses you receive, good or bad. >>>>5) Jobs posting is not allowed. >>>>6) Sharing copyrighted material and their links is not allowed. >>>> >>>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>>members are not responsible for any loss. >>>>--- >>>>You received this message because you are subscribed to the Google Groups >>>>"MS EXCEL AND VBA MACROS" group. >>>>To unsubscribe from this group and stop receiving emails from it, send an >>>>email to excel-macros+unsubscr...@googlegroups.com. >>>>To post to this group, send email to excel-macros@googlegroups.com. >>>>Visit this group at http://groups.google.com/group/excel-macros. >>>>For more options, visit https://groups.google.com/d/optout. >>>> >>> -- >>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>https://www.facebook.com/discussexcel >>> >>>FORUM RULES >>> >>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will >>>not get quick attention or may not be answered. >>>2) Don't post a question in the thread of another member. >>>3) Don't post questions regarding breaking or bypassing any security measure. >>>4) Acknowledge the responses you receive, good or bad. >>>5) Jobs posting is not allowed. >>>6) Sharing copyrighted material and their links is not allowed. >>> >>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>members are not responsible for any loss. >>>--- >>>You received this message because you are subscribed to the Google Groups >>>"MS EXCEL AND VBA MACROS" group. >>>To unsubscribe from this group and stop receiving emails from it, send an >>>email to excel-macros+unsubscr...@googlegroups.com. >>>To post to this group, send email to excel-macros@googlegroups.com. >>>Visit this group at http://groups.google.com/group/excel-macros. >>>For more options, visit https://groups.google.com/d/optout. >>> >>> >>> >>-- >>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>https://www.facebook.com/discussexcel >> >>FORUM RULES >> >>1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>get quick attention or may not be answered. >>2) Don't post a question in the thread of another member. >>3) Don't post questions regarding breaking or bypassing any security measure. >>4) Acknowledge the responses you receive, good or bad. >>5) Jobs posting is not allowed. >>6) Sharing copyrighted material and their links is not allowed. >> >>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>members are not responsible for any loss. >>--- >>You received this message because you are subscribed to the Google Groups "MS >>EXCEL AND VBA MACROS" group. >>To unsubscribe from this group and stop receiving emails from it, send an >>email to excel-macros+unsubscr...@googlegroups.com. >>To post to this group, send email to excel-macros@googlegroups.com. >>Visit this group at http://groups.google.com/group/excel-macros. >>For more options, visit https://groups.google.com/d/optout. >> > -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
