VBA Fans

最近工作闲了很多，股票行情差到不想看，也就多了时间看看书。之前很少用Excel 2007和2010，所以开始学习2007或2010版本的VBA。
Apress的这本《Pro Excel 2007 VBA》介绍的内容和普通的VBA书籍有些不同，因此适用于对Excel VBA或其它语言有一定了解的学习者。对我来说，像第2章、第3章和第6章的内容之前一直比较少接触，而第9章的内容则可以直接跳过。

目录
第1章：宏记录器和代码模块
第2章：数据进入，数据出来
第3章：在Excel2007中使用XML
第4章：用户窗体
第5章：Excel2007的图表
第6章：透视表
第7章：调试和错误处理
第8章：Office整合
第9章：ActiveX和.Net

PDF下载：爱问
源码下载：Apress

VBA, 图书 VBA

在进行网页内容下载时，经常需要截取指定关键字内的部分文本，以前一般使用Instr、InstrRev、Mid、Left和Right这些字符串函数来分离，这样比较繁琐一些。其实也可以使用正则表达式来处理，这里做一个自定义函数，以后自己就可以这样使用了。

Function TextBetween(strInput As String, strStart As String, strEnd As String) As String
    Dim regEx As Object
    Dim Matches As Object
    Dim strPattern As String
 
    Set regEx = CreateObject("vbscript.regexp")
 
    strStart = Replace(strStart, "\", "\\")
    strEnd = Replace(strEnd, "\", "\\")
 
    strStart = Replace(strStart, "(", "\(")
    strEnd = Replace(strEnd, "(", "\(")
 
    strStart = Replace(strStart, ")", "\)")
    strEnd = Replace(strEnd, ")", "\)")
 
    strStart = Replace(strStart, "*", "\*")
    strEnd = Replace(strEnd, "*", "\*")
 
    strStart = Replace(strStart, ".", "\.")
    strEnd = Replace(strEnd, ".", "\.")
 
    strStart = Replace(strStart, "^", "\^")
    strEnd = Replace(strEnd, "^", "\^")
 
    strStart = Replace(strStart, "$", "\$")
    strEnd = Replace(strEnd, "$", "\$")
 
    strStart = Replace(strStart, "$", "\$")
    strEnd = Replace(strEnd, "$", "\$")
 
    strStart = Replace(strStart, "+", "\+")
    strEnd = Replace(strEnd, "+", "\+")
 
    strStart = Replace(strStart, "?", "\?")
    strEnd = Replace(strEnd, "?", "\?")
 
    strStart = Replace(strStart, "{", "\{")
    strEnd = Replace(strEnd, "{", "\{")
 
    strStart = Replace(strStart, "}", "\}")
    strEnd = Replace(strEnd, "}", "\}")
 
    strStart = Replace(strStart, "|", "\|")
    strEnd = Replace(strEnd, "|", "\|")
 
    strPattern = strStart & "(.*\n*.*)" & strEnd
    regEx.Pattern = strPattern
    regEx.IgnoreCase = True
    regEx.Global = True
    regEx.MultiLine = True
 
    On Error Resume Next
    Set Matches = regEx.Execute(strInput)
    TextBetween = Matches(0).submatches(0)
    Set regEx = Nothing
    Set Matches = Nothing
End Function
 
Sub Test()
    MsgBox TextBetween("abc-this is a test-abc", "-", "-")
End Sub

VBA Excel, RegExp, VBA, 文本处理

有一个这样的想法，其实是可以实现的，只是可能实用性不会太好。

现在的加载宏一般都是手动打开文件运行，感觉可以做一个基于网络的加载宏管理器插件。
先建一个网站，允许网友们上传自己的加载宏，出于安全的考虑，必须是公开源代码。加载宏在审核之后分类呈现，添加版本的适用信息，可以接受用户评分。
然后做一个加载宏插件，显示网站上可用的加载宏，选择自己需要的加载宏后可以一键安装。下面只是一个简单的模版窗口，还可以添加分类、搜索、管理已安装加载宏等等功能。

VBA Addin, Excel, VBA

在Excel中插入图片一般使用Picture集合的Insert方法。 但是不同的版本有一些差别。

下面是Excel2003中的代码。其中Insert方法中的文件路径的文件夹分界符可以是”\”也可以是”/”。另外，设置LoctAspecRatio属性为True并不能固定长宽比，需要同时调整长度和宽度。

Sub InsertImg2003()
    Dim imgWidth As Integer
    Dim fixWidth As Integer
    Dim dRatio As Double
 
    ' 设置图片显示固定宽度
    fixWidth = Cells(2, 2).Width * 5
 
    ' 选择图片插入位置
    Cells(2, 2).Select
    With ActiveSheet.Pictures.Insert("C:\test.jpg")
        ' 获取图片插入后的原始宽度
        imgWidth = .ShapeRange.Width
        ' 获取拉伸比，如果固定显示高度的话用Height属性
        dRatio = fixWidth / imgWidth
        ' Excel2003中设置固定长宽比不起作用
        '.ShapeRange.LockAspectRatio = msoTrue
        ' 调整宽度
        .ShapeRange.ScaleWidth dRatio, msoFalse, msoScaleFromTopLeft
        ' 调整高度
        .ShapeRange.ScaleHeight dRatio, msoFalse, msoScaleFromTopLeft
    End With
End Sub

Excel2007中插入图片的代码基本上一样。但是Insert方法中的文件路径的文件夹分界符只能是”\”。另外设置固定长宽比在Excel2007中有效，所以只需要设置宽度即可。还有一个不同的地方是2007中使用代码插入图片后，图片并不定位于当前选择的单元格，需要设置图片的位置。

Sub InsertImg2007()
    Dim imgWidth As Integer
    Dim fixWidth As Integer
    Dim dRatio As Double
 
    ' 设置图片显示固定宽度
    fixWidth = Cells(2, 2).Width * 5
 
    ' 选择图片插入位置
    Cells(2, 2).Select
    With ActiveSheet.Pictures.Insert("C:\test.jpg")
        ' 获取图片插入后的原始宽度
        imgWidth = .ShapeRange.Width
        ' 获取拉伸比，如果固定显示高度的话用Height属性
        dRatio = fixWidth / imgWidth
        ' 设置固定长宽比，默认为True
        .ShapeRange.LockAspectRatio = msoTrue
        ' 调整宽度
        .ShapeRange.ScaleWidth dRatio, msoFalse, msoScaleFromTopLeft
        ' 不需要重复设置高度
        '.ShapeRange.ScaleHeight dRatio, msoFalse, msoScaleFromTopLeft
        ' Excel2007和2003不同，使用代码插入图片的位置并不位于选择的单元格
        .Left = Cells(2, 2).Left
        .Top = Cells(2, 2).Top
    End With
End Sub

2007的代码可以用于2010，但是当保存文件时，你会发现文件的大小并没有什么改变，实际上在Excel2010里使用代码插入图片只是保存了指向图片的链接，图片本身并没有保存下来。当源图片文件被删除或移走，Excel文件将不能显示图片。一个简单的方法是将图片剪切一下，然后重新粘贴，这样Excel文件中将在保存时包含图片。同时，Cut方法的位置放在ScaleWidth前或者后将影响图片的保存质量。

Sub InsertImg2010()
    Dim imgWidth As Integer
    Dim fixWidth As Integer
    Dim dRatio As Double
 
    ' 设置图片显示固定宽度
    fixWidth = Cells(2, 2).Width * 5
 
    ' 选择图片插入位置
    Cells(2, 2).Select
    With ActiveSheet.Pictures.Insert("C:\xiugai.jpg")
        ' 获取图片插入后的原始宽度
        imgWidth = .ShapeRange.Width
        ' 获取拉伸比，如果固定显示高度的话用Height属性
        dRatio = fixWidth / imgWidth
        ' 设置固定长宽比，默认为True
        .ShapeRange.LockAspectRatio = msoTrue
        ' 调整宽度
        .ShapeRange.ScaleWidth dRatio, msoFalse, msoScaleFromTopLeft
        ' 不需要重复设置高度
        '.ShapeRange.ScaleHeight dRatio, msoFalse, msoScaleFromTopLeft
        ' Excel2010和2007不同，插入的图片位于当前选择的单元格，不需要设置位置
        '.Left = Cells(2, 2).Left
        '.Top = Cells(2, 2).Top
        ' 复制图片让文件保存的时候包含图片
        .Cut
        ActiveSheet.Pictures.Paste.Select
    End With
End Sub

VBA Excel, Office2010, VBA

最近在Excel2010中试用一个以前写的Excel2003的做图工具时碰到一个ChartObject对象的命名错误，这个错误产生的名称在Excel2003中可以正常使用，但到Excel2010中则产生错误。这样发现Excel2003中的ChartObject对象命名的一个奇怪现象。

在Excel工作簿中，图表可以放在两个地方，一个是做为嵌入图表放在工作表里，另外一个是放在图表工作表里。这里讨论前一种情况。

每个图表由一个Chart对象代表，而包含这个Chart对象的就是ChartObject对象。ChartObject对象的属性和方法控制嵌入图表的外观和尺寸，而Chart对象的属性和方法控制图表的内容。

针对嵌入图表，我们可以使用Activate方法激活ChartObject对象，则它所包含的Chart对象也就处于活动状态。这时可以使用ActiveChart来引用这个Chart象。

每个ChartObject对象有一个Name属性，同时每个包含的Chart对象也有一个Name属性。ChartObject对象的名称可以改变，而Chart对象的名称不能修改。如果你改变工作表的名称，Chart对象的名称也会自动改变。

在Excel2003中，你可以通过下面的方法查看ChartObject对象的名称，按下“Ctrl”键，单击图表，在名称栏会显示所选图表的ChartObject对象的名称，并且图表的选择状态也发生了改变。如图：

在立即窗口中做下面的操作（这里是中文版的内容，英文版的结果有些不同）：
输入：?ActiveSheet.ChartObjects(1).Name
返回：Chart 1

输入：?ActiveSheet.ChartObjects(2).Name
返回：Chart 2

输入：?ActiveSheet.ChartObjects(1).Chart.Name
返回：Sheet1 图表 1

如果修改工作表的名称为Sheet2，Chart对象的名称会改成：
输入：?ActiveSheet.ChartObjects(1).Chart.Name
返回：Sheet2 图表 1

将ChartObject对象的名称改成中文，仍然返回”Chart 1″：
输入：?ActiveSheet.ChartObjects(“图表 1″).Name
返回：Chart 1

在ChartObject对象的名称前加一个空格或多个空格，仍然返回”Chart 1″：
输入：?ActiveSheet.ChartObjects(” Chart 1″).Name
返回：Chart 1
输入：?ActiveSheet.ChartObjects(” Chart 1″).Name
返回：Chart 1

在ChartObject对象的名称前加一个”1″，仍然返回”Chart 1″：
输入：?ActiveSheet.ChartObjects(“1Chart 1″).Name
返回：Chart 1
如果前面加了一个”1″，后面跟任何一个字符都可以返回”Chart 1″：
输入：?ActiveSheet.ChartObjects(“1D”).Name
返回：Chart 1

在ChartObject对象的名称前加”1″和多个空格，仍然返回”Chart 1″：
输入：?ActiveSheet.ChartObjects(“1 Chart 1″).Name
返回：Chart 1

输入：?ActiveSheet.ChartObjects(” 1 Chart 1″).Name
返回：Chart 1

但是在”Chart 1″前加上”2″，却返回”Chart 2″：
输入：?ActiveSheet.ChartObjects(“2Chart 1″).Name
返回：Chart 2

在”Chart 2″前加上”2″，也返回”Chart 2″：
输入：?ActiveSheet.ChartObjects(“2Chart 2″).Name
返回：Chart 2

假如工作表中只有两个图表，输入”3Chart 2″则出现错误。

接着来：
如果在立即窗口中输入下面的语句将第1个ChartObject对象的名称改成”Chart 2″。
ActiveSheet.ChartObjects(“Chart 1″).Name=”Chart 2″

在立即窗口你会发现还是可以使用”Chart 1″来指定第1个ChartObject对象，返回的名称却是”Chart 2″：
输入：?ActiveSheet.ChartObjects(“Chart 1″).Name
返回：Chart 2

如果在立即窗口中输入下面的语句将第2个ChartObject对象的名称改成”Chart 3″。
ActiveSheet.ChartObjects(2).Name=”Chart 3″

输入：?ActiveSheet.ChartObjects(“Chart 1″).Name
返回：Chart 2
输入：?ActiveSheet.ChartObjects(“Chart 2″).Name
返回：Chart 2
输入：?ActiveSheet.ChartObjects(“Chart 3″).Name
返回：Chart 3

但是
输入：?ActiveSheet.ChartObjects(“2Chart 2″).Name
返回：Chart 3

上面的这些名称变化使用的是”Chart *”，并不影响使用ChartObjects(“图表 1″)的表示方式，而使用”图表 *”来表示的话同样有上面这些变化。

这些ChartObject的名称变化并不影响其中的Chart对象的名称。总的来说，感觉ChartObject对象在处理名称时忽略了前后的空格。忽略空格后第一个字符是数字的话，则以这个数字作为序号来处理。剩下的名称定义一时没搞明白是怎么处理的。

前面说这么多，其实最终的结论是使用名称来定义ChartObject对象并不是很准确，建议使用序列号并结合Chart对象的名称来准确地定义ChartObject对象。

还好在Excel2007和Excel2010中，这种情况得到了改善。
Excel2007和Excel2010中，查看图表的名称，可以激活图表，然后选择菜单”图表工具“-〉”布局“，单击工具栏”属性“，可以在下拉窗口中看到图表名称，如下图：

在中文版的Excel2010中，可以使用ActiveSheet.ChartObjects(“图表 1″).Name或者ActiveSheet.ChartObjects(“Chart 1″).Name来表示ChartObject对象的名称，但不能在名称前和Excel2003一样添加空格和数字。

但是在Excel2007和Excel2010中仍然将两个图表的ChartObject对象定义成相同的名称。例如在输入下面的语句改变第一个图表的ChartObject对象的名称为”Chart 2″。
ActiveSheet.ChartObjects(“Chart 1″).Name=”Chart 2″

然后在立即窗口
输入：?ActiveSheet.ChartObjects(1).Name
返回：Chart 2

输入：?ActiveSheet.ChartObjects(2).Name
返回：Chart 2

注意：和Excel2003不同的是，Chart对象的名称也一起改变。
输入：?ActiveSheet.ChartObjects(1).Chart.Name
返回：Sheet1 Chart 2
输入：?ActiveSheet.ChartObjects(2).Chart.Name
返回：Sheet1 Chart 2

VBA Chart, Excel, VBA