VBA新手学习笔记之：二级下拉菜单(字典嵌套法)

VBA新手学习笔记之：二级下拉菜单（字典嵌套法）如下效果图：今天这个效果所涉及的新增知识点有：1.模块级变量的定义2.事件程序3.字典嵌套4.Join函数下面我们逐一来解读下这四个知识点：1.先看看什么是事件之前我们执行Excel程序都需要手动画一个按钮，然后将对应的过程指定给这个按钮，当我们需要程序运行的时候，就发送一个命令（点一下按钮）给这个程序，程序接收命令后，会按照程序的逻辑进行运行。而事件程序是不需要手工指定按钮，而是当我们在操作Excel的时候有些动作会自动被ExcelVBA所识别，VBA内部已经自行内置了有关此事件的过程名，我们在对应的过程中写需要执行操作的代码，当Excel的某个动作发生的时候，会自动触发执行所写的程序。这个案例所用到的事件程序有两个：第一：单元格选区发生变化的时候：当需要填写省份的那一列的单元格选区发生变化时，需要添加去重后的省份的下拉菜单操作方法：对准需要达到效果的工作表名的位置右键——查看代码——进入工作表的代码编辑区——选择worksheet——会自动弹出（单元格选区发生变化时要执行的过程）——在该过程中写所需要的代码即可PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)EndSub第二：单元格的值发生变化的时候：当省份填写完毕后，则需要将对应省份的城市添加到城市单元格的下拉菜单，如果省份单元格没有填、则城市下拉菜单跟着消失。操作方法：前面的操作与第一点都是一样的，调出代码窗口后——选择worksheet——在事件下拉框中选择Change事件——会自动生成（单元格值发生变化时要执行的过程）PrivateSubWorksheet_Change(ByValTargetAsRange)EndSub2.因为今天的两个程序中都需要使用同一个字典来做数据有效性的下拉菜单，所以用了一个字典对象的模块级变量dicSF,所谓模块级变量即在同一个模块内所有程序都能使用的变量；这里涉及到变量的作用域的问题，大家可以百度搜索下什么叫变量的作用域。3.字典嵌套的运用，因为我们要通过不同的省份分别对应找到其对应的城市，显然这是一个字典无法完成的事情，因为我们有很多个省份，多个省份下又又多个城市，所以我们在省份字典dicSF中每存入一个省份，那么就将这个省份作为一个新的字典的名称再创建一个字典，在这个字典下将城市再存入省份这个字典的Keys里字典嵌套大家不要想得太复杂，我们只要将省份字典的keys看成是你定义的一个新的字典名称，就很容易理解了就拿案例来说：字典嵌套的核心代码见下Fori=2ToUBound(arr)IfNotdicSF.Exists(arr(i,1))Then'创建对应省份的字典SetdicSF(arr(i,1))=CreateObject('scripting.dictionary')EndIf'在该省份的字典内添加所属城市dicSF(arr(i,1))(arr(i,2))=''Nexti代码解读：如果字典dicSF中没有对应的省份，比如现在I循环到2，那么省份就是云南，第一个循环，云南还没有存到dicSF中，那么NotdicSF.Exists(arr(i,1))的结果就是True，就执行下面的这个创建字典的操作SetdicSF(arr(i,1))=CreateObject('scripting.dictionary')，我们把代码分解出来，dicSF是我们定义的一个字典名称；同理：SetdicSF(arr(i,1))也可以看成我们定义的一个字典的名称，如果i=2,那么这里的dicSF(arr(i,1))实际上就是dicSF（云南），那么在这个字典里添加对应城市的代码dicSF(arr(i,1))(arr(i,2))=''就可以看成dicSF（云南）（思茅市）=“”，也就是说“思茅市”是dicSF（云南）的第一个key。循环完之后，所有的省份就都创建了一个字典并将其下属的城市都存进其keys中了。4.join函数是将一个数组以给定的分割符组合成一个长字符串，数据有效性种序列来源值的格式要求将每个城市以逗号分割开，我们知道，字典的keys和Items都是一个以0开始的一维数组，在选择来源的时候我们不能直接将数组给它，所以就用到Join函数来连接数组的每个元素了。WithTarget.Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=Join(dicSF.Keys,',')EndWith最后附上两段完整的代码：OptionExplicit'要求变量声明DimdicSFAsObject'定义一个模块级变量用来存去重之后的省份'单元格的值发生变化时要做的事PrivateSubWorksheet_Change(ByValTargetAsRange)DimstrSFAsStringWithTargetIf.Column<>6ThenExitSubIf.Count<>1ThenExitSubstrSF=.Value'下面是通过'数据有效性'录制宏得到的代码With.Offset(0,1).Validation.DeleteIfstrSF<>''Then.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=Join(dicSF(strSF).Keys,',')Else.AddType:=xlValidateInputOnly,AlertStyle:=xlValidAlertStop,_Operator:=xlBetweenEndIfEndWithEndWithEndSub'单元格选区发生变化时要做的事PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)Dimarr,iAsIntegerIfTarget.Column<>6ThenExitSubIfTarget.Row>1Then'清空选中单元格对应城市的单元格的内容Target.Offset(0,1).Value=''SetdicSF=CreateObject('scripting.dictionary')arr=Range('A1').CurrentRegion.ValueFori=2ToUBound(arr)'如果字典里没有保存该省份IfNotdicSF.Exists(arr(i,1))Then'创建对应省份的字典SetdicSF(arr(i,1))=CreateObject('scripting.dictionary')EndIf'在该省份的字典内添加所属城市dicSF(arr(i,1))(arr(i,2))=''Nexti'添加数据有效性WithTarget.Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=Join(dicSF.Keys,',')EndWithEndIfEndSub好了，今天分享完毕，各位早点休息。