VBA事件与典型应用——Excel之VBA（7）

 

关于格式设置中的With语句

With......End With

什么是事件？

Excel事件就是一个能被对象识别的操作。

当某个事件发生后自动运行的过程称为事件过程。事件过程也是Sub过程。

实践过程必须写在特定对象所在的模块中，而且只有过程所在的模块里的对象才能触发这个事件。

简单理解：不用点击，自动执行的宏

 

常用事件：

•Worksheet_SelectionChange事件

•Worksheet_Change事件

•Worksheet_Activate事件

•Workbook_BeforeSave事件

•Workbook_Open事件

•application.EnableEvents事件-启用事件侦听

 

 

With语句

with用来引出作用对象，在with...end with间都是对with作用对象的操作

优点：是简化书写

示例代码：

要求：更改单元格字体大小为18号

Sub 宏1() ' ' 宏1 宏 ' ' Range("AJ3").Select With Selection.Font 　　.Name = "Arial" 　　.Size = 18 　　.Strikethrough = False 　　.Superscript = False 　　.Subscript = False 　　.OutlineFont = False 　　.Shadow = False 　　.Underline = xlUnderlineStyleNone 　　.ColorIndex = xlAutomatic 　　.TintAndShade = 0 　　.ThemeFont = xlThemeFontNone End With End Sub

'如果不进行简写with，则属性的修改很缀长，如下所示 '另外，在with对象内，加上前缀可操作with外对象 With Selection.Font 　　Selection.Font.Name = "Arial" 　　Selection.Font.Size = 18 　　sheet2.range("a1")=1  '操作wiht外对象 　　Selection.Font.Strikethrough = False 　　Selection.Font.Superscript = False 　　Selection.Font.Subscript = False 　　Selection.Font.OutlineFont = False 　　Selection.Font.Shadow = False 　　Selection.Font.Underline = xlUnderlineStyleNone 　　Selection.Font.ColorIndex = xlAutomatic 　　Selection.Font.TintAndShade = 0 　　Selection.Font.ThemeFont = xlThemeFontNone End With

说明：尽管要求是仅修改字体大小，但是在Excel里实际上是打开了字体设置，字体设置的其他属性是在录制宏里是一体的

演示案例：

 

1）高亮显示当前所选单元格整行

 

此处运用•Worksheet_SelectionChange事件

 

Sub 宏2() ' ' 修改背景色为黄色 ' ' With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

Sub 宏3() ' ' 清除整表的背景颜色为无填充 ' ' With Cells.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

'由录制的宏里面的代码整合 Sub test() Cells.Interior.Pattern = xlNone Selection.EntireRow.Interior.Color = 65535 End Sub

 

 

2）输入条件过后自动完成筛选

 

此处运用：•Worksheet_Change事件

注意：慎用change事件，所有的表内改变都会被Change事件侦听，会造成Change侦听无效死循环

Sub shaixuan() Range("l1:q10000").ClearContents Range("a1:f232").AutoFilter field:=4, Criteria1:=Range("i2") Range("a1:f232").Copy Range("l1") Range("a1:f232").AutoFilter End Sub

' 解决方式一： Sub shaixuan() Application.EnableEvents = False '关闭事件侦听 Range("l1:q10000").ClearContents Range("a1:f232").AutoFilter field:=4, Criteria1:=Range("i2") Range("a1:f232").Copy Range("l1") Range("a1:f232").AutoFilter Application.EnableEvents = Ture '开启事件侦听 End Sub

 

3）数据透视表自动更新

此处运用：•Worksheet_Activate事件

 

代码：ActiveWorkbook.RefreshAll

 

4）为重要文件制作文件恢复节点

 此处运用：•Workbook_BeforeSave事件

提要：

Now函数：获取事件

Format函数：（在Excel本身类似Text函数）

代码：ThisWorkbook.SaveCopyAs "d:data" & format(now(), "yyyymmddhhmmss") &".xls"

说明：format，在Vba中类似Text；yyyymmddhhmmss是单元格的日期格式自定义

注：此处要用的是SaveCopyAs，这是除原文件保存外的备份文件，若用Saveas，则是直接另存为，而无备份，源文件未修改

注：拷贝路径需事先存在

附件速查：

With语句：

With Selection 水平对齐方式 垂直对齐方式 自动换行 文字方向 缩进 缩进量 缩小字体填充 文字方向 合并单元格

With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With

字体 字号 删除线 上标 下标 大纲字体 阴影 下划线 字体颜色 颜色变深或变浅 主题字体

With Selection.Font .Name = "华文琥珀" .Size = 9 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With

图案样式 图案颜色 主体颜色 颜色变深或变浅 填充色 对象的淡色和底纹图案

With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -4.99893185216834E-02 .Color = 65535 .PatternTintAndShade = 0 End With

工作表事件：

工作簿事件：

续：