Look up the dictionaries...
点击联系发帖人
时间：2011-09-25 21:37
求初二上册英语（上海教育出版社）语法点的复习提纲！急！_百度知道
求初二上册英语（上海教育出版社）语法点的复习提纲！急！
别人总结的永远是别人的东西这个东西还是自己总结吧。自己总结出来的才记得深，他告诉你的才是最权威的因为每个地方出的题都是不一样的，重点也不一样。提纲或者重点什么的 最好去找你们的英语老师
其他类似问题
按默认排序
其他3条回答
d! 意为“打搅了。 tell sb to do sth 意为“告诉某人去做某事”. 意为“据说”： He is singing.look after 照顾：10去上学。如.将-oo改为--ee。22) tell. 你必须照顾你的老父亲，我能在两天后完成.看
(look at=have a look at) Please look at the map of China。 2，my friend、戴”, too，即some：He doesn'！”!1。 3，一般是为了与陌生人搭话。24) 表示时间的 in. 构成疑问句，照看 You must look after your old father，例如.. talk 意为“谈话..。如? 与 Which。26) look 短语 常见的look短语有以下这些，可以用来, I can finish it in two days。如, will.“leave+地点”表示“离开某地”，过去分词.请看中国地图，also与either 1.a 用在以辅音音素开头的单词前： class---classes
dish---dishes watch---watches
box---boxes 3.，将-f或-fe变为-v, speak.：5) every day 与 everyday 1。 Father always tells interesting stories to us。即“中日瑞不变英法变，或者要打断对方所说（做）的事？19) a little？ say to 意为“对. Our English teacher is from the US。如.look up 查找 Please look up the word in the dictionary. 医生仔细检查了玛丽： 3： scarf---scarves(fes)
roof---roofs serf---serfs
gulf---gulfs chief---chiefs
proof---proofs belief---beliefs II 名词复数的不规则变化 1. in 表示时间的一段或较长的时间., but we found nothing strange,2004： We go to school at 7.;t是助动词.，有词义）2.）. 他不喜欢英语.；like是主要动词。another 修饰单数名词，例如，一般放在句尾. Their daughter makes her breakfast all by herself。 He has got married： He told me something about his past。如.，其复数形式是 the others、复数变化. stop doing sth 意为“停止做某事”。 2，-ing分词。 4。如;to do与remember doing/., is there a hotel in the neighborhood，词尾加-es。如。如! 与 I'： He said to his students that they would have a test,she. stop to do sth 意为“停下来去做某事”： he。如;m sorry： mouse---mice apple tree---apple trees man teacher---men teachers
14) 双写最后一个字母的-ing分词 初中阶段常见的有以下这些！”..最常用的助动词有.an 用于以元音音素开头的单词前, -sh，复数形式是 others： We have great mushroom pizza as well as soda，比如.a lot of (=lots of)变为many或much..
他告诉我一些他的往事. 我们每天7： foot---feet
tooth---teeth 2。如。 Do you play soccer every day.添加词尾。如, our classroom。 It'.?4) 频度副词的位置，一直是中学生朋友们比较困扰的问题，词尾加-es，一般用 talk to。 He is talking with his friend： sheep---sheep
deer---deer fish---fish
people---people 5。 3, Tom.let→letting 让 hit→hitting 打。 I 名词复数的规则变化 1： They don'.讲话..as well as也有“也”的意思： Sandra is also a Korean student, another.
他们继续玩游戏.too用于肯定句和疑问句，将-y变为-i? 我能和张先生讲话吗. say 意为“说”.。如. 与否定副词not合用, the other。如，如 others。e。 tell sb sth 意为“告知某人某事”, on 与 at 都可以和表示时间的词（组）连用. 李萍和李晶看上去一样. everyday 作定语. 明天晚上一定来参加晚会，第三人称单数形式： half---halves
leaf---leaves thief---thieves
knife---knives self---selves
wife---wives life---lives
wolf---wolves shelf---shelves
loaf---loaves 但是.travel→travel(l)ing 旅游 swim→swimming 游泳 run→running 跑步 dig→digging 挖： The students stopped talking： David told his son to do the homework，多用 with： It is said that he could stay under the water for a long time： at 8.look around 到处寻找?你来这儿之前学过英语吗. 他完成了作业、作业中经常出错, she： on Sunday
在星期天 on May Day
在“五一”节 on a hot afternoon
在一个炎热的下午 He arrived in Beijing on April 26.and变为or. 他在唱歌. Sandra 也是一个韩国学生.also用于肯定句和疑问句。 （doesn'.already变为yet.。如： go---goes---going---went---gone
work---works---working---worked---worked watch---watches---watching---watched---watched当主语为第三人称单数的时候。如，后面主要接语言。 4： They are talking about the movie。 2.:10 every day。 3. on 主要指在具体的一天. every day 作状语? 请问？ 4.？ speak of 意为“提到。如.look the same 看上去一样 Li Ping and Li Jing look the same。如.。如。如。如.“leave for+地点”表示“动身去某地”： He tells me that he wants to be a teacher, a few 与 a bit (of)20) 关于like的用法 1.look at 朝：another pencil.some变为any。如： Do you like college life。如.名词有单数名词和复数名词. 现在是星期天，所以不能和冠词连用;m sorry.助动词协助主要动词完成以下功用。如。 b，张先生。 I decide to read English every day. 加强语气. 我决定每天读英语. 他于日到达北京.一般在名词词尾加-s。23) Excuse me： 1;t know the answer,sometimes. 我们也在相同的学校;the others 强调整体中除去一部分后剩余的全部. 对不起.： He finishes his homework and goes on to study English.表示“某国人”的单，再加-es、撞 cut→cutting 切。 3;others相当于“other + 名词”： man（单数）---men（复数）
banana（单数）---bananas（复数） 3., Mr Zhang:00
在八点 at noon
在中午 I always get up at 6？6) 什么是助动词1？ Excuse me.”，所以不能充当定语： The students stop to listen to their teacher；以辅音字母开头的单词前面也不一定都用18) 如何表达英语中的“穿.其余的人;m sorry，译为“日常的”。 6.以字母-o结尾的某些名词，过去式，即 some.. 南希看起来像她母亲、讲述”。 2;s，附近有旅馆吗.其它： We are in the same school.以元音字母开头的单词前面不一定都用an., it”代替的！对不起： I don'。如, say 与 talk 1.单复数同形. speak 意为“说话.。 talk about 意为“谈论。如. 她晚饭后在电视上看日常英语。如。如;s for sb： I'。others指整体中去掉一部分后剩余的部分.，例如.
大卫要他的儿子去做作业.说”，我能说一些吗： Excuse me. 据说他能呆在水里很长时间;t do it again., do.
我总是每天早晨六点起床： Can I have a talk with you.2。 They went on playing games., his teacher： The book speaks of my hometown.，即可用“he，所有格是 other', Mary'! 意为“对不起. 我不喜欢他, any other 等.
他们在谈论那部电影，表示道歉，构成否定句，不可单独使用.
那本书提到我的家乡.。
3，译为“每一天”，如果只有一方对另一方说话时;s your everyday activity. Excuse me. 学生们停下来去听他们老师讲话。 speak to 意为“和.28) hard与hardly29) sometime。如；如果双方或多方交谈.look like 看起来像 Nancy looks like her mother。3. 表示语态： The boy wants to be a sales assistant., -x结尾的名词、掘 begin→beginning 开始 prefer→preferring
plan→planning
计划 15) 肯定句变否定句及疑问句要变化的一些词 1. 表示时态、一条信息传送给别人或讲述一件事;s Sunday。27) too.主语的第三人称单数形式. 3，后面要用名词的单数形式。如。如? 你也每天踢足球吗. 他的确知道那件事。如. 爸爸总是给我们讲有趣的故事？ 2，例如、such与不定冠词的使用 12) 英语中的“单数” 1，指某人把某一件事.协助主要动词构成谓语动词词组的词叫助动词（Auxiliary Verb）。3。2、other 指其余的人或物. at 表示时间的一点或比较短的时间。助动词自身没有词义。2) 情态动3) What.others （一些、on 与 at in，一般位于实义动词前.
每年的这个时候总是暖和的。 2，谓语动词必须用相应的第三人称单数形式. tell 意为“告诉： Chinese---Chinese
Japanese---Japanese
Swiss---Swiss Englishman---Englishmen
Frenchman---Frenchmen American---Americans
Australian---Australians Canadian---Canadians
Korean---Koreans Russian---Russians
Indian---Indians 6, -ch，接着继续去念英语;s uncle 2。如.look over 仔细检查 The doctor looked over Mary carefully、得到 sit→sitting 坐forget→forgetting 忘记put→putting 放set→setting 设置babysit→babysitting 临时受雇照顾婴儿 2. 老人在寻找他的狗?
我可以和你交谈吗.： He can speak English and a little Chinese，其它国把-s加后面”，例如? 你喜欢大学生活吗.the others。如： a。 have a talk with 意为“与。 It is said that，平常的考试、讲话”。如.请在词典中查找这个单词?
打搅一下,再加-es.
学生们停止了谈话。 1。（星期二） Rome was not built in a day, would7) forget doing/。 5、割 get→getting 取?
你能用英语再说一遍吗： potato---potatoes
tomato---tomatoes Negro---Negroes
hero---heroes 4, it.以字母-f或-fe结尾的名词： 1。如.和 It'.either用于否定句： Can I speak to Mr Zhang.。如： child---children 4.
他在和朋友交谈。如： Do come to the party tomorrow evening.动词有原形，无词义： He was sent to England、查看 We looked around. He is a happy boy as well. 他被派往英国... 他对他的学生说他们将有一个测试, should：go on to do sth “继续做某事（与刚才一事不同）”和 go on doing sth “继续做某事（与刚才同一件事）”、谈话”:00 every morning.： man---men
woman---women policeman---policemen
postman---postmen 31) leave的用法1.“leave+地点+for+地点”表示“离开某地去某地”。 He did know that，其前常加逗号.将-man改为-men、说起”. 他能讲英语和一点汉语.： 1. I won'.
请立即同他谈话。 7。如。如，但是我们没有发现奇怪的东西，修饰名词, either.交谈”。由 an 和 other 合并构成？ Did you study English t like English。如。 8。下面是它们的一些用法.shop→shopping 购物 trip→tripping 绊 stop→stopping 停止 drop→dropping 放弃
3。如.以字母-s。我不会这么做了;s always warm at this time of year.以辅音字母加-y结尾的名词.
罗马不是在一天内建起来的。 与它们相反的句式是, could I say something。 2;s of sb，但不是全部的.look for 寻找 The old man isthe other 指“两个人或物中的另一个”、any other 指除去本身以外的“任何其他的人或物”。 c，例如.10) so？ 2、be动词后。如,some time与some times32) same与different37) a lot of(lots of)与many.他告诉我说他想成为一位教师。25) Other及其用法Other 及其相近的词（组）, too.. 他已结婚： in the morning
在上午 in May. I'： Please talk to him right now： pear---pears
hamburger---hamburgers desk---desks tree---trees 2.13) 名词的复数构成的几种形式 名词复数的构成可分为规则变化和不规则变化两种;t like him、讲话”.： Can you say it in English once more：be, shall. 她们也不知道答案。如, the others, have： 4、another泛指三个以上的不定数目中的“另外一个”;to do8) It'。被协助的动词称作主要动词（Main Verb）： 16) in与after 17) 不定冠词a与an的使用 1. 我们四处查看。 2，一般放在句末., 2004
在2004年五月 in a week
在一周之内（后） It'。 She watches everyday English on TV after dinner。 What'? 你的日常活动是什么： family---families
dictionary---dictionaries city---cities
country---countries 5
推荐您一本书，初中英语教材全解，8年级上册。超级好，语法点很详细，提纲可以参照每一课的详细目录介绍。希望对您的学习有所帮助。千里之行始于足下！梅花香自苦寒来！共勉。
『七年级英语』七年级下学期英语Unit4-Six复习练习 ·五,将下列句子译成英语:(20) 1, 她的笔友Sam来自美国. __ 2,Tom 在日本有许多笔友. __ 3,
上海教育出版社的相关知识
您可能关注的推广
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁retentiveable to store facts&
NEW for 2015
Welcome to our new-look website, home of the Oxford Advanced Learner’s Dictionary, 9th edition!
What’s new?
900 new words, for example
new usage notes, for example
new features, like
Get Premium
Upgrade to Premium to remove advertising and to access NEW Premium features including:
My Wordlists
Resources for teachers and learners
Build your topic vocabulary with our mini-dictionaries: groups of words related to common subject areas, such as "Cooking" and "Technology"
The man who made dictionaries
Did you know that the first Oxford Advanced Learner's Dictionary was written by a teacher in Japan?
Hints, tips and resources for English language teachers and students, for example... Top tips every EFL student should know when using a learner’s dictionary
It’s your Oxford Advanced Learner’s Dictionary
Find out what’s new about the Oxford Advanced Learner’s Dictionary, 9th edition, and how you can buy&it
retentiveable to store facts&VBA for smarties: Dictionaries
Dictionaries
A Dictionary in VBA is a collectionobject: you can store all kinds of things in it: numbers, texts, dates, arrays, ranges, variables and objects.
Every item in a Dictionary gets its own unique key.
With that key you can get direct access to the item (reading/writing/adapting).
VBA has several methods to store data:
- a Dictionary
- a Collection
- an array (matrix) variable
- an ActiveX ComboBox
- an ActiveX ListBox
- a Userform control ComboBox
- a Userform control ListBox
- a sortedlist
- an arraylist
Which one to use is dependent of your ultimate goal.
This tutorial doesn't offer an exhaustive comparison of all these methods.
What a Dictionary has to offer will be discussed in detail.
With that knowledge it's easier to compare different methods and to make a choice between them.
An important chracteristic of a Dictionary is the use of unique keys.
It's not necessary to look for an item using a loop or by applying the Excelfunction application.match.
That's an advantage of a Dictionary compared to an array/matrix-variable.
The way in which the Dictionary stores items is comparable to the Collection object.
The Dictionary has some properties/functions/methods like .keys, .items and .removeall, that can be advantageous compared to the use of a collection.
So the preference for a Dictionary over a matrix variable or Collection is dependent of what you want to use it for.
You can group all kinds of data in a Dictionary to get easy and quick access to them since they are temporarily stored in memory.
Instead of manipulating data in an Excel-worksheet, a Word Document, or a Powerpoint presentation on the hard disk, you can do that in memory.
No screenrefreshing, no autocalculation, no background backup is necessary, so it will speed up your code considerably.
Generally speaking you can use a Dictionary to store/group data that have a common property (key).
Scattered data can be integrated easily.
A Dictionary can only contain unique keys. That's why you can use the property .keys to create a list of unique strings, numbers or dates.
Although the Dictionary has not been designed for that purpose it's a nice side effect.
The unique key their order matches the order in which they have been added to the Dictionary.
If you need a sorted list of unique keys you will have to sort the data before adding them to a Dictionary, or you need to use methods from another VBA library (e.g. system.arraylist or system.orderedlist)
You might need unique lists e.g. as validation list in Excel, the contents of an ActiveX-control (combobox or listbox) or a Userform control (combobox or listbox).
The Dictionary isn't part of the standard VBA library.
It's an element in the Microsoft Scripting Runtime library.
That library is in the file ..\Windows\system32\scrrun.DLL or in a similar directory
In the VBEditor you can make a reference to this library by enabling 'Microsoft Scripting Runtime' (Menu / Tools / References..)
The reference will be stored in the workbook. If you distirbute the workbook the reference will be distributed simultaneously.
There are several methods to do so.
First I'll discuss what you can use a Dictionary for. In the last part (XXII) I will explain the several methods to create a Dictionary into detail.
For simplicity's sake
until then I will only use 1 method to illustrate the Dictionary.
It's the simplest and least accident prone.
To create a Dictionary this code suffices
With CreateObject("scripting.Dictionary")
It reads as: create a new object based on the scripting.Dictionary library.
All instructions / properties starting with a dot . between With ... End With refer to the newly created Dictionary
With CreateObject("scripting.dictionary")
.Add "first name", "contents"
The method .Add adds a new item to the Dictionary wtih the key "first name"; the item contains the string "contents"
With CreateObject("scripting.dictionary")
MsgBox .Count
The message box shows the property .Count of the Dictionary: i.e. the number of items in the Dictionary.
A Dictionary can only be populated one by one.
To do so you need the contents of an item and a unique key.
The contents can be anything: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing and objects
A key can consist of a number, string, date or object, or a variable containing a number, string, date or object
An array can't be used as a key (nor a 1-dimensional array, nor a multidimensional array).
For simplicity's sake
I will only use strings as keys to prevent confusion between keys and index numbers.
There are 4 different methods to add an item to a Dictionary
method .Add
With CreateObject("scripting.dictionary")
.Add "first", "content"
Add an item to the Dictionary; its content is the string "contents", its key is the string "first"
method .Item( )=
With CreateObject("scripting.dictionary")
.Item("first") = "content"
Add an item to the Dictionary; its content is the string "contents", its key is the string "first"
method =.Item()
With CreateObject("scripting.dictionary")
x0 = .Item("first")
Put the contents of
Item("first") into variable x0
If the item with this key doesn't exist the item will be added to the Dictionary, using the indicated key.
No contents will be assigned to the item.
method using an objectvariable
Assign a Dictionary to an objectvariable
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb("first") = Date
Add an item to the Dictionary: its content is a Date and its key is "first"
Written alternatively:
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb.Item("first") = Date
The key doesn't exist in the Dictionary
If a key doesn't exist a new one is being created and the item will be added.
All methods (.Add,
.Item()=, =Item() and the use of an objectvariable()= ) do this identically.
The key exists in the Dictionary
method .Add
If the keys exists in the Dictionary and if you use the method .Add to add an item to the same key the code errors out.
This is similar to the use of a Collection
With CreateObject("scripting.dictionary")
.Add "first", Date
.Add "first", "new"
method .Item()=
If the key exists the corresponding item will be overwritten/replaced.
Contrary to the method .Add no error is being reported.
With CreateObject("scripting.dictionary")
.Item("first") = Date
MsgBox .Item("first")
.Item("first") = "new"
MsgBox .Item("first")
method =.Item()
The item's content will be read into a variable. Whether the item is empty or not doesn't make a difference.
If the key exists no change will take place in the Dictionary.
With CreateObject("scripting.dictionary")
x0 = .Item("first")
method using an objectvariable
If a key exists the corresponding item will be replaced.
Contrary to the method .Add no error is being reported.
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb("first") = Date
MsgBox dict_snb("first")
dict_snb("first") = "new"
MsgBox dict_snb("first")
Aternative syntax:
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb.Item("first") = Date
MsgBox dict_snb.Item("first")
dict_snb.Item("first") = "new"
MsgBox dict_snb.Item("first")
New keys will be added in the order of the adding process. No other order in keys or items will be made.
A key can consist of a string, a number, a date or an object.
A key can also consist of a variable that is a string, a number, a date, or an object.
A Dictionary can contain several different kinds of keys (string, number, date, object).
Several different keys illustrated
string key
With CreateObject("scripting.dictionary")
.Items("aa1") = "example 1 "
.Add "aa2", "example 2"
x = .Item("aa3")
dict_snb("aa4") = "example 3"
number key
With CreateObject("scripting.dictionary")
.Items(2) = "example 4"
.Items(1234589) = "example 5"
.Add 23, "example 6"
x = .Item(45)
dict_snb(56788) = "example 7"
With CreateObject("scripting.dictionary")
.Items(Date) = "example 8"
.Items(CDate("12-03-2013")) = "example 9"
.Add DateAdd("m", 2, 1), "example 10"
x = .Item(DateSerial())
dict_snb(56788) = "example 11"
object key
With CreateObject("scripting.dictionary")
.Items(TextBox1) = "example 12"
.Items(TextBox2) = "example 13"
.Add ListBox1, "example 14"
x = .Item(Sheet1)
dict_snb(ComboBox1) = "example 15"
ActiveX-controls in a worksheet
With CreateObject("scripting.dictionary")
For Each it In Sheets("sheet1").OLEObjects
.Item(it) = it.Name
Several kinds of keys in 1 Dictionary
With CreateObject("scripting.dictionary")
For Each it In Array("bb1", 12, 45673, Date + 10, sheet4)
.Item(it) = "Item 1" & .Count
There are 2 options:
a distinction between low
ToDay is not identical to the key today
no distinction between low ToDay is identical to the key today
The property .comparemode indicates the way keys are being compared.
.comparemode unspecified: lowercase and uppercase are relevant
.comparemode = BinaryCompare (0) : lowercase and uppercase are relevant
.comparemode = TextCompare (1) : no relevance to lowercase or uppercase.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
y = .Item(it)
MsgBox .Count'
unique keys
MsgBox Join(.Keys, vbLf)
With CreateObject("scripting.dictionary")
.CompareMode = 0
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
y = .Item(it)
MsgBox .Count' 8
unique keys
MsgBox Join(.Keys, vbLf)
With CreateObject("scripting.dictionary")
.CompareMode = 1
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
y = .Item(it)
MsgBox .Count'
unique keys
MsgBox Join(.Keys, vbLf)
Sometimes all items have to be added to a Dictionary.
It doesn't matter in that case which key will be used.
It's necessary to generate a unique key for each item automatically.
You can use the property .count of the Dictionary to create these unique keys.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(.Count) = it & "_contents"
Examples when all items need to be added to the Dictionary:
to convert a string into a 2-dimensional array: Example 23.9
to group all ActiveX controls: Example 23.7
By adding items to a Dictionary you simultaneously create a list of unique keys.
So you can use this adding process to create a list of unique values.
It's not even necessary to add any content to items to create such a list.
The method =Item("key") does that.
If the key doesn't exist it wil be added, if it exists nothing will happen.
This method triggers no errors, you should deal with.
With CreateObject("scripting.dictionary")
For Each it In Array(22, 33, 44, 22, 3, 22, 55, 44)
y = .Item(it)
MsgBox .Count
5 unique keys because of the replication of 44 and 22
MsgBox Join(.Keys, vbLf)
Items will be added sequentially. No ordering of keys or items will take place.
The contents of items can be: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing or objects
As illustration of items with varying contents.
Only a string will be used as key.
an empty item
With CreateObject("scripting.dictionary")
x0 = .Item("aa1")
an empty string
With CreateObject("scripting.dictionary")
.Item("aa2") = vbNullString
.Item("aa3") = ""
.Add "aa4", ""
dict_snb("aa5") = vbNullString
With CreateObject("scripting.dictionary")
.Item("aa6") = "abcde"
.Add "aa7", "abcde"
dict_snb("aa8") = "abcde"
a non-printable character
With CreateObject("scripting.dictionary")
.Item("aa9") = vbTab
Add "aa10", vbLf
dict_snb("aa11") = vbCrLf
With CreateObject("scripting.dictionary")
.Item("aa12") = 12345'
typename: Integer
.Add "aa13" = 1234589'
typename: Long
dict_snb("aa14") = RGB(23, 45, 678) '
typename: Long
a date (typename: Date)
With CreateObject("scripting.dictionary")
.Item("aa15") = Date
.Add "aa16", CDate("23-04-2012")
dict_snb("aa17") = DateSerial()
a 1-dimensional array (typename: Variant())
With CreateObject("scripting.dictionary")
.Item("aa18") = Array("aa1", "aa2", "aa3")
Add "aa19", Split("bb1_cc1_dd1", "_")
dict_snb("aa20") = Array("aa1", "aa2", "aa3")
a multi-dimensional array (typename: Variant())
With CreateObject("scripting.dictionary")
ReDim sn(6, 10)
.Item("aa21") = sn
.Add "aa22", Range("A1:K10")
dict_snb("aa23") = Range("A1:K10").Formula
The method .Add puts the object into the Dictionary directly.
The methods
objectvariable=) need the instruction 'Set' to assign the object to the Dictionary item.
Otherwise the object's default property would be the content of the Dictionary item.
With CreateObject("scripting.dictionary")
Set .Items("aa24") = Range("A1:K10")'
typename: Range
Set dict_snb("aa25") = Range("A1:K10")
.Add "aa26", Range("A1:K10")
userformcontrols
With CreateObject("scripting.dictionary")
For Each it In Controls
Set .Item(it.Name) = it
.Add it.name& "_", it
ActiveX-controls
With CreateObject("scripting.dictionary")
For Each it In Sheets("sheet1").OLEObjects
Set .Item(it.Name) = it
.Add it.name & "_", it
all sheets in a workbook
With CreateObject("scripting.dictionary")
For Each sh In Sheets
Set .Item(sh.Name) = sh
.Add sh.Name & "_", sh
Set dict_snb(sh.Name & "_#") = sh
A User Defined Type (UDT)k
You can store a User Defined Type (UDT) in an array.
For instance
Public Type translation
dutch As String
german As String
french As String
italian As String
Sub filling_type_array()
ReDim sn(2) As translation
For j = 0 To 2
sn(j).dutch = Choose(j + 1, "tafel", "wijn", "water")
sn(j).german = Choose(j + 1, "Tisch", "Wein", "Wasser")
sn(j).french = Choose(j + 1, "table", "vin", "eau")
sn(j).italian = Choose(j + 1, "tavola", "vino", "aqua")
MsgBox sn(2).german & " = " & sn(2).italian
For each array item you can retrieve the data of the type using:
For j=0 to ubound(sn)
MsgBox sn(j).german & " = " & sn(j).italian
You can't put a
UDT into a Dictionary (nor in a
collection).
Dim woord As translation
With CreateObject("scripting.dictionary")
.Add "overzicht", woord
Will return an error code.
With the use of another method we can accomplish an analogous result:
create a classmodule (e.g named 'trans')
declare the properties of the class:
Public german As String, french As String, italian As String
- create new in assign values to their properties and store these instances in a Dictionary.
For illustration purposes 3 different methods to do so:
NB. the Dutch words serve as
keys in the Dictionary.
Sub type_class_in_Dictionary()
With CreateObject("scripting.dictionary")
Set sn = New trans
sn.german = "Tisch"
sn.french = "table"
sn.italian = "tavola"
.Add "tafel", sn
Set .Item("wijn") = New trans
.Item("wijn").german = "Wein"
.Item("wijn").french = "vin"
.Item("wijn").italian = "vino"
.Add "water", New trans
.Item("water").german = "Wasser"
.Item("water").french = "eau"
.Item("water").italian = "aqua"
MsgBox .Item("tafel").french
MsgBox .Item("wijn").italian
The Dictionary keeps track of the amount of elements.
The property .Count indicates the number of items in a Dictionary.
Since every item has its own unique key the property .Count also indicates the amount of unique keys.
The function .Items contains all items in the Dictionary, represented by a 1-dimensional array.
The array's property Ubound() indicates the number of elements in an array.
Since the lower limit of a 1-dimensional array is 0, Ubound(.items)+1 also represents the number of elements in a Dictionary.
The function .Keys contains all keys in the Dictionary, represented by a 1-dimensional array.
The array's property Ubound() indicates the number of elements in an array.
Since the lower limit of a 1-dimensional array is 0, Ubound(.Keys)+1 also represents the number of elements in a Dictionary.
Equivalent methods to determine the size of a Dictionary:
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
y = .Item(it)
MsgBox .Count
MsgBox UBound(.Keys) + 1
MsgBox UBound(.Items) + 1
If you add an item to a Dictionary using the method .Add and applying an existing key the code will error out.
To prevent this error you can check the key's existence in the Dictionary.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
If Not .Exists(it) Then .Add it, it & "_content"
If you use the method .Item =
to add an item to a Dictionary no error message pops up if the keys already exists, the existing item will be replaced.
If that's not what you want you can check the key's existence.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
If Not .Exists(it) Then .Item(it) = it & "_content"
If you want to change the content of an item in the Dictionary it can be very practical to check its existence beforehand.
If the key doesn't exist it will be created automatically when using this code to read the item's content.
y = .Item("first")
If you want to prevent the adding of empty items to the Dictionary checking is required:
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
If .Exists("aa2") Then .Item("aa2") = "new value"
The unique key
A Dictionary has been designed to access (reading /writing) an item directly by key.
Using .item("aa2") you get the contents of the item stored with the key "aa2"
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
MsgBox .Item("aa2")
The property .Item has only the key as argument.
The property .Item has no indexnumbers
Use the item's indexnumber
In this case you will have to use the function .Items.
The function .Items is a 1-dimensional array.
Although in VBA you can access a 1-dimensional array directly using c00=array(2) it seems the function .Items has to be converted to a 'normal' VBA array first.
The simplest way to do so is adding () after .Items: .Items()
In Excel you can translate the function .Items to an array using Application.Index.
In VBA you can use the method Filter.
NB. If you use the method Filter all values will be converted into strings.
If you read the function .items into an array
variable or translate it into an array you can use the index of that variable/ array respectively.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
MsgBox .items()(0) '
the first item
MsgBox Application.Index(.Items, 1, 1)
MsgBox Filter(.Items, "")(0)
MsgBox Filter(.Items, "")(LBound(.Keys))
MsgBox .items()(0) '
the second item
MsgBox Application.Index(.Items, 1, 2)
MsgBox Filter(.Items, "")(1)
MsgBox .items()(.Count-1) ' the last item
MsgBox Application.Index(.Items, 1, .Count)
MsgBox Filter(.Items, "")(UBound(.Keys))
MsgBox Filter(.Items, "")(.Count - 1)
sp = .Items '
with an array-variable
MsgBox sp(0) '
the first item
MsgBox sp(1) ' the second item
MsgBox sp(UBound(sp)) '
the last item
Use the key's indexnumber
In this case you will have to use the function .Keys.
The function .Keys is a 1-dimensional array.
Although in VBA you can access a 1-dimensional array directly using c00 = array(2) it appears the function .Keys has to be converted to a 'normal' VBA array first.
The simplest way to do so is adding () after .Keys: .Keys()
In Excel you can translate the function .Keys to an array using Application.Index.
In VBA you can use the method Filter (NB. If you use the method Filter all values will be converted into strings).
If you read the function .Keys into an array variable or translate it into an array you can use the index of that variable/ array respectively.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_contents"
MsgBox .keys()(0) '
the first key
MsgBox Application.Index(.Keys, 1, 1)
MsgBox Filter(.Keys, "")(0)
MsgBox Filter(.Keys, "")(LBound(.Keys))
MsgBox ,Keys()(1) '
the second key
MsgBox Application.Index(.Keys, 1, 2)
MsgBox Filter(.Keys, "")(1)
Msgbox .Keys()(.Count-1) ' the last key
MsgBox Application.Index(.Keys, 1, .Count)
MsgBox Filter(.Keys, "")(UBound(.Keys))
MsgBox Filter(.Keys, "")(.Count - 1)
MsgBox .Item(Application.Index(.Keys, 1, 1)) '
the item with the first key
MsgBox .Item(Filter(.Keys, "")(0))
MsgBox .Item(Application.Index(.Keys, 1, 2)) '
the item with the second key
MsgBox .Item(Filter(.Keys, "")(1))
sn = .Keys'
the use of an array variable
MsgBox sn(0)
' the first key
MsgBox sn(1)
the second key
MsgBox sn(UBound(sn))
MsgBox .Item(sn(0))
' the item with the first key
MsgBox .Item(sn(1))
MsgBox .Item(sn(UBound(sn)))
Loop all keys in the Dictionary and check a condition
Using the function .Keys that contains all keys in a 1-dimensional array
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
For Each it In .Keys
If it = "aa4" Then MsgBox .Item(it)
Using an object variable:
set dict_snb as CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
dict_snb.Item(it) = it & "_content"
For Each it In dict_snb
If it = "aa4" Then MsgBox .Item(it)
Filter the keys in the Dictionary
We can use the function .Keys that contains all keys in a 1-dimensional array
With CreateObject("scripting.dictionary")
For Each it In Array("aa14", "bb345", "cc392", "rrr987")
.Item(it) = it & "_content"
for each it in Filter(.Keys, "cc")
MsgBox .Item(it)
Write all keys to a worksheet
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
Cells(1, 1).Resize(, .Count) = .Keys
' in a row
Cells(1, 1).Resize(.Count) = Application.Transpose(.Keys) ' in a column
Put all keys into a variable
The variable is an array variable with lower bound (lbound) 0
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
sn = .Keys
Put all keys into a
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_contents"
MsgBox Join(.Keys, vbLf)
Do calculations with the keys
Many Excel worksheetformulae can be applied to the array .keys if the keys consist of numbers.
the highest keynumber
MsgBox Application.Max(.Keys)
the lowest keynumber
MsgBox Application.Min(.Keys)
the third highest
MsgBox Application.Large(.Keys, 3)
Filter keys on a certain condition
E.g. filter the year 2012
The method 'filter' converts all keys to strings.
So the filtering condition has to be a string too.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", DateSerial(), DateSerial(), DateSerial(), 22, DateSerial(), 44)
.Item(it) = it & "_content"
MsgBox Join(Filter(.Keys, "-2012"), vbLf)
If you want to use the filtered keys to retrieve the corresponding items you will have to reconvert the keys to their original type.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", DateSerial(), DateSerial(), DateSerial(), 22, DateSerial(), 44)
.Item(it) = it & "content"
For Each it In Filter(.Keys, "-2012")
MsgBox .Item(CDate(it))
Write the array to a worksheet
NB. This requires the items to be numbers, strings or dates.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
Cells(1, 1).Resize(, .Count) = .Items'
Cells(1, 1).Resize(.Count) = Application.Transpose(.Items) '
in a column
Put all items into a variable
The variable is an array variable with lower bound(lbound) 0
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
sn = .Items
Put all items into a string
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", 22, 3, 22, DateSerial(), 44)
.Item(it) = it & "_content"
MsgBox Join(.Items, vbLf)
Do calculations on the items
Many Excel worksheetformulae are applicable to the array .Items if the items contain numbers.
the highest item
MsgBox Application.Max(.Items)
the smallest item
MsgBox Application.Min(.Items)
the third largest item
MsgBox Application.Large(.Items, 3)
Filter items on a certain condition
Filter the year 2012
Since the method Filter converts all items to text the filtercondition also has to be a string.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", DateSerial(), DateSerial(), DateSerial(), 22, DateSerial(), 44)
.Item(it) = it & "_content"
MsgBox Join(Filter(.Items, "-2012"), vbLf)
You can also consider this as a 'replacement' of an item in the Dictionary.
With CreateObject("scripting.dictionary")
.Item("aa") = "this is the first item"
MsgBox .Item("aa")
.Key("aa") = "bb"
MsgBox .Item("bb")
You can connect an item to another / new key.
With CreateObject("scripting.dictionary")
.Item("aa") = "The first item"
.Item("bb") = .Item("aa")
MsgBox .Item("bb")
You need to use the key of an item
to remove it with this method.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
.Remove "aa3"
Replace the contents of an item in the Dictionary
The method .Item()=
replaces an existing item by a new one with the same key.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")
.Item(it) = it & "_content"
For Each it In Array("aa1", "aa2", "aa5", "aa6")
.Item(it) = it & "_new content"
MsgBox Join(.Items, "|")
If you sort the items before adding them to a Dictionary you can restrict the items to the most/least recent item, or the item containing the maximum / minimum value.
Since every item is overwritten, only the last item in the sorting order will be kept.
A prerequisite is a sortable property.
See example 23.2 and 23,3.
Add data to items in the Dictionary
This method applies only to items that contain single numbers, strings or dates.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = "aa"
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = .Item(it) & "_bb"
MsgBox Join(.Items, "|")
Perform calculations
This method applies only to items that contain single numbers, strings, dates or booleans.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = 10
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = .Item(it) + 40
MsgBox Join(.Items, "|")
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4")
.Item(it) = Date + .Count
For Each it In Array("aa1", "aa2", "aa3", "aa4")
.Item(it) = DateAdd("m", 1, .Item(it))
MsgBox Join(.Items, vbLf)
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
.Item(it) = False
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
.Item(it) = Format(.Item(it), "Yes/No")
MsgBox Join(.Items, "|")
Change an item that contains an array
a 1-dimensional array
To read an item that contains an array:
With CreateObject("scripting.dictionary")
.Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
MsgBox .Item("aa")(3)
I couldn't find a method to adapt an element in an array of a Dictionary Item directly.
Although the following code doesn't error out, the 4th element in the array isn't been changed by the line .Item("aa")(3) = "the fourth item is " & .Item("aa")(3).
With CreateObject("scripting.dictionary")
.Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
MsgBox .Item("aa")(3)
.Item("aa")(3) = "the fourth item is " & .Item("aa")(3)
MsgBox .Item("aa")(3)
You can use a workaround:
- read the array into a variable
- change one or more
elements of this array variable
- replace the Dictionary item's content by the array variable
With CreateObject("scripting.dictionary")
.Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
sn = .Item("aa")
sn(3) = "the fourth element is " & sn(3)
.Item("aa") = sn
MsgBox .Item("aa")(3)
a 2-dimensional array
To read an element from a 2-dimensional array in a Dictionary Item:
With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)
sn(j, jj) = j + 5 * jj
.Item("aa") = sn
MsgBox .Item("aa")(2, 3)
I couldn't find a method to adapt an element in an array of a Dictionary Item directly.
With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)
sn(j, jj) = j + 5 * jj
.Item("aa") = sn
MsgBox .Item("aa")(2, 3)
.Item("aa")(2, 3) = 10 * .Item("aa")(2, 3)
MsgBox .Item("aa")(2, 3)
You can use a workaround
- read the array into a variable
- change one or more elements in this array variable
- replace the Dictionary item's content by this array variable
With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)
sn(j, jj) = j + 5 * jj
.Item("aa") = sn
MsgBox .Item("aa")(2, 3)
sp = .Item("aa")
sp(2, 3) = 10 * sp(2, 3)
.Item("aa") = sp
MsgBox .Item("aa")(2, 3)
With CreateObject("scripting.dictionary")
For Each it In Array("2", "33", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
.RemoveAll
Dictionary isn't part of the standard VBA library.
It's in the Microsoft scripting runtime library
It means you have to make a connection to the library in order to be able to use its methods.
The technical term for that connection is 'binding'
establish the connection to the library before using its code: early binding
or to connect only if you need the code: late binding
Late binding
Until now I only used late binding.
That means you create a new object (alternatively called 'instance'), based on a library using the method CreateObject.
The reference to the several VBA libraries isn't very well documented. In the case of a Dictionary it is:
Createobject("scripting.dictionary")
This code performs 2 actions:
it makes a connection to the library Microsoft Scripting Runtime
it creates an new object (instance) based on this library.
Both methods (early and late binding) are equivalents. Your preference determines which method to use.
You need to create a new object to be able to work with it.
implicit object
You can make an implicit object using With.... End With
With CreateObject("scripting.dictionary")
.Add "aa1", "today"
22.1.2 object variable
Or you can define an object variable
Set d_snb_ = CreateObject("scripting.dictionary")
d_snb.Add "aa1", "today"
early binding
The Dictionary is part of the Microsoft Scripting Runtime library
You can make the conection manually in the VBEditor (Alt-F11):
VBEditor/tools/references: check
'Microsoft Scripting runtime'
You could also use a macro:
Sub M_link_ref_scripting()
ThisWorkbook.VBProject.References.AddFromFile "C:\windows\system32\scrrun.dll"
This connection will be stored in the Excel file.
If you distribute the workbook the file will connect automatically to the library if the other system also contains Office.
We need to create a new object (instance), based on the library to be able to work with it.
early binding using an implicit object
Make an implicit object using With ... End With
With New Dictionary
.Add "aa1", "today"
22.2.2. early binding using an objectvariable
The instruction 'Set' is required to assign an object to a variable.
Set d_snb = New Dictionary
d_snb.Add "aa1", "today"
22.2.3.1 early binding using a declared private objectvariable
You can assign a new instance to the objectvariable in the declaration part of a codemodule.
(e.g. the workbook, a worksheet, a userform or a macromodule).
Private d_snb As New Dictionary
Having done that you can use the objectvariable in every procedure in this codemodule.
d_snb.Add "aa1", "PPP"
22.2.3.2 early binding using a declared public objectvariable
If you need the objectvariable to be accessible in the whole project (workbook) you will have to declare it in a macromodule
Public d_snb As New Dictionary
In the declaration part:
Public d_snb As Dictionary
In a macro:
Set d_snb = New Dictionary
23.1 Delete duplicate rows/arrays
Column A contains the record keys, some of which are duplicates.
Columns B:F etc,
contain the data of the records.
Sub M_delete_duplicates()
sn = Sheets("Sheet1").Cells(1).CurrentRegion.resize(,5)
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Filter records containing the highest values in column B
Column A contains the record keys.
Column A contains then record keys, some of which are duplicates.
Columns B:F ..... contain the data of the records.
Column B contains the data that can be sorted.
Sub M_filter_records_highest_date_in_columnB()
Sheets("Sheet1").Cells(1).CurrentRegion.Sort Sheets("Sheet1").Cells(1, 2)
sn = Sheets("Sheet1").Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Filter records that contain the smallest value in Column B
Column A contains the record keys, some of which are duplicates.
Columns B: ..... contain the data of the records.
Column B contains the data to be sorted.
Sub M_filter_records_smallestvalue_in_columnB()
Sheets("Sheet1").Cells(1).CurrentRegion.Sort Sheets("Sheet1").Cells(1, 2),2
sn = Sheets("Sheet1").Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
records : adding new data
Column A contains the keys of the records.
Column B contains data of the records.
Column E contains (some) keys of the same records as those in column A.
Column F contains data that have to be added to the records in column A.
Sub M_records_aanvullen()
sn = Sheets("Sheet1").Cells(1).CurrentRegion.Resize(, 3)
sp = Sheets("sheet1").Cells(1, 5).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
For j = 1 To UBound(sp)
st = .Item(sp(j, 1))
st(3) = sp(j, 2)
.Item(sn(j, 1)) = Application.Index(st, 0, 0)
Sheets("Sheet1").Cells(1, 10).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Integrate several worksheets into one
Sub M_integration()
With createobject("scripting.dictionary")
For each sh in Sheets
.item(sh.name)=sh.usedrange
Sheets.Add.Name="total"
For each it in .items
sheets("total").cells(rows.count,1).end(xlup).offset(1).resize(ubound(it),ubound(it,2))=it
Integrate data from several csv files
You will have to adapt the path G:\OF\
Sub M_integratie_csv()
sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\OF\*.csv"" /b").stdout.readall, vbCrLf)
With CreateObject("scripting.dictionary")
For j = 0 To UBound(sn)
.Item(sn(j)) = GetObject("G:\OF\" & sn(j)).Sheets(1).UsedRange.Value
GetObject("G:\OF\" & sn(j)).Close False
Sheets.Add.Name = "total"
For Each it In .items
Sheets("total").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(it), UBound(it, 2)) = it
23.7 Create a list of unique elements
The adding of items to a Dictionary creates a list of unique keys.
It's not even necessary that the items have any content.
In that case you need the method x0=.Item(key).
If the key doesn't exist, t if it exists nothing happens at all.
The method also doesn't produce any error codes.
You can use the unique elements list as validation list (Excel), or to populate a combobox or listbox.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa2", "aa2", "aa4", "aa5")
y = .Item(it)
Sheets("sheet1").Cells(1, 10).Validation.Add 3, , , Join(.Keys, ",")
Sheets("sheet1").OLEObjects("Combobox1").Object.List = .Keys
Sheets("sheet1").ListBox1.List = .Keys
In a userform:
Private Sub Userform_initialize()
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa2", "aa2", "aa4", "aa5")
y = .Item(it)
ComboBox1.List = .Keys
ListBox1.List = .Keys
Me("ComboBox2").List = .Keys
Me("ListBox2").List = .Keys
Controls("ComboBox3").List = .Keys
Controls("Listbox3").List = .Keys
check whether all ActiveX controls have some data
Make the ActiveX commandbutton visible if all ActiveX controls are not empty.
Private dict As Dictionary
Sub M_vul()
Set dict = CreateObject("scripting.dictionary")
For Each it In Array("TextBox1", "TextBox2", "textbox3", "combobox4", "combobox5")
Set dict(it) = Sheets("sheet1").OLEObjects(it)
Private Sub Textbox1_change()
M_controle
Private Sub M_controle()
For Each it In dict.Items
y = y * (it.Object.Value
Sheets("sheet1").CommandButton1.Visible = y
check the existence of a worksheet
In a macromodule:
Public dict_sheets As Dictionary
Sub M_sheet_exists()
Set dict_sheets = CreateObject("scripting.dictionary")
For Each it In Sheets
x0 = dict_sheets(it.name)
Now you can use everywhere in the VBproject (Workbook):
Sub M_continue()
If Not dict_sheets.Exists("Sheet10") Then Sheets.Add.Name = "Sheet10"
convert a string into a 2-dimensional array
With CreateObject("scripting.dictionary")
For Each it In Split("aa1_aa2_aa3_aa4_aa5|bb1_bb2_bb3_bb4_bb5|cc1_cc2_cc3_cc4_cc5", "|")
.Item(.Count) = Application.Index(Split(it, "_"), 1, 0)
Cells(10, 1).Resize(.Count, UBound(.Item(1))) = Application.Index(.Items, 0, 0)}
求初二上册英语（上海教育出版社）语法点的复习提纲！急！
别人总结的永远是别人的东西这个东西还是自己总结吧。自己总结出来的才记得深，他告诉你的才是最权威的因为每个地方出的题都是不一样的，重点也不一样。提纲或者重点什么的 最好去找你们的英语老师
其他类似问题
按默认排序
其他3条回答
d! 意为“打搅了。 tell sb to do sth 意为“告诉某人去做某事”. 意为“据说”： He is singing.look after 照顾：10去上学。如.将-oo改为--ee。22) tell. 你必须照顾你的老父亲，我能在两天后完成.看
(look at=have a look at) Please look at the map of China。 2，my friend、戴”, too，即some：He doesn'！”!1。 3，一般是为了与陌生人搭话。24) 表示时间的 in. 构成疑问句，照看 You must look after your old father，例如.. talk 意为“谈话..。如? 与 Which。26) look 短语 常见的look短语有以下这些，可以用来, I can finish it in two days。如, will.“leave+地点”表示“离开某地”，过去分词.请看中国地图，also与either 1.a 用在以辅音音素开头的单词前： class---classes
dish---dishes watch---watches
box---boxes 3.，将-f或-fe变为-v, speak.：5) every day 与 everyday 1。 Father always tells interesting stories to us。即“中日瑞不变英法变，或者要打断对方所说（做）的事？19) a little？ say to 意为“对. Our English teacher is from the US。如.look up 查找 Please look up the word in the dictionary. 医生仔细检查了玛丽： 3： scarf---scarves(fes)
roof---roofs serf---serfs
gulf---gulfs chief---chiefs
proof---proofs belief---beliefs II 名词复数的不规则变化 1. in 表示时间的一段或较长的时间., but we found nothing strange,2004： We go to school at 7.;t是助动词.，有词义）2.）. 他不喜欢英语.；like是主要动词。another 修饰单数名词，例如，一般放在句尾. Their daughter makes her breakfast all by herself。 He has got married： He told me something about his past。如.，其复数形式是 the others、复数变化. stop doing sth 意为“停止做某事”。 2，-ing分词。 4。如;to do与remember doing/., is there a hotel in the neighborhood，词尾加-es。如。如! 与 I'： He said to his students that they would have a test,she. stop to do sth 意为“停下来去做某事”： he。如;m sorry： mouse---mice apple tree---apple trees man teacher---men teachers
14) 双写最后一个字母的-ing分词 初中阶段常见的有以下这些！”..最常用的助动词有.an 用于以元音音素开头的单词前, -sh，复数形式是 others： We have great mushroom pizza as well as soda，比如.a lot of (=lots of)变为many或much..
他告诉我一些他的往事. 我们每天7： foot---feet
tooth---teeth 2。如。 Do you play soccer every day.添加词尾。如, our classroom。 It'.?4) 频度副词的位置，一直是中学生朋友们比较困扰的问题，词尾加-es，一般用 talk to。 He is talking with his friend： sheep---sheep
deer---deer fish---fish
people---people 5。 3, Tom.let→letting 让 hit→hitting 打。 I 名词复数的规则变化 1： They don'.讲话..as well as也有“也”的意思： Sandra is also a Korean student, another.
他们继续玩游戏.too用于肯定句和疑问句，将-y变为-i? 我能和张先生讲话吗. say 意为“说”.。如. 与否定副词not合用, the other。如，如 others。e。 tell sb sth 意为“告知某人某事”, on 与 at 都可以和表示时间的词（组）连用. 李萍和李晶看上去一样. everyday 作定语. 明天晚上一定来参加晚会，第三人称单数形式： half---halves
leaf---leaves thief---thieves
knife---knives self---selves
wife---wives life---lives
wolf---wolves shelf---shelves
loaf---loaves 但是.travel→travel(l)ing 旅游 swim→swimming 游泳 run→running 跑步 dig→digging 挖： The students stopped talking： David told his son to do the homework，多用 with： It is said that he could stay under the water for a long time： at 8.look around 到处寻找?你来这儿之前学过英语吗. 他完成了作业、作业中经常出错, she： on Sunday
在星期天 on May Day
在“五一”节 on a hot afternoon
在一个炎热的下午 He arrived in Beijing on April 26.and变为or. 他在唱歌. Sandra 也是一个韩国学生.also用于肯定句和疑问句。 （doesn'.already变为yet.。如： go---goes---going---went---gone
work---works---working---worked---worked watch---watches---watching---watched---watched当主语为第三人称单数的时候。如，后面主要接语言。 4： They are talking about the movie。 2.:10 every day。 3. on 主要指在具体的一天. every day 作状语? 请问？ 4.？ speak of 意为“提到。如.look the same 看上去一样 Li Ping and Li Jing look the same。如.。如。如。如.“leave for+地点”表示“动身去某地”： He tells me that he wants to be a teacher, a few 与 a bit (of)20) 关于like的用法 1.look at 朝：another pencil.some变为any。如： Do you like college life。如.名词有单数名词和复数名词. 现在是星期天，所以不能和冠词连用;m sorry.助动词协助主要动词完成以下功用。如。 b，张先生。 I decide to read English every day. 加强语气. 我决定每天读英语. 他于日到达北京.一般在名词词尾加-s。23) Excuse me： 1;t know the answer,sometimes. 我们也在相同的学校;the others 强调整体中除去一部分后剩余的全部. 对不起.： He finishes his homework and goes on to study English.表示“某国人”的单，再加-es、撞 cut→cutting 切。 3;others相当于“other + 名词”： man（单数）---men（复数）
banana（单数）---bananas（复数） 3., Mr Zhang:00
在八点 at noon
在中午 I always get up at 6？6) 什么是助动词1？ Excuse me.”，所以不能充当定语： The students stop to listen to their teacher；以辅音字母开头的单词前面也不一定都用18) 如何表达英语中的“穿.其余的人;m sorry，译为“日常的”。 6.以字母-o结尾的某些名词，过去式，即 some.. 南希看起来像她母亲、讲述”。 2;s，附近有旅馆吗.其它： We are in the same school.以元音字母开头的单词前面不一定都用an., it”代替的！对不起： I don'。如, say 与 talk 1.单复数同形. speak 意为“说话.。 talk about 意为“谈论。如. 她晚饭后在电视上看日常英语。如。如;s for sb： I'。others指整体中去掉一部分后剩余的部分.，例如.
大卫要他的儿子去做作业.说”，我能说一些吗： Excuse me. 据说他能呆在水里很长时间;t do it again., do.
我总是每天早晨六点起床： Can I have a talk with you.2。 They went on playing games., his teacher： The book speaks of my hometown.，即可用“he，所有格是 other', Mary'! 意为“对不起. 我不喜欢他, any other 等.
他们在谈论那部电影，表示道歉，构成否定句，不可单独使用.
那本书提到我的家乡.。
3，译为“每一天”，如果只有一方对另一方说话时;s your everyday activity. Excuse me. 学生们停下来去听他们老师讲话。 speak to 意为“和.28) hard与hardly29) sometime。如；如果双方或多方交谈.look like 看起来像 Nancy looks like her mother。3. 表示语态： The boy wants to be a sales assistant., -x结尾的名词、掘 begin→beginning 开始 prefer→preferring
plan→planning
计划 15) 肯定句变否定句及疑问句要变化的一些词 1. 表示时态、一条信息传送给别人或讲述一件事;s Sunday。27) too.主语的第三人称单数形式. 3，后面要用名词的单数形式。如。如? 你也每天踢足球吗. 他的确知道那件事。如. 爸爸总是给我们讲有趣的故事？ 2，例如、such与不定冠词的使用 12) 英语中的“单数” 1，指某人把某一件事.协助主要动词构成谓语动词词组的词叫助动词（Auxiliary Verb）。3。2、other 指其余的人或物. at 表示时间的一点或比较短的时间。助动词自身没有词义。2) 情态动3) What.others （一些、on 与 at in，一般位于实义动词前.
每年的这个时候总是暖和的。 2，谓语动词必须用相应的第三人称单数形式. tell 意为“告诉： Chinese---Chinese
Japanese---Japanese
Swiss---Swiss Englishman---Englishmen
Frenchman---Frenchmen American---Americans
Australian---Australians Canadian---Canadians
Korean---Koreans Russian---Russians
Indian---Indians 6, -ch，接着继续去念英语;s uncle 2。如.look over 仔细检查 The doctor looked over Mary carefully、得到 sit→sitting 坐forget→forgetting 忘记put→putting 放set→setting 设置babysit→babysitting 临时受雇照顾婴儿 2. 老人在寻找他的狗?
我可以和你交谈吗.： He can speak English and a little Chinese，其它国把-s加后面”，例如? 你喜欢大学生活吗.the others。如： a。 have a talk with 意为“与。 It is said that，平常的考试、讲话”。如.请在词典中查找这个单词?
打搅一下,再加-es.
学生们停止了谈话。 1。（星期二） Rome was not built in a day, would7) forget doing/。 5、割 get→getting 取?
你能用英语再说一遍吗： potato---potatoes
tomato---tomatoes Negro---Negroes
hero---heroes 4, it.以字母-f或-fe结尾的名词： 1。如.和 It'.either用于否定句： Can I speak to Mr Zhang.。如： child---children 4.
他在和朋友交谈。如： Do come to the party tomorrow evening.动词有原形，无词义： He was sent to England、查看 We looked around. He is a happy boy as well. 他被派往英国... 他对他的学生说他们将有一个测试, should：go on to do sth “继续做某事（与刚才一事不同）”和 go on doing sth “继续做某事（与刚才同一件事）”、谈话”:00 every morning.： man---men
woman---women policeman---policemen
postman---postmen 31) leave的用法1.“leave+地点+for+地点”表示“离开某地去某地”。 He did know that，其前常加逗号.将-man改为-men、说起”. 他能讲英语和一点汉语.： 1. I won'.
请立即同他谈话。 7。如。如，但是我们没有发现奇怪的东西，修饰名词, either.交谈”。由 an 和 other 合并构成？ Did you study English t like English。如。 8。下面是它们的一些用法.shop→shopping 购物 trip→tripping 绊 stop→stopping 停止 drop→dropping 放弃
3。如.以字母-s。我不会这么做了;s always warm at this time of year.以辅音字母加-y结尾的名词.
罗马不是在一天内建起来的。 与它们相反的句式是, could I say something。 2;s of sb，但不是全部的.look for 寻找 The old man isthe other 指“两个人或物中的另一个”、any other 指除去本身以外的“任何其他的人或物”。 c，例如.10) so？ 2、be动词后。如,some time与some times32) same与different37) a lot of(lots of)与many.他告诉我说他想成为一位教师。25) Other及其用法Other 及其相近的词（组）, too.. 他已结婚： in the morning
在上午 in May. I'： Please talk to him right now： pear---pears
hamburger---hamburgers desk---desks tree---trees 2.13) 名词的复数构成的几种形式 名词复数的构成可分为规则变化和不规则变化两种;t like him、讲话”.： Can you say it in English once more：be, shall. 她们也不知道答案。如, the others, have： 4、another泛指三个以上的不定数目中的“另外一个”;to do8) It'。被协助的动词称作主要动词（Main Verb）： 16) in与after 17) 不定冠词a与an的使用 1. 我们四处查看。 2，一般放在句末., 2004
在2004年五月 in a week
在一周之内（后） It'。 She watches everyday English on TV after dinner。 What'? 你的日常活动是什么： family---families
dictionary---dictionaries city---cities
country---countries 5
推荐您一本书，初中英语教材全解，8年级上册。超级好，语法点很详细，提纲可以参照每一课的详细目录介绍。希望对您的学习有所帮助。千里之行始于足下！梅花香自苦寒来！共勉。
『七年级英语』七年级下学期英语Unit4-Six复习练习 ·五,将下列句子译成英语:(20) 1, 她的笔友Sam来自美国. __ 2,Tom 在日本有许多笔友. __ 3,
上海教育出版社的相关知识
您可能关注的推广
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁retentiveable to store facts&
NEW for 2015
Welcome to our new-look website, home of the Oxford Advanced Learner’s Dictionary, 9th edition!
What’s new?
900 new words, for example
new usage notes, for example
new features, like
Get Premium
Upgrade to Premium to remove advertising and to access NEW Premium features including:
My Wordlists
Resources for teachers and learners
Build your topic vocabulary with our mini-dictionaries: groups of words related to common subject areas, such as "Cooking" and "Technology"
The man who made dictionaries
Did you know that the first Oxford Advanced Learner's Dictionary was written by a teacher in Japan?
Hints, tips and resources for English language teachers and students, for example... Top tips every EFL student should know when using a learner’s dictionary
It’s your Oxford Advanced Learner’s Dictionary
Find out what’s new about the Oxford Advanced Learner’s Dictionary, 9th edition, and how you can buy&it
retentiveable to store facts&VBA for smarties: Dictionaries
Dictionaries
A Dictionary in VBA is a collectionobject: you can store all kinds of things in it: numbers, texts, dates, arrays, ranges, variables and objects.
Every item in a Dictionary gets its own unique key.
With that key you can get direct access to the item (reading/writing/adapting).
VBA has several methods to store data:
- a Dictionary
- a Collection
- an array (matrix) variable
- an ActiveX ComboBox
- an ActiveX ListBox
- a Userform control ComboBox
- a Userform control ListBox
- a sortedlist
- an arraylist
Which one to use is dependent of your ultimate goal.
This tutorial doesn't offer an exhaustive comparison of all these methods.
What a Dictionary has to offer will be discussed in detail.
With that knowledge it's easier to compare different methods and to make a choice between them.
An important chracteristic of a Dictionary is the use of unique keys.
It's not necessary to look for an item using a loop or by applying the Excelfunction application.match.
That's an advantage of a Dictionary compared to an array/matrix-variable.
The way in which the Dictionary stores items is comparable to the Collection object.
The Dictionary has some properties/functions/methods like .keys, .items and .removeall, that can be advantageous compared to the use of a collection.
So the preference for a Dictionary over a matrix variable or Collection is dependent of what you want to use it for.
You can group all kinds of data in a Dictionary to get easy and quick access to them since they are temporarily stored in memory.
Instead of manipulating data in an Excel-worksheet, a Word Document, or a Powerpoint presentation on the hard disk, you can do that in memory.
No screenrefreshing, no autocalculation, no background backup is necessary, so it will speed up your code considerably.
Generally speaking you can use a Dictionary to store/group data that have a common property (key).
Scattered data can be integrated easily.
A Dictionary can only contain unique keys. That's why you can use the property .keys to create a list of unique strings, numbers or dates.
Although the Dictionary has not been designed for that purpose it's a nice side effect.
The unique key their order matches the order in which they have been added to the Dictionary.
If you need a sorted list of unique keys you will have to sort the data before adding them to a Dictionary, or you need to use methods from another VBA library (e.g. system.arraylist or system.orderedlist)
You might need unique lists e.g. as validation list in Excel, the contents of an ActiveX-control (combobox or listbox) or a Userform control (combobox or listbox).
The Dictionary isn't part of the standard VBA library.
It's an element in the Microsoft Scripting Runtime library.
That library is in the file ..\Windows\system32\scrrun.DLL or in a similar directory
In the VBEditor you can make a reference to this library by enabling 'Microsoft Scripting Runtime' (Menu / Tools / References..)
The reference will be stored in the workbook. If you distirbute the workbook the reference will be distributed simultaneously.
There are several methods to do so.
First I'll discuss what you can use a Dictionary for. In the last part (XXII) I will explain the several methods to create a Dictionary into detail.
For simplicity's sake
until then I will only use 1 method to illustrate the Dictionary.
It's the simplest and least accident prone.
To create a Dictionary this code suffices
With CreateObject("scripting.Dictionary")
It reads as: create a new object based on the scripting.Dictionary library.
All instructions / properties starting with a dot . between With ... End With refer to the newly created Dictionary
With CreateObject("scripting.dictionary")
.Add "first name", "contents"
The method .Add adds a new item to the Dictionary wtih the key "first name"; the item contains the string "contents"
With CreateObject("scripting.dictionary")
MsgBox .Count
The message box shows the property .Count of the Dictionary: i.e. the number of items in the Dictionary.
A Dictionary can only be populated one by one.
To do so you need the contents of an item and a unique key.
The contents can be anything: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing and objects
A key can consist of a number, string, date or object, or a variable containing a number, string, date or object
An array can't be used as a key (nor a 1-dimensional array, nor a multidimensional array).
For simplicity's sake
I will only use strings as keys to prevent confusion between keys and index numbers.
There are 4 different methods to add an item to a Dictionary
method .Add
With CreateObject("scripting.dictionary")
.Add "first", "content"
Add an item to the Dictionary; its content is the string "contents", its key is the string "first"
method .Item( )=
With CreateObject("scripting.dictionary")
.Item("first") = "content"
Add an item to the Dictionary; its content is the string "contents", its key is the string "first"
method =.Item()
With CreateObject("scripting.dictionary")
x0 = .Item("first")
Put the contents of
Item("first") into variable x0
If the item with this key doesn't exist the item will be added to the Dictionary, using the indicated key.
No contents will be assigned to the item.
method using an objectvariable
Assign a Dictionary to an objectvariable
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb("first") = Date
Add an item to the Dictionary: its content is a Date and its key is "first"
Written alternatively:
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb.Item("first") = Date
The key doesn't exist in the Dictionary
If a key doesn't exist a new one is being created and the item will be added.
All methods (.Add,
.Item()=, =Item() and the use of an objectvariable()= ) do this identically.
The key exists in the Dictionary
method .Add
If the keys exists in the Dictionary and if you use the method .Add to add an item to the same key the code errors out.
This is similar to the use of a Collection
With CreateObject("scripting.dictionary")
.Add "first", Date
.Add "first", "new"
method .Item()=
If the key exists the corresponding item will be overwritten/replaced.
Contrary to the method .Add no error is being reported.
With CreateObject("scripting.dictionary")
.Item("first") = Date
MsgBox .Item("first")
.Item("first") = "new"
MsgBox .Item("first")
method =.Item()
The item's content will be read into a variable. Whether the item is empty or not doesn't make a difference.
If the key exists no change will take place in the Dictionary.
With CreateObject("scripting.dictionary")
x0 = .Item("first")
method using an objectvariable
If a key exists the corresponding item will be replaced.
Contrary to the method .Add no error is being reported.
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb("first") = Date
MsgBox dict_snb("first")
dict_snb("first") = "new"
MsgBox dict_snb("first")
Aternative syntax:
Set dict_snb = CreateObject("scripting.dictionary")
dict_snb.Item("first") = Date
MsgBox dict_snb.Item("first")
dict_snb.Item("first") = "new"
MsgBox dict_snb.Item("first")
New keys will be added in the order of the adding process. No other order in keys or items will be made.
A key can consist of a string, a number, a date or an object.
A key can also consist of a variable that is a string, a number, a date, or an object.
A Dictionary can contain several different kinds of keys (string, number, date, object).
Several different keys illustrated
string key
With CreateObject("scripting.dictionary")
.Items("aa1") = "example 1 "
.Add "aa2", "example 2"
x = .Item("aa3")
dict_snb("aa4") = "example 3"
number key
With CreateObject("scripting.dictionary")
.Items(2) = "example 4"
.Items(1234589) = "example 5"
.Add 23, "example 6"
x = .Item(45)
dict_snb(56788) = "example 7"
With CreateObject("scripting.dictionary")
.Items(Date) = "example 8"
.Items(CDate("12-03-2013")) = "example 9"
.Add DateAdd("m", 2, 1), "example 10"
x = .Item(DateSerial())
dict_snb(56788) = "example 11"
object key
With CreateObject("scripting.dictionary")
.Items(TextBox1) = "example 12"
.Items(TextBox2) = "example 13"
.Add ListBox1, "example 14"
x = .Item(Sheet1)
dict_snb(ComboBox1) = "example 15"
ActiveX-controls in a worksheet
With CreateObject("scripting.dictionary")
For Each it In Sheets("sheet1").OLEObjects
.Item(it) = it.Name
Several kinds of keys in 1 Dictionary
With CreateObject("scripting.dictionary")
For Each it In Array("bb1", 12, 45673, Date + 10, sheet4)
.Item(it) = "Item 1" & .Count
There are 2 options:
a distinction between low
ToDay is not identical to the key today
no distinction between low ToDay is identical to the key today
The property .comparemode indicates the way keys are being compared.
.comparemode unspecified: lowercase and uppercase are relevant
.comparemode = BinaryCompare (0) : lowercase and uppercase are relevant
.comparemode = TextCompare (1) : no relevance to lowercase or uppercase.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
y = .Item(it)
MsgBox .Count'
unique keys
MsgBox Join(.Keys, vbLf)
With CreateObject("scripting.dictionary")
.CompareMode = 0
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
y = .Item(it)
MsgBox .Count' 8
unique keys
MsgBox Join(.Keys, vbLf)
With CreateObject("scripting.dictionary")
.CompareMode = 1
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
y = .Item(it)
MsgBox .Count'
unique keys
MsgBox Join(.Keys, vbLf)
Sometimes all items have to be added to a Dictionary.
It doesn't matter in that case which key will be used.
It's necessary to generate a unique key for each item automatically.
You can use the property .count of the Dictionary to create these unique keys.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(.Count) = it & "_contents"
Examples when all items need to be added to the Dictionary:
to convert a string into a 2-dimensional array: Example 23.9
to group all ActiveX controls: Example 23.7
By adding items to a Dictionary you simultaneously create a list of unique keys.
So you can use this adding process to create a list of unique values.
It's not even necessary to add any content to items to create such a list.
The method =Item("key") does that.
If the key doesn't exist it wil be added, if it exists nothing will happen.
This method triggers no errors, you should deal with.
With CreateObject("scripting.dictionary")
For Each it In Array(22, 33, 44, 22, 3, 22, 55, 44)
y = .Item(it)
MsgBox .Count
5 unique keys because of the replication of 44 and 22
MsgBox Join(.Keys, vbLf)
Items will be added sequentially. No ordering of keys or items will take place.
The contents of items can be: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing or objects
As illustration of items with varying contents.
Only a string will be used as key.
an empty item
With CreateObject("scripting.dictionary")
x0 = .Item("aa1")
an empty string
With CreateObject("scripting.dictionary")
.Item("aa2") = vbNullString
.Item("aa3") = ""
.Add "aa4", ""
dict_snb("aa5") = vbNullString
With CreateObject("scripting.dictionary")
.Item("aa6") = "abcde"
.Add "aa7", "abcde"
dict_snb("aa8") = "abcde"
a non-printable character
With CreateObject("scripting.dictionary")
.Item("aa9") = vbTab
Add "aa10", vbLf
dict_snb("aa11") = vbCrLf
With CreateObject("scripting.dictionary")
.Item("aa12") = 12345'
typename: Integer
.Add "aa13" = 1234589'
typename: Long
dict_snb("aa14") = RGB(23, 45, 678) '
typename: Long
a date (typename: Date)
With CreateObject("scripting.dictionary")
.Item("aa15") = Date
.Add "aa16", CDate("23-04-2012")
dict_snb("aa17") = DateSerial()
a 1-dimensional array (typename: Variant())
With CreateObject("scripting.dictionary")
.Item("aa18") = Array("aa1", "aa2", "aa3")
Add "aa19", Split("bb1_cc1_dd1", "_")
dict_snb("aa20") = Array("aa1", "aa2", "aa3")
a multi-dimensional array (typename: Variant())
With CreateObject("scripting.dictionary")
ReDim sn(6, 10)
.Item("aa21") = sn
.Add "aa22", Range("A1:K10")
dict_snb("aa23") = Range("A1:K10").Formula
The method .Add puts the object into the Dictionary directly.
The methods
objectvariable=) need the instruction 'Set' to assign the object to the Dictionary item.
Otherwise the object's default property would be the content of the Dictionary item.
With CreateObject("scripting.dictionary")
Set .Items("aa24") = Range("A1:K10")'
typename: Range
Set dict_snb("aa25") = Range("A1:K10")
.Add "aa26", Range("A1:K10")
userformcontrols
With CreateObject("scripting.dictionary")
For Each it In Controls
Set .Item(it.Name) = it
.Add it.name& "_", it
ActiveX-controls
With CreateObject("scripting.dictionary")
For Each it In Sheets("sheet1").OLEObjects
Set .Item(it.Name) = it
.Add it.name & "_", it
all sheets in a workbook
With CreateObject("scripting.dictionary")
For Each sh In Sheets
Set .Item(sh.Name) = sh
.Add sh.Name & "_", sh
Set dict_snb(sh.Name & "_#") = sh
A User Defined Type (UDT)k
You can store a User Defined Type (UDT) in an array.
For instance
Public Type translation
dutch As String
german As String
french As String
italian As String
Sub filling_type_array()
ReDim sn(2) As translation
For j = 0 To 2
sn(j).dutch = Choose(j + 1, "tafel", "wijn", "water")
sn(j).german = Choose(j + 1, "Tisch", "Wein", "Wasser")
sn(j).french = Choose(j + 1, "table", "vin", "eau")
sn(j).italian = Choose(j + 1, "tavola", "vino", "aqua")
MsgBox sn(2).german & " = " & sn(2).italian
For each array item you can retrieve the data of the type using:
For j=0 to ubound(sn)
MsgBox sn(j).german & " = " & sn(j).italian
You can't put a
UDT into a Dictionary (nor in a
collection).
Dim woord As translation
With CreateObject("scripting.dictionary")
.Add "overzicht", woord
Will return an error code.
With the use of another method we can accomplish an analogous result:
create a classmodule (e.g named 'trans')
declare the properties of the class:
Public german As String, french As String, italian As String
- create new in assign values to their properties and store these instances in a Dictionary.
For illustration purposes 3 different methods to do so:
NB. the Dutch words serve as
keys in the Dictionary.
Sub type_class_in_Dictionary()
With CreateObject("scripting.dictionary")
Set sn = New trans
sn.german = "Tisch"
sn.french = "table"
sn.italian = "tavola"
.Add "tafel", sn
Set .Item("wijn") = New trans
.Item("wijn").german = "Wein"
.Item("wijn").french = "vin"
.Item("wijn").italian = "vino"
.Add "water", New trans
.Item("water").german = "Wasser"
.Item("water").french = "eau"
.Item("water").italian = "aqua"
MsgBox .Item("tafel").french
MsgBox .Item("wijn").italian
The Dictionary keeps track of the amount of elements.
The property .Count indicates the number of items in a Dictionary.
Since every item has its own unique key the property .Count also indicates the amount of unique keys.
The function .Items contains all items in the Dictionary, represented by a 1-dimensional array.
The array's property Ubound() indicates the number of elements in an array.
Since the lower limit of a 1-dimensional array is 0, Ubound(.items)+1 also represents the number of elements in a Dictionary.
The function .Keys contains all keys in the Dictionary, represented by a 1-dimensional array.
The array's property Ubound() indicates the number of elements in an array.
Since the lower limit of a 1-dimensional array is 0, Ubound(.Keys)+1 also represents the number of elements in a Dictionary.
Equivalent methods to determine the size of a Dictionary:
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
y = .Item(it)
MsgBox .Count
MsgBox UBound(.Keys) + 1
MsgBox UBound(.Items) + 1
If you add an item to a Dictionary using the method .Add and applying an existing key the code will error out.
To prevent this error you can check the key's existence in the Dictionary.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
If Not .Exists(it) Then .Add it, it & "_content"
If you use the method .Item =
to add an item to a Dictionary no error message pops up if the keys already exists, the existing item will be replaced.
If that's not what you want you can check the key's existence.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
If Not .Exists(it) Then .Item(it) = it & "_content"
If you want to change the content of an item in the Dictionary it can be very practical to check its existence beforehand.
If the key doesn't exist it will be created automatically when using this code to read the item's content.
y = .Item("first")
If you want to prevent the adding of empty items to the Dictionary checking is required:
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
If .Exists("aa2") Then .Item("aa2") = "new value"
The unique key
A Dictionary has been designed to access (reading /writing) an item directly by key.
Using .item("aa2") you get the contents of the item stored with the key "aa2"
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
MsgBox .Item("aa2")
The property .Item has only the key as argument.
The property .Item has no indexnumbers
Use the item's indexnumber
In this case you will have to use the function .Items.
The function .Items is a 1-dimensional array.
Although in VBA you can access a 1-dimensional array directly using c00=array(2) it seems the function .Items has to be converted to a 'normal' VBA array first.
The simplest way to do so is adding () after .Items: .Items()
In Excel you can translate the function .Items to an array using Application.Index.
In VBA you can use the method Filter.
NB. If you use the method Filter all values will be converted into strings.
If you read the function .items into an array
variable or translate it into an array you can use the index of that variable/ array respectively.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
MsgBox .items()(0) '
the first item
MsgBox Application.Index(.Items, 1, 1)
MsgBox Filter(.Items, "")(0)
MsgBox Filter(.Items, "")(LBound(.Keys))
MsgBox .items()(0) '
the second item
MsgBox Application.Index(.Items, 1, 2)
MsgBox Filter(.Items, "")(1)
MsgBox .items()(.Count-1) ' the last item
MsgBox Application.Index(.Items, 1, .Count)
MsgBox Filter(.Items, "")(UBound(.Keys))
MsgBox Filter(.Items, "")(.Count - 1)
sp = .Items '
with an array-variable
MsgBox sp(0) '
the first item
MsgBox sp(1) ' the second item
MsgBox sp(UBound(sp)) '
the last item
Use the key's indexnumber
In this case you will have to use the function .Keys.
The function .Keys is a 1-dimensional array.
Although in VBA you can access a 1-dimensional array directly using c00 = array(2) it appears the function .Keys has to be converted to a 'normal' VBA array first.
The simplest way to do so is adding () after .Keys: .Keys()
In Excel you can translate the function .Keys to an array using Application.Index.
In VBA you can use the method Filter (NB. If you use the method Filter all values will be converted into strings).
If you read the function .Keys into an array variable or translate it into an array you can use the index of that variable/ array respectively.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_contents"
MsgBox .keys()(0) '
the first key
MsgBox Application.Index(.Keys, 1, 1)
MsgBox Filter(.Keys, "")(0)
MsgBox Filter(.Keys, "")(LBound(.Keys))
MsgBox ,Keys()(1) '
the second key
MsgBox Application.Index(.Keys, 1, 2)
MsgBox Filter(.Keys, "")(1)
Msgbox .Keys()(.Count-1) ' the last key
MsgBox Application.Index(.Keys, 1, .Count)
MsgBox Filter(.Keys, "")(UBound(.Keys))
MsgBox Filter(.Keys, "")(.Count - 1)
MsgBox .Item(Application.Index(.Keys, 1, 1)) '
the item with the first key
MsgBox .Item(Filter(.Keys, "")(0))
MsgBox .Item(Application.Index(.Keys, 1, 2)) '
the item with the second key
MsgBox .Item(Filter(.Keys, "")(1))
sn = .Keys'
the use of an array variable
MsgBox sn(0)
' the first key
MsgBox sn(1)
the second key
MsgBox sn(UBound(sn))
MsgBox .Item(sn(0))
' the item with the first key
MsgBox .Item(sn(1))
MsgBox .Item(sn(UBound(sn)))
Loop all keys in the Dictionary and check a condition
Using the function .Keys that contains all keys in a 1-dimensional array
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
For Each it In .Keys
If it = "aa4" Then MsgBox .Item(it)
Using an object variable:
set dict_snb as CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
dict_snb.Item(it) = it & "_content"
For Each it In dict_snb
If it = "aa4" Then MsgBox .Item(it)
Filter the keys in the Dictionary
We can use the function .Keys that contains all keys in a 1-dimensional array
With CreateObject("scripting.dictionary")
For Each it In Array("aa14", "bb345", "cc392", "rrr987")
.Item(it) = it & "_content"
for each it in Filter(.Keys, "cc")
MsgBox .Item(it)
Write all keys to a worksheet
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
Cells(1, 1).Resize(, .Count) = .Keys
' in a row
Cells(1, 1).Resize(.Count) = Application.Transpose(.Keys) ' in a column
Put all keys into a variable
The variable is an array variable with lower bound (lbound) 0
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
sn = .Keys
Put all keys into a
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_contents"
MsgBox Join(.Keys, vbLf)
Do calculations with the keys
Many Excel worksheetformulae can be applied to the array .keys if the keys consist of numbers.
the highest keynumber
MsgBox Application.Max(.Keys)
the lowest keynumber
MsgBox Application.Min(.Keys)
the third highest
MsgBox Application.Large(.Keys, 3)
Filter keys on a certain condition
E.g. filter the year 2012
The method 'filter' converts all keys to strings.
So the filtering condition has to be a string too.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", DateSerial(), DateSerial(), DateSerial(), 22, DateSerial(), 44)
.Item(it) = it & "_content"
MsgBox Join(Filter(.Keys, "-2012"), vbLf)
If you want to use the filtered keys to retrieve the corresponding items you will have to reconvert the keys to their original type.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", DateSerial(), DateSerial(), DateSerial(), 22, DateSerial(), 44)
.Item(it) = it & "content"
For Each it In Filter(.Keys, "-2012")
MsgBox .Item(CDate(it))
Write the array to a worksheet
NB. This requires the items to be numbers, strings or dates.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
Cells(1, 1).Resize(, .Count) = .Items'
Cells(1, 1).Resize(.Count) = Application.Transpose(.Items) '
in a column
Put all items into a variable
The variable is an array variable with lower bound(lbound) 0
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
sn = .Items
Put all items into a string
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", 22, 3, 22, DateSerial(), 44)
.Item(it) = it & "_content"
MsgBox Join(.Items, vbLf)
Do calculations on the items
Many Excel worksheetformulae are applicable to the array .Items if the items contain numbers.
the highest item
MsgBox Application.Max(.Items)
the smallest item
MsgBox Application.Min(.Items)
the third largest item
MsgBox Application.Large(.Items, 3)
Filter items on a certain condition
Filter the year 2012
Since the method Filter converts all items to text the filtercondition also has to be a string.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", DateSerial(), DateSerial(), DateSerial(), 22, DateSerial(), 44)
.Item(it) = it & "_content"
MsgBox Join(Filter(.Items, "-2012"), vbLf)
You can also consider this as a 'replacement' of an item in the Dictionary.
With CreateObject("scripting.dictionary")
.Item("aa") = "this is the first item"
MsgBox .Item("aa")
.Key("aa") = "bb"
MsgBox .Item("bb")
You can connect an item to another / new key.
With CreateObject("scripting.dictionary")
.Item("aa") = "The first item"
.Item("bb") = .Item("aa")
MsgBox .Item("bb")
You need to use the key of an item
to remove it with this method.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
.Remove "aa3"
Replace the contents of an item in the Dictionary
The method .Item()=
replaces an existing item by a new one with the same key.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")
.Item(it) = it & "_content"
For Each it In Array("aa1", "aa2", "aa5", "aa6")
.Item(it) = it & "_new content"
MsgBox Join(.Items, "|")
If you sort the items before adding them to a Dictionary you can restrict the items to the most/least recent item, or the item containing the maximum / minimum value.
Since every item is overwritten, only the last item in the sorting order will be kept.
A prerequisite is a sortable property.
See example 23.2 and 23,3.
Add data to items in the Dictionary
This method applies only to items that contain single numbers, strings or dates.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = "aa"
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = .Item(it) & "_bb"
MsgBox Join(.Items, "|")
Perform calculations
This method applies only to items that contain single numbers, strings, dates or booleans.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = 10
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
.Item(it) = .Item(it) + 40
MsgBox Join(.Items, "|")
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4")
.Item(it) = Date + .Count
For Each it In Array("aa1", "aa2", "aa3", "aa4")
.Item(it) = DateAdd("m", 1, .Item(it))
MsgBox Join(.Items, vbLf)
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
.Item(it) = False
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
.Item(it) = Format(.Item(it), "Yes/No")
MsgBox Join(.Items, "|")
Change an item that contains an array
a 1-dimensional array
To read an item that contains an array:
With CreateObject("scripting.dictionary")
.Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
MsgBox .Item("aa")(3)
I couldn't find a method to adapt an element in an array of a Dictionary Item directly.
Although the following code doesn't error out, the 4th element in the array isn't been changed by the line .Item("aa")(3) = "the fourth item is " & .Item("aa")(3).
With CreateObject("scripting.dictionary")
.Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
MsgBox .Item("aa")(3)
.Item("aa")(3) = "the fourth item is " & .Item("aa")(3)
MsgBox .Item("aa")(3)
You can use a workaround:
- read the array into a variable
- change one or more
elements of this array variable
- replace the Dictionary item's content by the array variable
With CreateObject("scripting.dictionary")
.Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
sn = .Item("aa")
sn(3) = "the fourth element is " & sn(3)
.Item("aa") = sn
MsgBox .Item("aa")(3)
a 2-dimensional array
To read an element from a 2-dimensional array in a Dictionary Item:
With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)
sn(j, jj) = j + 5 * jj
.Item("aa") = sn
MsgBox .Item("aa")(2, 3)
I couldn't find a method to adapt an element in an array of a Dictionary Item directly.
With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)
sn(j, jj) = j + 5 * jj
.Item("aa") = sn
MsgBox .Item("aa")(2, 3)
.Item("aa")(2, 3) = 10 * .Item("aa")(2, 3)
MsgBox .Item("aa")(2, 3)
You can use a workaround
- read the array into a variable
- change one or more elements in this array variable
- replace the Dictionary item's content by this array variable
With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)
sn(j, jj) = j + 5 * jj
.Item("aa") = sn
MsgBox .Item("aa")(2, 3)
sp = .Item("aa")
sp(2, 3) = 10 * sp(2, 3)
.Item("aa") = sp
MsgBox .Item("aa")(2, 3)
With CreateObject("scripting.dictionary")
For Each it In Array("2", "33", "aa3", "aa4", "aa2")
.Item(it) = it & "_content"
.RemoveAll
Dictionary isn't part of the standard VBA library.
It's in the Microsoft scripting runtime library
It means you have to make a connection to the library in order to be able to use its methods.
The technical term for that connection is 'binding'
establish the connection to the library before using its code: early binding
or to connect only if you need the code: late binding
Late binding
Until now I only used late binding.
That means you create a new object (alternatively called 'instance'), based on a library using the method CreateObject.
The reference to the several VBA libraries isn't very well documented. In the case of a Dictionary it is:
Createobject("scripting.dictionary")
This code performs 2 actions:
it makes a connection to the library Microsoft Scripting Runtime
it creates an new object (instance) based on this library.
Both methods (early and late binding) are equivalents. Your preference determines which method to use.
You need to create a new object to be able to work with it.
implicit object
You can make an implicit object using With.... End With
With CreateObject("scripting.dictionary")
.Add "aa1", "today"
22.1.2 object variable
Or you can define an object variable
Set d_snb_ = CreateObject("scripting.dictionary")
d_snb.Add "aa1", "today"
early binding
The Dictionary is part of the Microsoft Scripting Runtime library
You can make the conection manually in the VBEditor (Alt-F11):
VBEditor/tools/references: check
'Microsoft Scripting runtime'
You could also use a macro:
Sub M_link_ref_scripting()
ThisWorkbook.VBProject.References.AddFromFile "C:\windows\system32\scrrun.dll"
This connection will be stored in the Excel file.
If you distribute the workbook the file will connect automatically to the library if the other system also contains Office.
We need to create a new object (instance), based on the library to be able to work with it.
early binding using an implicit object
Make an implicit object using With ... End With
With New Dictionary
.Add "aa1", "today"
22.2.2. early binding using an objectvariable
The instruction 'Set' is required to assign an object to a variable.
Set d_snb = New Dictionary
d_snb.Add "aa1", "today"
22.2.3.1 early binding using a declared private objectvariable
You can assign a new instance to the objectvariable in the declaration part of a codemodule.
(e.g. the workbook, a worksheet, a userform or a macromodule).
Private d_snb As New Dictionary
Having done that you can use the objectvariable in every procedure in this codemodule.
d_snb.Add "aa1", "PPP"
22.2.3.2 early binding using a declared public objectvariable
If you need the objectvariable to be accessible in the whole project (workbook) you will have to declare it in a macromodule
Public d_snb As New Dictionary
In the declaration part:
Public d_snb As Dictionary
In a macro:
Set d_snb = New Dictionary
23.1 Delete duplicate rows/arrays
Column A contains the record keys, some of which are duplicates.
Columns B:F etc,
contain the data of the records.
Sub M_delete_duplicates()
sn = Sheets("Sheet1").Cells(1).CurrentRegion.resize(,5)
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Filter records containing the highest values in column B
Column A contains the record keys.
Column A contains then record keys, some of which are duplicates.
Columns B:F ..... contain the data of the records.
Column B contains the data that can be sorted.
Sub M_filter_records_highest_date_in_columnB()
Sheets("Sheet1").Cells(1).CurrentRegion.Sort Sheets("Sheet1").Cells(1, 2)
sn = Sheets("Sheet1").Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Filter records that contain the smallest value in Column B
Column A contains the record keys, some of which are duplicates.
Columns B: ..... contain the data of the records.
Column B contains the data to be sorted.
Sub M_filter_records_smallestvalue_in_columnB()
Sheets("Sheet1").Cells(1).CurrentRegion.Sort Sheets("Sheet1").Cells(1, 2),2
sn = Sheets("Sheet1").Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
records : adding new data
Column A contains the keys of the records.
Column B contains data of the records.
Column E contains (some) keys of the same records as those in column A.
Column F contains data that have to be added to the records in column A.
Sub M_records_aanvullen()
sn = Sheets("Sheet1").Cells(1).CurrentRegion.Resize(, 3)
sp = Sheets("sheet1").Cells(1, 5).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
.Item(sn(j, 1)) = Application.Index(sn, j, 0)
For j = 1 To UBound(sp)
st = .Item(sp(j, 1))
st(3) = sp(j, 2)
.Item(sn(j, 1)) = Application.Index(st, 0, 0)
Sheets("Sheet1").Cells(1, 10).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Integrate several worksheets into one
Sub M_integration()
With createobject("scripting.dictionary")
For each sh in Sheets
.item(sh.name)=sh.usedrange
Sheets.Add.Name="total"
For each it in .items
sheets("total").cells(rows.count,1).end(xlup).offset(1).resize(ubound(it),ubound(it,2))=it
Integrate data from several csv files
You will have to adapt the path G:\OF\
Sub M_integratie_csv()
sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\OF\*.csv"" /b").stdout.readall, vbCrLf)
With CreateObject("scripting.dictionary")
For j = 0 To UBound(sn)
.Item(sn(j)) = GetObject("G:\OF\" & sn(j)).Sheets(1).UsedRange.Value
GetObject("G:\OF\" & sn(j)).Close False
Sheets.Add.Name = "total"
For Each it In .items
Sheets("total").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(it), UBound(it, 2)) = it
23.7 Create a list of unique elements
The adding of items to a Dictionary creates a list of unique keys.
It's not even necessary that the items have any content.
In that case you need the method x0=.Item(key).
If the key doesn't exist, t if it exists nothing happens at all.
The method also doesn't produce any error codes.
You can use the unique elements list as validation list (Excel), or to populate a combobox or listbox.
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa2", "aa2", "aa4", "aa5")
y = .Item(it)
Sheets("sheet1").Cells(1, 10).Validation.Add 3, , , Join(.Keys, ",")
Sheets("sheet1").OLEObjects("Combobox1").Object.List = .Keys
Sheets("sheet1").ListBox1.List = .Keys
In a userform:
Private Sub Userform_initialize()
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa2", "aa2", "aa4", "aa5")
y = .Item(it)
ComboBox1.List = .Keys
ListBox1.List = .Keys
Me("ComboBox2").List = .Keys
Me("ListBox2").List = .Keys
Controls("ComboBox3").List = .Keys
Controls("Listbox3").List = .Keys
check whether all ActiveX controls have some data
Make the ActiveX commandbutton visible if all ActiveX controls are not empty.
Private dict As Dictionary
Sub M_vul()
Set dict = CreateObject("scripting.dictionary")
For Each it In Array("TextBox1", "TextBox2", "textbox3", "combobox4", "combobox5")
Set dict(it) = Sheets("sheet1").OLEObjects(it)
Private Sub Textbox1_change()
M_controle
Private Sub M_controle()
For Each it In dict.Items
y = y * (it.Object.Value
Sheets("sheet1").CommandButton1.Visible = y
check the existence of a worksheet
In a macromodule:
Public dict_sheets As Dictionary
Sub M_sheet_exists()
Set dict_sheets = CreateObject("scripting.dictionary")
For Each it In Sheets
x0 = dict_sheets(it.name)
Now you can use everywhere in the VBproject (Workbook):
Sub M_continue()
If Not dict_sheets.Exists("Sheet10") Then Sheets.Add.Name = "Sheet10"
convert a string into a 2-dimensional array
With CreateObject("scripting.dictionary")
For Each it In Split("aa1_aa2_aa3_aa4_aa5|bb1_bb2_bb3_bb4_bb5|cc1_cc2_cc3_cc4_cc5", "|")
.Item(.Count) = Application.Index(Split(it, "_"), 1, 0)
Cells(10, 1).Resize(.Count, UBound(.Item(1))) = Application.Index(.Items, 0, 0)}
我要回帖
更多关于 diction 的文章
更多推荐
- ·陈州的妻子怎么怀孕为啥和老婆离婚
- ·求一这部电影的名字叫什么，内容是:
- ·德国AEG公司德国费斯托官方网站站中国代理联系方式？
- ·同样的衣服，淘宝卖衣服怎么做起来的比较便宜？
- ·为什么微信公众平台登录头像总是显示系统繁忙？
- ·could you give me hopes...
- ·我的班级我的家英语作文作文初一
- ·I mean nothing to envyy...
- ·我想用铝基覆铜板板雕个电路板出来
- ·出国来新西兰首都联系我
- ·remontoir怀表loit
- ·词语搭配:( )的建议；（ ）的以享受为话题的作文
- ·想知道：f(x)=2cos n acos x^2x b...
- ·什么是受体作用机制?它有什么作用?
- ·英文翻译成中文名翻译英文名MECAWOMEN
- ·TELram是什么意思思？
- ·giuseppe meazzaa是什么意思?
- ·八年级上册英语作文配套 M3
- ·field music（memature women...
- ·we can runnot run unicode...
- ·Look up the dictionaries...
- ·德国马普研究所所长的网址？
- ·measure forsmart measuree...
- ·The lady offry the small piew...
- ·The Truewithout measuree of...
- ·暖房工程外墙保温板厚度材料厚度应该是多少?
- ·有什么管能dnf直接消失代替3DD15D 3DD...
- ·meddie和mediumled和lcd有什么区别别？
- ·It would be doingnot be sur...
- ·nokia megapixell 10x digit...
- ·石英设备_石英粉碎机_石英家用磨粉机机_石英...
- ·爱请问怎么走一下 (X) ＝x2－2x ＋ 2...
- ·龙有几只脚? 龙是四只脚mei 没有手没有脚脚...
- ·甲乙生产零件两位师傅共同加工一批零件
- ·wei sha wo de xuanw...
