1、1. ，常用的、带解释的 VBA 短句 by:yuhongpuhttp:/ BL 为长整型 myNow = Now 把当前的时间赋给变量myNowDo 开始循环语句 DoBL = Second(Now) - Second(myNow) 循环中不断检查变量 BL 的值If BL = 30 Then GoTo Cl 当 BL=30 即跳转到 CLDoEvents 转让控制权,以便 sheets 可继续操作Loop Until BL 30 当 BL30 即跳出循环Exit SubCl:Application.EnableEvents = False 避免引起其他事件 ActiveWorkbook.C
2、lose True 关闭活动工作簿并保存Application.EnableEvents = True 可触发其他事件End Subrange(“e4“).addcomment.Text “代头“ 来一个函数的Private Sub Worksheet_SelectionChange(ByVal Target As Range)右边单元格反向显示活动单元格文本If ActiveCell.Column 100“, Operator:=xlAnd, _Criteria2:=“ 4 ThenIf InputBox(“密码:“, “ 请确认密码“, 4) = a ThenSheets(a).Selec
3、tElseSheets(4).SelectEnd IfEnd IfEnd Sub4. 浮动按钮 by:Qee 用http:/ Properties=Excel 8.0;Hdr=No;imex=1;Data Source=“ “Range(“A6“).SelectApplication.ScreenUpdating = TrueEnd Sub23. ADODBRecordset 中取不重复值Public Sub RunMacro()http:/ “ “ & _“ReadOnly=True“lcCommandText = “select distinct 学生,到过的国家 from sheet1$
4、“Set loADODBConnection = CreateObject(“ADODB.Connection“)Set loADODBRecordset = CreateObject(“ADODB.Recordset“)loADODBConnection.Open lcConnectionStringloADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1Sheets.AddDim r, f As Integerr = 1For f = 0 To loADODBRecordset.Fields.Count - 1Sheet
5、s(1).Cells(r, f + 1) = loADODBRecordset.Fields(f).NameNextWhile Not loADODBRecordset.EOFr = r + 1For f = 0 To loADODBRecordset.Fields.Count - 1Sheets(1).Cells(r, f + 1) = loADODBRecordset.Fields(f).ValueNextloADODBRecordset.MoveNextWendloADODBConnection.CloseEnd Sub24. 统计及取不重复值 by:oobirdBy:oobirdhtt
6、p:/ Macro1()Dim ww = “Sheet1!“ & Application.ConvertFormula(Sheet1.UsedRange.Address, xlA1, xlR1C1, 1) 转换公式成地址转换”Sheet1!R1C1:R14C4”With Sheet2.UsedRange.Clear.a1.Consolidate w, xlSum, 1, 1 Sources:=w,Function:=xlSum,TopRow:=True,LeftColumn:=TrueEnd WithEnd SubSub yy()by:oobirdDim n%With Sheet1n = .a
7、1.End(xlDown).Row A1 是字段名.Range(“A1:A“ & n).AdvancedFilter 2, , Sheet2.a1, 1End WithEnd SubMatch 方法：查找数组第 2 列里有“A”字符串：arr = a1:b10arr2 = Application.Index(arr, , 2)If IsError(Application.Match(“A“, arr2, 0) ThenMsgBox “不包含 A“ElseMsgBox “包含 A“End IfFilter 方法：If UBound(Filter(Application.Transpose(arr2), “A“) = 0 Then MsgBox “包函 A“25. 数组赋值及取不重复值 By:山菊花Private Sub CommandButton1_Click()http:/
