[原创]VBA 学习笔记--不断更新(2008.8.3)

forrest

4) 文件其它操作

4.1) 判断文件存在
   使用Dir 函数判断,格式:Dir[(pathname [,attributes])]
   Dir 会返回匹配 pathname 的第一个文件名.示例如下:
   strfile= Dir(ThisWorkbook.Path & "\filename.xls")   '检查文件是否存在
   If strfile <> "" Then '存在就打开
      Workbooks.Open ThisWorkbook.Path & "\filename.xls"
   Else  '文件不存在就警告
     MsgBox "文件不存在",vbOkOnly,"警告"
   End If

4.2) 搜查文件
使用Application.FileSearch 方法
示例如下:

 pp="D:\Temp":ff=0
 With Application.FileSearch
   .NewSearch
   .LookIn=pp
   .Filename="prn*.tmp"       ' 查找文件prn*.tmp  *为指代符
   If .Exectue()>0 Then ff=.FoundFiles.Count
 End With
 Msgbox pp & " 找到 " & ff & "个档案"

4.3)删除文件
使用kill方法
 示例如下 :kill("c:\mytest.txt")

4.4)取得文件日期属性
  使用FileSystem object 对象实现,示例函数如下

  Function FileDate(filespec,datetype)
     Dim fs,f,s
     Set fs=CreateObject("Scription.FileSystemObject")
     Set f=fs.GetFile(filespec)
     Select Case datetype
         Case 1:FileDate=f.DateCreated     ' 档案建立日期
         Case 2:FileDate=f.DateLastAccessed   '存取日期
         Case 3:FileDate=f.DateLastModified   '修改日期
     End Select
   End function

4.5) FileSystemObject 文件系统对象的使用
          FileSystemObject(FSO) 对象模型,具有大量的属性、方法和事件，使用面向对象的"object.method"语法，来处理文件夹和文件，可以在office2000以后版本使用。FileSystemObject 并不是VBA的一部分，它是以一个COM组件的形式提供，可以在VB、VBA、VBScript 中使用。

      FSO对象模型可以创建、改变、移动和删除文件夹，或探测特写的文件夹是否存在，若存在，还可以找出有关文件夹的信息，如名称、被创建或最后一次修改的日期，或探测特写的文件是否存在，若存在，还可以找出有关文件夹的信息，如名称、被创建或最后一次修改的日期，等等。FSO对象模型还使文件处理变得很容易。可以创建文件，插入和改变数据，以及输出（读取）数据。FSO对象模型，支持通过TextStream对象来创建和操作文本文件，但不支持二进制文件的创建或操作，这可以用VBA语句完成。

使用FileSystemObject 对象模型进行文件操作的步骤是：

1.使用CreateObject 方法来创建FileSystemObject对象;
2. 在新创建的对象上使用适当的方法;
3. 访问对象的属性

使用FSO处理文件夹的任务和方法

创建文件夹   FileSystemObject.CreateFolder
删除文件夹   Folder.Delete 或 FileSystemObject.MoveFolder
复制文件夹   Folder.Move 或 FileSystemObject.CopyFolder
检索文件夹   Folder.Name

如果文件夹在驱动器上存在,则找出它   FileSystemObject.FolderExists
获得现有Folder对象的实例                  FileSystemObject.GetFolder
找出文件夹的父文件夹名                     FileSystemObject.GetParentFolderName
找出系统文件夹的路径                        FileSystemObject.GetSpecialFolder

[此贴子已经被作者于2008-8-7 13:13:49编辑过]

forrest

FSO 中写数据到文本文件的方法

向打开的文本文件写数据,不用后续一个新行字符 Write
向打开的文本文件写数据,后续一个新行字符     WriteLine
向打开的文本文件写一个或多个空白行 WriteBlankLines

FSO 中读取文本文件的方法:

移动文件 File.Move 或FileSystemObject.MoveFile
复制文件 File.Copy 或FileSystemObject.CopyFile
删除文件 File.Delete 或 FileSystemObject.DeleteFile

使用FSO处理文件、文件夹比使用VBA语句的方法具有更易操作的特点，FSO 除了不能处理二进制文件，其文件或文件夹处理的方法也更完备，更直观和易于使用。

forrest

第二节  工作表操作

在文件操作一节已经讲了EXCEL工作簿文件的打开、新建、保存和关闭了，这里再讲文件下的表格操作。

1）新建与删除

新建工作表、图表或宏表。新建的工作表将成为活动工作表。
Sheets.Add(Before,After,Count,Type) 说明：

Before Variant 类型，可选。 指定工作表对象，新建的工作表将置于此工作表之前。
After Variant 类型，可选。指定工作表对象，新建的工作表将置于此工作表之后。
Count VAriant 类型，可选。要新建的工作表的数目。默认值为1
Type  VAriant 类型 可选。指定工作表类型。可以用以下 XlSheetType 常量之一；xlWorksheet.xlChar、xlExcel4MarcoSheet.默认值为xlWorksheet.
如果BEFORE 和After 两者都省略，则新建的工作表将插入到活动工作表之前。

示例：

forrest

示例: Sheets.Add after:=Sheets(Sheets.Count) 该语句可以在最后一个工作表后增加一个新表

删除工作表

Sheets("工作表名").Delete
示例: 在工作表末新建一个和删除倒数第二个表

Sub Mysht()

    Application.DisplayAlerts=False        '关闭删除确认对话框
    Sheets.Add after:=Sheets(Sheets.Count)
    Sheets(Sheets.Count-1).Delete
    Application.DisplayAlerts=True        '开启确认对话框

End Sub

2). 隐藏与显示
使用工作表的Visible 属性来设置工作表是否隐藏或显示

隐藏 Sheets("sheet2").Visible= false
显示 Sheets("sheet2").Visible=True

3) 保护与撤销

保护工作表使其不至被修改
   Sheets("工作表名").Protect(Password)

撤消工作表保护使其被修改
   Sheets("工作表名".UnProtect(Password)

Password Variant 类型，可选。为一个字符串，该字符串为工作表或工作簿指定区分大小写的密码。如果省略本参数，不用密码就可以取消以该工作表或工作簿的保护。否则，必须指定密码，通过密码来取消对该工作表或工作簿的保护。

    示例： Sheets("Sheet1").Proctect "123456"  Sheets("sheet1").Unprotect "123456"

如果要对工作进行详细保护设置，可参考VBA详解。

[此贴子已经被作者于2008-8-10 16:44:24编辑过]

forrest

第三节 单元格和区域操作

EXCEL 数据的计算归根到底还是对表的单元格进行的，所以单元格和区域操作非常重要的，这一节就详细介绍这方面的内容。

一、如何引用单元格和区域

1）用Range 属性引用单元格和单元格区域

使用 Range 属性来引用A1 引用样式中的单元格或单元格区域及进行属性更改和赋值。如例句：

Sheets("Sheet1").Range("A1:D5").Font.Bold=True
Sheets("Sheet1").Range("A1:D5").Value=10000

Range 对象既可表单个单元格，也可表单元格区域。下面说明 Range 对象最常用方法。

引用                    含义

Range("A1")            单元格A1
Range("A1:B5")         从单元格 A1 到单元格 B5 的区域
Range("C5:D9,G9:H16") 多块选定区域
Range("A:A")           A 列
Range("1:1")           第一行
Range("1:5")           从第一行到第五行的区域
Range("1:1,3:3,8:8")   第1、3和8行
Range("A:C")           从A列到C列的区域
Range("A:A,C:C,F:F")   A、C和F列
Range("单元格区域名称") 命名的单元格区域

2） 用Cells 属性引用单元格

可用Cells 属性通过行列编号来引用单个单元格。下例中Cells(6,1) 返回Sheet1 上的单元格 A6,然后将Value 属性设置为行列编号，所以 Cells 属性非常适合单元格区域中循环且速度很快。如下例所示：

Sub CC()

    Dim Counter As Integer
    For Counter =1 To 20
       Worksheets("Sheet1").Cells(Counter,3).Value=Counter

    Next Counter
End Sub

3) 用Rows 或 Columns 属性引用行列
    可用Rows属性或Columns属性来处理整行或整列。下例中，用Row(1)返回Sheet1上的第一行，然后将单元格区域的Font对象的Bold属性设置为True. 如： Worksheets("Sheet1").Row(1).Font.Bold=True

下表举例说明了使用 Rows 和 Columns 属性的一些行和列的引用。

引用               含义

Row(1)            第一行
Rows              工作表中的所有行
Columns(1)        第一列
Columns("A")      第一列
Columns           工作表中的所有列

5）用括号[]引用单元格和区域

可用方括号A1 引用样式或命名区域括起来，作为Range 属性的快捷方式。这样就不必键入单词"Range"或使用引用，如下例所示： Worksheets(Sheet1").[A1:B5].ClearContents
[MyRange].Value=30

6) 用offset 相对其他单元格来引用单元格

处理相对另一个单元格的某一单元格的常用方法是使用offset 属性。下例中，将位于活动工作表上活动单元格下一行和右边三列的单元格的内容设置为双下划线格式。
如： ActiveCell.Offset(1,3).Font.Underline=xlDouble

7) 用 Selection 属性引用活动区域

Select 方法激活工作表和工作表上的对象；而 Selection 属性返回代表活动工作簿中活动工作表上的当前选定区域的对象。在成功使用 Selection 属性之前，必须先激活或选定工作表，然后用 Select方法选定单元格区域（或其他对象）。

宏录制器经常创建使用 Select 方法和Selection属性的宏。下述 Sub 过程是用宏录制器创建的，该过程演示了 Select 方法和Selection 属性在一起使用的方法。

Sub Marco1()

       Sheets("Sheet1").Select
        Range("A1").Select
        ActiveCell.FormulaR1C1="Name"
        Range("B1").Select
        Active("A1:B1").Select
        Selection.Font.Bold=True

End Sub

8) 区域中循环引用单元格方法

     使用VBA 时，经常需要对某一个单元格区域内的每个单元格运行同一段语句。为达到这一目的，可组合循环语句和一个或多个方法来标识每个单元格，一次针对一个单元格，并执行该操作。

8.1)  For ... Next 循环语句与 Cells 属性配合使用

    使用 Cells 属性时,可用循环计数器(或其他变量或表达式) 来替代单元格索引编号.下例中变量 counter 代替行号.此过程单元格区域C1: C20中循环,将绝对值小于0.01单元都置为0.

 Sub RoundTab1()

    For Counter=1 To 20
          If Abs(Cells(counter,3).value) <0.01 Then Cells(Counter,3)=0

    Next

End Sub

[此贴子已经被作者于2008-8-10 17:36:52编辑过]

forrest

二、如何处理单元格和区域

1.处理三维区域

如果要处理若干工作表上相同的单元格区域,可用 Array函数选定两张或多张工作表.下例设置三维单元格区域的边框格式.

Sub FormatSheets()
     Sheets(Array("Sheet2","Sheet3","Sheet5")).Select
     Range("A1:H1").Select
     Selection.Borders(xlBottom).LineStyle=xlDouble

End Sub

2 处理活动单元格

ActiveCell属性返回代表活动单元格的Range 对象。可对活动单元格应用 Range对象的任何属性和方法，如下例所示。

Sub SetValue()

    Worksheets("Sheet1").Active
    ActiveCell.Value=35

End Sub

注意  只有活动单元格所在的工作表处于活动状态时，才能处理该活动单元格。

3 选择活动单元格周围的单元格

 CurrentRegion 属性返回由空白行和空白列所包围的单元格区域。下例中，选定区域扩充到活动单元格相邻的包含数据的单元格中，然后用“货币”样式设置该区域的格式。

Sub Region()

    Worksheets("Sheet1").Activate
    ActiveCell.CurrentRegion.Select
    Selection.Style="Currency"
End Sub

4 单元格和区域赋值

用赋值号=赋值，如给A1 赋值为10；Range(A1).value=10(由于Value 是默认属性,所以可以直接省略)区域赋值,如 Range(A1:D5)=10

5 单元格和区域赋予公式进行计算

把公式符串赋予给单元格或区域的Formula属性，如下例：注意一点是公式字符串要有开头的等号=。
Sub EnterFormula()

    Worksheets("Sheet1").Range("D6").Formula= "=sum(D2:D5)"

End Sub

[此贴子已经被作者于2008-8-11 8:44:00编辑过]

forrest

三、单元格和区域的定位

1）使用工作单元格的END 属性来定们边界

如：

数据区域的最下行的确定：Sheets(1).Cells(65536,1).End(xlup).Row
数据区域的最右列的确定：Sheets(1).Cells(1,1).End(xltoright).Column
选定包含单元格“B4”的区域在B列中顶端的单元格： Range("B4").End(xlUp).Select
选定饮食单元格“B4”的区域在第四行中最右端的单元格：Range("B4").End(xlToRight).Select
将选定包含单元格“B4”延伸至第四行最后一个包含数据的单元格：
Range("B4",Range("B4").End(xlToRight)).Select

2） 合理利用已用区域UseRange对象来获得区域范围
如：需要获得先前表格已用区域的最右下角单元格位置

Sub Loc()
    temp=[a1]:[a1]=1         '使用A1单元格，保证已用区域从A1开始
    irow=ActiveSheet.UsedRange.Rows.Count         '右下单元格行
    icol=ActiveSheet.UsedRange.Columns.Count      '右下单元格列
    [a1]=temp                        '还原A1的值
    Cells(irow,icol).Select          '选定右下角单元格
End Sub

四、单元格和区域的保护与锁定

1）工作表选择改变事件过程中的保护，如下：不许可用户选择及改动保护单元格区域，示例中保护B1:B10
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim mg As Range
    Set mg=Range("b1:b10")
    If Intersect(Target,mg) Is Nothing Then Exit Sub       ‘选择单元格不在保护区内就退出
    Msgbox "不可以选取单元格！" &  Target.Address           '选择单元在保护内就改变选择
    ActiveSheet.Range("a1").Select

End Sub

2) 通过行列的隐藏来保护
    本示例隐藏工作表 "Sheet1" 的第5行和C列
  Sub SetHide()

           With Worksheets("Sheet1")     '使用With...End With 语句块，提高效率
            .Unprotect                               '撤销保护，如果原来未保护则不需该句
             .Row(5).Hidden=True             '隐藏第5行
            .Columns("C").Hidden=True   ' 隐藏C列
             .Protect                                 ' 启动保护,如果原来未保护则不需该句
            End With
End Sub

3) 通过锁定区域来保护,可限定未锁定区域内输入

Sub SetLock()

    ActiveSheet.Unproject
    Cells.Locked=True                      '锁定全表
     Range("A1:b10").Locked=false  '解锁区域A1:B10,限定该区域为可输入区
    ActiveSheet.Project
End Sub

[此贴子已经被作者于2008-8-11 12:58:52编辑过]

forrest

第四节 图表的操作 

略

forrest

                第三章  EXCEL VBA 高级使用

    通过以上章节的学习,估计大家都能够使用EXCEL VBA进行基本的数据计算、数据汇总、数据保存、数据库的使用和绘制图表，这些功能已经可以解决我们平时所遇到的大多数问题。但有时还会遇到一些较难的问题，如计算机硬件或底层方面的使用。这些问题可以使用本章介绍的 Windows API 函数来解决。

    Windows API 是Windows 32位应用程序编程接口，是一系列复杂函数、消息和结构的集合。这种集合被包含在一个后缀名为DLL的动态连接库文件中，装有WINDOWS系统的电脑都有标准的WINDOWS动态连接库文件。编程人员可用不同编程语言的引用方法来使用它们，进而编制出解决WINDOWS系统底层问题的应用程序。如多媒体的播放，只有会了WINDOWS API 才算真正进入了WINDOWS系统下程序开发的大门。

第一节 WIN API 的使用

API（Application Programming Interface),也就是WINDOW32位操作系统的应用程序编程接口。我们可以认为API函数是构筑整个

WINDOWS框架的基石，在它的下面是WINDOWS的操作系统核心，而它上面则是WINDOWS的应用程序。在EXCEL VBA中使用API

就是为了开发出实用高效的应用程序，而VBA下使用API函数进行API声明才能使用。

一、声明API函数
    声明VBA所在文件之外的过程或函数就能够访问WINDOWS API或其它外部动态链接库（DLL）。在声明了过程和函数后，其调用方法与VBA自己的过程或函数调用方法相同。要声明一个DLL文件中的过程或函数，需要在代码窗口增加一个Declare 语句。例如取的计算机名称的函数GetComputerName,作如下声明
Private declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String,nSize As Long) As Long

或

Public Declare Function GetComputerName Lib"kernel" Alias "GetComputerNameA" (ByVal lpBuffer As String,nSize As Long) As Long

    以上声明的不同在于所声明函数的使用范围，Private Declare声明的是模块私有，只能在声明它的模块内调用；Public Declare 声明的是全局函数，可以在应用程序的任何地方调用，一般我们使用Public Declare 声明。声明完毕后就能在程序中使用此函数。

[此贴子已经被作者于2008-8-11 13:38:36编辑过]

forrest

                第三章  EXCEL VBA 高级使用

    通过以上章节的学习,估计大家都能够使用EXCEL VBA进行基本的数据计算、数据汇总、数据保存、数据库的使用和绘制图表，这

些功能已经可以解决我们平时所遇到的大多数问题。但有时还会遇到一些较难的问题，如计算机硬件或底层方面的使用。这些问题可

以使用本章介绍的 Windows A PI 函数来解决。

    Windows A_P_I 是Windows 32位应用程序编程接口，是一系列复杂函数、消息和结构的集合。这种集合被包含在一个后缀名为

DLL的动态连接库文件中，装有WINDOWS系统的电脑都有标准的WINDOWS动态连接库文件。编程人员可用不同编程语言的引用方

法来使用它们，进而编制出解决WINDOWS系统底层问题的应用程序。如多媒体的播放，只有会了WINDOWS AP I 才算真正进入了

WINDOWS系统下程序开发的大门。

第一节 WIN API 的使用

API（Application Programming Interface),也就是WINDOW32位操作系统的应用程序编程接口。我们可以认为API函数是构筑整个

WINDOWS框架的基石，在它的下面是WINDOWS的操作系统核心，而它上面则是WINDOWS的应用程序。在EXCEL VBA中使用API

就是为了开发出实用高效的应用程序，而VBA下使用API函数进行API声明才能使用。