ET疑难千寻千解丛书之EXCEL2010编程与实践 罗刚君 章兰新 黄朝阳 编著
疑难21
如何让程序兼容Excel多版本 Excel目前最常用的3个版本是Excel 2003、2007和2010,如何让自己的程序可以兼容多个版本呢?例如Excel新版本相对2003增加了行列数、增加了Sort对象强化排序、增加了去重复值功能、去除了FileSearch对象、对单元格颜色大大改进等,如何让程序在Excel 2003、2007和2010中都可以顺利执行呢? è解决方案 为了体现兼容性,通常采用3种思路:一是让程序自动判断Excel版本变化后的实际数据,例如行列数变化;二是写两段代码,让程序根据当前Excel版本调用对应的一段代码;三是使用低版本的方式,因为通常低版本Excel无法使用高版本Excel的部分功能,但高版本可以调用低版本的代码。 í操作方法 步骤1
对于行列数不同引起的兼容性问题,可以利用动态引用的方式处理。Excel 2003及早期版本是65536行´256列,Excel 2007和2010是1048576行´16384列,那么在引用最后一个非空行时,通常采用[a65536].end(xlup)和[a1048576].end(xlup)。然而,两种引用方式都不能在多个版本中兼容,正确的引用方式如下。 cells(rows.Count,1):第一列最后一个非空单元格。其中rows.Count用于计算总行数,可以随Excel的版本变化而变化,相对于65536和1048576有更大的通用性。 cells(1,columns.Count):第一行最后一个非空单元格。其优点同上。 步骤2
对于排序,Excel 2003及早期版本使用Sort方法,而从Excel 2007开始增加了一个Sort对象。Sort对象比Sort方法功能更强大,但无法在Excel 2003及早期版本中使用。所以为了兼容性,可以如下方式处理:
- Sub 排序() '根据版本号执行不同代码
- Dim rng As Range
- Set rng = ActiveSheet.UsedRange '将当前表已用区域赋予变量rng
- '根据版本号执行对应的排序代码,Excel 2007的版本号是12,Excel 2010的版本号是14
- If Application.Version * 1 < 12 Then GoTo line1 Else GoTo line2
- line1: '2007以前的版本使用
- rng.Offset(1, 0).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:
- =xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
- DataOption1:=xlSortNormal
- Exit Sub
- line2: 'Excel 2007和2010专用
- With ActiveSheet.Sort '利用排序对象进行排序
- .SortFields.Clear '清除原有的SortFields对象,该对象存储了排序状态
- .SortFields.Add Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=
- xlAscending, DataOption:=xlSortNormal '添加排序条件
- .SetRange rng '设置排序的区域为rng
- .Orientation = xlTopToBottom '排序方向为纵向
- .Apply '根据当前应用的排序状态对区域进行排序
- End With
- End Sub
复制代码步骤3
对于查找重复值,Excel 2007和2010有专用的RemoveDuplicates方法实现,而Excel 2003及更早的版本只能通过高级筛选完成。那么为了通用性,同样可以采取步骤2相同的思路。代码如下:
- Sub 消除重复值() '根据版本号执行不同代码
- Dim rng As Range
- Set rng = Range([a1], Cells(Rows.Count, 1).End(xlUp)) '将A列所有已用单元
- 格赋值给变量rng
- If Application.Version * 1 < 12 Then GoTo line1 Else GoTo line2
- '根据版本号执行对应的排序代码
- line1: '2007以前的版本使用(虽然2007和2010也可以使用,但效率不如Remove Duplicates方法)
- With rng
- '高级筛选不重复值,将它保存在最后一列
- .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1,
- Columns.Count), Unique:=True
- .Clear '清除原有数据
- Cells(1, Columns.Count).CurrentRegion.Copy .Item(1)
- '将筛选后的不重复值复制到A列
- Cells(1, Columns.Count).CurrentRegion.Delete '清除辅助区数据
- End With
- Exit Sub '退出过程,避免执行Line2
- line2: 'Excel 2007和2010专用
- Intersect(rng, rng.Offset(1, 0)).RemoveDuplicates Columns:=1, Header:
- =xlNo '取不重复值
- End Sub
复制代码步骤4
Excel 2007以前的版本有FileSearch对象,可以方便地进行文件查找;Excel 2007及2010删除了该功能,但可以利用DIR实现相同功能。本例通过DIR方法完成文件查找,替代FileSearch对象,且代码在所有版本中通用:
- Sub 查找文件() 'DIR替代FileSearch对象实现文件查找
- Dim 路径 As String, 文件对象 As String, 文件名称 As String
- 路径 = "D:" '搜索D盘文件
- 文件对象 = "*.*" '搜索所有文件
- 文件名称 = Dir(路径& "" &文件对象) '获取文件名
- Do '开始循环,直到
- Range("A" & i + 1) =文件名称 '将找到的文件名写入A列单元格
- 文件名称= Dir '查找其他文件
- i = i + 1
- Loop Until 文件名称 = ""
- End Sub
复制代码步骤5
Excel 2007开始对单元格的可用颜色从56种升级到43亿种,在Excel 2003中可以使用ColorIndex来表示所有颜色值,但对于Excel 2007和2010则远远不足。如果利用ColorIndex获取或者设置单元格背景,大部分情况下会产生颜色偏差。例如将A1:A5的背色复制到B1:B5,使用以下代码在Excel中完全可行,但在新版中则部分正确、部分错误,如图所示。
- Sub 复制颜色()
- For i = 1 To 5
- Cells(i, 2).Interior.ColorIndex = Cells(i, 1).Interior.ColorIndex
- Next
- End Sub
复制代码
============================= 以上摘自《EXCEL2010编程与实践》 |