1、变量 如果给对象项变量赋值,必须用set关键词 如Set sheet=Activesheet 如果加上Option Explicit则变量必须先声明后使用 2、设置函数为易失性函数,让自定义函数也能重新计算 秩序在Function过程中添加一行代码即可. Application.Volatile True ‘将函数设置为易失性函数 3、声明动态数组 (1)可以用 Dim 数组名称() As 数据类型 (2)可以使用Array创建数组,如 Dim arr As Variant arr=Array(1,2,3,4); (3)可以使用Split Dim arr2 As Variant arr=Split(“叶枫”,“张三”); (4)数组常用运算 UBound(数组名称);’数组的最大索引号 LBound(数组名称);’数组的最小索引号 (5)用Join函数将一位数组合并组合成字符串 arr=Split(“叶枫”,“张三”); Txt=Join(arr,”,”); ‘第二个参数为分隔个元素的分隔符,如果省略,默认使用空格。 (6) 将数组中的数据写入单元格区域 arr=Split(1,2,3,4,5,6); Range.(“a1:A6”).Value=Application.WorksheetFunction.trasnpose(arr) ‘transpose函数为行列转置的作用,如果只有一行不需要使用,直接赋值arr即可。 4、在vba中直接使用工作表函数要加前缀Application.WorksheetFunction 如:Application.WorksheetFunction.Sum(“a1:b2”); 5、区域的选择 (1)引用多个不连续的区域 无论有多少区域,参数都只有一个字符串,参数中各个区域的地址用逗号分隔 Range("A1:B2,c1:d2").Select (2)引用多个区域的公共区域 可以将Range属性的参数设置为一个用空格分隔的多个单元格地址组成的字符串 如:Range("B1:B10 A4:D6").Select (3)引用两个区域围成的矩形区域 设置两个用逗号隔开的参数,就可以引用这两个区域围成的矩形区域,注意与第(1)中情况的区别 Range("A1:B2”,”c1:d2").Select (4)使用cells属性引用单元格 Range(Cells(1,1),Cells(10,5)).Select 或者Range(“A1”,”E10”).Select (5)可以使用union合并多个单元格区域 Application.Union(Range("A1:B2"), Range("c1:d2")).Select '同时选中两个区域 (6)Worksheet对象的UsedRange和CurrentRegion属性 UseRange属性返回的总是一个矩形区域,无论这些区域是否存在空行、空列。 CurrentRegion属性,返回指定单元格在内的一个连续的区域,如Range(“B5”).CurrentRegion.Select 只要是断开的区域都不统计,哪怕空行下边、空列右边还有数据。 (7)Range对象的End属性返回制定单元格的区域最尾端的单元格 Dim c As Range Set c = ActiveSheet.Range("A10").End(xlUp) MsgBox c.Address 有有xlToLeft,xlToRight,xlUp,xlDown四种参数分别对应左右上下。 (8)使用offset定位相邻单元格 Range("A1").Offset(0,1) 6、择复制粘贴 (1)分三步骤 Public Sub aa() Range("A1:B2").Select Selection.Copy Range("c1:d2").Select ActiveSheet.Paste End Sub (2)一个命令选择复制粘贴 Range("B1:B10").Copy Destination:=目标单元格 无论复制的区域包含多少单元格,在设置目标区域时,都可以只指定一个单元格作为目标区域最左上角的单元格即可,如: Range("B1:B10").Copy Destination:=Range(“G3”) 7、判断文件夹中是否存在指定名称的文件 Dir函数,如果存在则返回该文件名,否则返回空。 8、将工作簿中的每张表存为单独的文件 Application.ScreenUpdating = False Dim folder As String folder = ThisWorkbook.Path & "\分拆保存目录" If Len(Dir(folder, vbDirectory)) = 0 Then MkDir folder '选择是否新建该文件夹 Dim sht As Worksheet For Each sht In Worksheets vv = sht.Visible sht.Visible = xlSheetVisible sht.Copy sht.Visible = vv ActiveWorkbook.SaveAs Filename:=folder & "\" & sht.Name & ".xlsx" '保存路径 ActiveWorkbook.Close Next Application.ScreenUpdating = True 9、常用的事件 如:Workbook_Open()、OnTimer、WorkSheet_Activate()、Change()等 可以使用Application.OnTime Now() + TimeValue("00:00:01"), "ts"设置定时发生的事件 10、窗体的使用 (1)首先workbook的open事件中代开窗体 Private Sub Workbook_Open() Application.Visible = False '启动时先隐藏EXCEL表 test.Show vbModal '这个参数分vbModal模式窗口和vbModeless非模式窗口两种 End Sub (3)设置窗体的代码 Private Sub UserForm_Initialize() sex.List = Array("男", "女") End Sub Private Sub cmdExit_Click() Unload Me '卸载代码所在的窗体 End Sub Private Sub cmdSave_Click() Dim xrow As Long '定义变量xrow,用来不存要输入数据的工作表行号 xrow = Range("A1").CurrentRegion.Rows.Count + 1 '求工作表中第1条空行的行号,前提是标题行为第一行 '将窗体的信息写入工作表 Cells(xrow, "a").Value = txtname.Value Cells(xrow, "b").Value = sex.Value '将窗体中输入的数据清除,等待下次输入 txtname.Value = "" sex.Value = "" End Sub 11、VBA连接SQL SERVER数据库操作ExcelPrivate Sub cmdDataBase_Click() Dim i As Integer, j As Integer, sht As Worksheet 'i,j为整数变量;sht 为excel工作表对象变量,指向某一工作表 Static nowfield As String '工具 ---〉引用 ---〉Microsoft ActiveX data objects .... '下面两句就不需要增加引用ADO Set cn = CreateObject("Adodb.Connection") Set rs = CreateObject("Adodb.Recordset") Dim strCn As String, strSQL As String '字符串变量 Dim strCond As String strCn = "Provider=SQLOLEDB;Server=(local);Database=db_ibcms;Uid=sa;Pwd=XXXX" '定义数据库链接字符串 '下面的语句将读取数据表数据,并将它保存到excel工作表中:工作表为一张两维表,记录集也是一张两维表 strSQL = "select * from [tb_ttList] " '定义SQL查询命令字符串 Dim result As Variant result = cn.Open(strCn) '与数据库建立连接,如果成功,返回连接对象cn rs.Open strSQL, cn '执行strSQL所含的SQL命令,结果保存在rs记录集对象中 '把sht指向当前工作簿的sheet1工作表 Set sht = ThisWorkbook.Worksheets("sheet1") For j = 0 To rs.Fields.Count - 1 nowfield = rs.Fields(j).Name sht.Cells(1, j + 1).Value = nowfield '将所有的字段名列在第一行 '对每列分别进行赋值,从第2行开始 i = 2 '当数据指针未移到记录集末尾时,循环下列操作 Do While Not rs.EOF '把当前记录的字段值保存到sheet1工作表的第i行第j+1列 sht.Cells(i, j + 1) = rs(nowfield) '把指针移向下一条记录 rs.MoveNext i = i + 1 Loop rs.MoveFirst '必须返回首条记录,否则游标已到底,第一列后就不再填数了 Next '关闭记录集 rs.Close '关闭数据库链接,释放资源 cn.Close End Sub |
|