完美Excel

VBA7的新功能

excelperfect — Mon, 01 Feb 2010 14:38:56 +0800

据Excel产品团队博客的文章《Programmability Improvements in Excel 2010》，VBA增加了一些新功能。
将Excel 4宏迁移到VBA
在将VBA引入到Excel之前，有自已的宏工具，称之为Excel 4宏，在Excel宏工作表中使用。很多人都早已将他们的Excel 4宏迁移到VBA，然而，一些Excel 4宏的功能在VBA中消失了，这使得迁移发生了困难。
在Excel 2010中，目标是为必须完全将Excel 4宏迁移到VBA中的人移除任何现有的阻碍。这项工作通常分为两类：

在Excel的对象模型中添加新的对象、方法和属性，代表一些现有的而在VBA中没有等价的Excel 4宏函数。
改进VBA中页面设置操作的性能，提供Excel 4宏中PAGE.SETUP()功能相似的性能。

对图表和形状的宏录制
当Excel 2007引入新的图表引擎时，主要集中在现代化的图表和形状的渲染能力。由于这项工作巨大，因此必须作一些痛苦的取舍决定，这导致Excel 2007在图表和形状对象模型方面失去了与Excel 2003的兼容性，即宏录制能力。
在Excel 2010中，宏录制器又可用于图表格式了，Excel 2003中的绝大多数图表格式录制功能又回来了。（极少数例如，包括坐标轴文本格式）
64位支持和代码兼容性
Office 2010将发布32位版本和64位版本。因此，VBA7将随Office 2010发布，支持能够运行32位和64位Office的代码的开发。

VBA7来了

excelperfect — Mon, 01 Feb 2010 13:56:26 +0800

VBA6已经将近10余年了，而今，VBA7即将到来。
据The official blog of the Microsoft Excel product team的一篇文章：Programmability Improvements in Excel 2010：VBA的新版本——VBA7，将随Office 2010发布，支持能够在32位和64位Office上运行的代码的开发。

VBA7, the new version of VBA that ships with Office 2010, supports the development of code that can run in both 32- and 64-bit Office.

让Excel自动响应消息框中的提示

excelperfect — Tue, 26 Jan 2010 12:53:54 +0800

这是以前收录的一段程序示例：
使用VBA在调用工作簿中打开被调用工作簿，并运行其中的宏。该宏将弹出一个消息框，如果用户没有响应，那么程序会在指定时间后自动响应。
调用工作簿中的程序代码如下：

Public Declare Function SetTimer& Lib "user32" (ByVal hwnd&, _
    ByVal nIDEvent&, ByVal uElapse&, ByVal lpTimerFunc&)
Private Declare Function KillTimer& Lib "user32" (ByVal hwnd&, _
    ByVal nIDEvent&)
Public Const NV_INPUTBOX As Long = &H5000
 
Public Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idEvent As Long, ByVal dwTime As Long)
    SendKeys "%Y"
    KillTimer hwnd, idEvent
End Sub
 
Sub test()
 
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    Dim targetworkbook As Workbook
 
    Dim usersave As VbMsgBoxResult
 
    Set targetworkbook = Workbooks.Open("C:\test2.xls", UpdateLinks:=0)
 
    Calculate
    targetworkbook.Activate
    SetTimer 0, NV_INPUTBOX, 1000, AddressOf TimerProc
    Application.Run targetworkbook.Name & "!tester"
 
    targetworkbook.Activate
 
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
 
End Sub

假设被调用工作簿存储在C盘，工作簿名为test2.xls，那么其中的代码如下：

Sub tester()
    TimedMsgBox
End Sub
 
Sub TimedMsgBox()
    Dim cTime As Long
    Dim WSH As Object
 
    Set WSH = CreateObject("WScript.Shell")
    cTime = 5 '5 秒
    Select Case WSH.Popup("Open an Excel file?!", cTime, "Question", vbOKCancel)
        Case vbOK
            MsgBox "你单击了确定"
        Case vbCancel
            MsgBox "你单击了取消"
        Case -1
            MsgBox "超时"
    End Select
End Sub

运行调用工作簿中的程序后，被调用的工作簿test2.xls会被打开，并运行其中的程序，该程序会弹出一个消息框，如果用户没有响应该消息框，那么在5秒后，会自动响应，即弹出一个内容为“超时”的消息框。

带有菜单栏的用户窗体

excelperfect — Thu, 21 Jan 2010 13:17:12 +0800

今天，给大家分享Jan Karel Pieterse的作品：带有菜单栏的用户窗体。如下图所示。

示例文档下载：

Excel图表类型及使用

excelperfect — Wed, 20 Jan 2010 13:00:16 +0800

引言：记得曾经有朋友问过：Excel各种类型的图表适合在什么情形下使用？虽然我平时不常使用图表，但碰巧看过人民邮电出版社出版过的两本书：《Excel 2007图表完全剖析》和《PPT演示之道》，其中谈到过常用图表类型的适用情形，特将这些内容归纳于此，供有兴趣的朋友参考。更详细的内容大家可以阅读这两本书。同时，大家也可以共同探讨，根据自已使用图表的经验，谈谈什么情形下适用什么图表类型。
Excel提供了11种类型的图表，其中又含有73种图表子类型。
柱形图

经常用于表示以行和列排列的数据。对于显示随时间的变化很有用。最常用的布局是将信息类型放在横坐标轴上，将数值项放在纵坐标轴上。
折线图

与柱形图类似，也可以很好地显示在工作表中以行和列排列的数据。区别在于折线图可以显示一段时间内连续的数据，特别用于显示趋势。
饼图

适合于显示个体与整体的比例关系。显示数据系列相对于总量的比例，每个扇区显示其占总体的百分比，所有扇区百分数的总和为100%。在创建饼图时，可以将饼图的一部分拉出来与饼图分离，以更清晰地表达其效果。
下列图表类型是用于比较组分的有效图表：
（1）饼图适合用于比较2～5个组分。
（2）复合饼图适合用于比较6～10个组分。
（3）复合条饼图可处理6～15个组分。
（4）如果有两个或多个饼图，应使用百分比堆积柱形图。百分比堆积条形图、百分比堆积折线图以及百分比堆积面积图都是由百分比堆积柱形图衍变而来的。
条形图

对于比较两个或多个项之间的差异很有用。
面积图

面积图是以阴影或颜色填充折线下方区域的折线图，适用于要突出部分时间系列时，特别适合于显示随时间改变的量。如果只有几个数据点，添加垂直线有助于读者分辨每个时期的实际值。
XY（散点）图

适合于表示表格中数值之间的关系，常用于统计与科学数据的显示。特别适合用于比较两个可能互相关联的变量。虽然散点图可用于大型数据集，但其缺陷是，如果不使用VBA或插件，将很难给点加上标签。如果数据集较小，可使用成对的条形图来显示相同的信息，这种图表可添加标签，让读者能够看出哪些记录与其他记录不符。
气泡图

与散点图相似，但气泡图不常用且通常不易理解。气泡图是一种特殊的XY散点图，可显示3个变量的关系。气泡图最适合用于较小的数据集。
气泡图给XY散点图中的每个点添加一些信息，在气泡图中标记的大小将随第三个数据点而异。数据集很稀疏时，使用气泡图最合适。如果图表包含的数据点太多，气泡将导致图表很难看懂。
散点图与气泡图都能够显示两三个不同的变量之间的关系。创建数据时要小心，散点可显示两个变量之间是否存在着关系，而气泡图有一项独特功能，即能够提供第三维数据。
股价图

常用于显示股票市场的波动，可使用它显示特定股票的最高价/最低价与收盘价。
曲面图

适合于显示两组数据的最优组合，但难以阅读。
圆环图

与饼图一样，圆环图显示整体中各部分的关系。但与饼图不同的是，它能够绘制超过一列或一行的数据。圆环图不容易阅读。
圆环图是一种奇怪的图表，有时可用于比较两个饼图，但通常百分比堆积柱形图更适合这种比较。
雷达图

可用于对比表格中多个数据系列的总计，很适合用于进行绩效评估，但不太常用。雷达图可显示4～6个变量之间的关系。
雷达图用于显示个人或公司在几个方面的表现，下面是一些典型的用法。
（1）员工绩效评价。例如，经理可能用1～5分评价员工在效率、准确性、时间观念等方面的表现。虽然这些数据可使用表格来表示，但使用雷达图表示更有趣。
（2）客户满意度。例如，营销经理可使用雷达图总结客户满意度调查结果。此时，应使用一行显示客户在速度、准确性和价值等几个方面的满意度。
- – - – - – - – - – - – - – - – - – -
只能混合使用某些图表类型。混合使用图表类型时，为获得最佳效果，建议遵循下列原则：
（1）应总是使用二维图表类型，Excel不允许混合使用三维图表。
（2）在二维类型中，应只混合使用簇状图表类型。
（3）不能混合使用水平图表和垂直图表。柱形、折线与面积图应放在一起，条形图不能与它们混合。
（4）可以混合使用圆形图表。
（5）在大部分情况下，折线图可改为带直线的XY散点图。

当然，图表类型的选择要结合实际要表达的意图。其最终目的是，以最简洁的型式，表达出要展现的结果。
注:网站空间被删之前的文章,找出来重新辑录于此.

在一个列表框中显示另一列表框中的具体内容

excelperfect — Tue, 19 Jan 2010 19:07:27 +0800

有时，需要选中某列表框的的项目后，在另一列表框中显示该项目的具体内容，如下图1所示。

图1：两个列表框实现内容匹配
“专业工程”包括“建筑工程、装饰装修工程……”等，当选中项目列表框中的专业工程后，在分类列表框中显示相应的内容。同理，选择项目列表框中的措施项目后，在分类列表框中也会显示相应的内容。
第一步：准备数据
如下图2所示，在工作表Sheet1中输入下列数据。

图2：基础数据
将A列的数据命名为“项目”，将B列的数据命名为“专业工程”，将C列的数据命名为“措施项目”。注意，B列和C列的名称应与A列的数据相一致。
第二步：准备窗体界面
如图1所示，在用户窗体中放置两个标签控件、两个列表框控件，并将两个列表框控件分别命名为lbxItem和lbxCategory。
第三步：输入代码
在用户窗体代码模块中，输入下列代码：

Private Sub lbxItem_Change()
    Dim rngCategory As Range
 
    Set rngCategory = Sheet1.Range(Me.lbxItem.Value)
 
    Me.lbxCategory.List = rngCategory.Value
End Sub
 
Private Sub UserForm_Initialize()
    Dim rngItem As Range
 
    Set rngItem = Sheet1.Range("项目")
 
    Me.lbxItem.List = rngItem.Value
End Sub

运行后的最终效果如图1所示。当然，您可以在A列添加更多的项目数据，然后在C列、D列……添加相应的内容并以A列的数据命名，从而扩展本实例。
(注：网站空间被删前的文章，有一定的学习意义，故找出来重新贴出之)

使用VBA求解汉诺塔问题

excelperfect — Mon, 18 Jan 2010 12:37:06 +0800

这是在网站空间被误删前发的一个贴子，翻出来重新贴出。
据百度百科：
汉诺塔（又称河内塔）问题是印度的一个古老的传说。开天辟地的神勃拉玛在一个庙里留下了三根金刚石的棒，第一根上面套着64个圆的金片，最大的一个在底下，其余一个比一个小，依次叠上去，庙里的众僧不倦地把它们一个个地从这根棒搬到另一根棒上，规定可利用中间的一根棒作为帮助，但每次只能搬一个，而且大的不能放在小的上面。面对庞大的数字(移动圆片的次数)18446744073709551615，看来，众僧们耗尽毕生精力也不可能完成金片的移动。
后来，这个传说就演变为汉诺塔游戏：
1、有三根杆子A,B,C。A杆上有若干碟子
2、每次移动一块碟子，小的只能叠在大的上面
3、把所有碟子从A杆全部移到C杆上
经过研究发现，汉诺塔的破解很简单，就是按照移动规则向一个方向移动金片：
如3阶汉诺塔的移动：A→C,A→B,C→B,A→C,B→A,B→C,A→C
此外，汉诺塔问题也是程序设计中的经典递归问题。
算法思路：
1、如果只有一个金片，则把该金片从源移动到目标棒，结束。
2、如果有n个金片，则把前n-1个金片移动到辅助的棒，然后把自己移动到目标棒，最后再把前n-1个移动到目标棒
- – - – - – - – - – - – - – - – -
这是一个很有趣的问题，也是一个迷人的问题，相信学过计算机特别是递归的朋友都熟悉这个经典的问题。问题简单描述如下：
设有3根标号为A，B，C的柱子，在A柱上放着n个盘子，每一个都比下面的略小一点，要求把A柱上的盘子全部移到C柱上，移动的规则是：（1）一次只能移动一个盘子；（2）移动过程中大盘子不能放在小盘子上面；（3）在移动过程中盘子可以放在A，B，C的任意一个柱子上。
其解答的基本思想是使用递归的方式：
1、1个盘子的汉诺塔问题可直接移动。
2、n个盘子的汉诺塔问题可递归表示为，首先把上边的n-1个盘子借助于C柱从A柱移到B柱，然后把最下边的一个盘子从A柱移到C柱，最后把移到B柱的n-1个盘子借助于A柱再移到C柱。4个盘子汉诺塔问题的递归求解示意图如下图所示。

程序代码：

Option Explicit
Dim Counter As Integer
 
Sub GetMove()
    Dim n As Integer
    On Error Resume Next
    n = Application.InputBox(Prompt:="请输入一个代表要移动盘子的数量值:", _
                                  Title:="汉诺塔问题", _
                                  Type:=2)
    Call Move(n, "A", "B", "C")
End Sub
 
Sub Move(ByVal nValue As Integer, ByVal A As String, ByVal B As String, ByVal C As String)
        If nValue = 1 Then
            Counter = Counter + 1
            Debug.Print Counter & ":" & "将盘" & "1" & ":" & "从柱" & A & "移到柱" & C
        Else
            Call Move(nValue - 1, A, C, B)
            Counter = Counter + 1
            Debug.Print Counter & ":" & "将盘" & nValue & ":" & "从柱" & A & "移到柱" & C
            Call Move(nValue - 1, B, A, C)
        End If
End Sub

程序运行，将n值设置为4时的输出为：
1:将盘1:从柱A移到柱B
2:将盘2:从柱A移到柱C
3:将盘1:从柱B移到柱C
4:将盘3:从柱A移到柱B
5:将盘1:从柱C移到柱A
6:将盘2:从柱C移到柱B
7:将盘1:从柱A移到柱B
8:将盘4:从柱A移到柱C
9:将盘1:从柱B移到柱C
10:将盘2:从柱B移到柱A
11:将盘1:从柱C移到柱A
12:将盘3:从柱B移到柱C
13:将盘1:从柱A移到柱B
14:将盘2:从柱A移到柱C
15:将盘1:从柱B移到柱C

关于《Excel VBA编程入门范例》

excelperfect — Fri, 15 Jan 2010 13:17:28 +0800

早在2006年9月，我在学习Excel VBA的过程中，选取了一些比较简短且简单的示例，通过程序示例向初学者介绍Excel VBA。写作工作一直持续到当年底，完成了前6章，并陆续在ExcelHome论坛和博客上发表。本来打算一直写下去，最后汇集成一本电子图书，帮助希望学习Excel VBA的朋友。无奈事务繁忙，最后还是搁置了。
在《Excel VBA编程入门范例》陆续发布过程中和发布后，收到了大量网友的推荐和鼓励，在EH博客中浏览总人数达2万多，在EH论坛中浏览人数达5万多，并且很多网友都在持续关注中，不少网友给我发邮件，询问这本书的后续部分。
在这里，谢谢大家的关注！
2010年，我将尽量抽出时间，完成后续部分的写作，与大家分享！

下面是当初写作《Excel VBA编程入门范例》时的一些摘录。
目录
《ExcelVBA编程入门范例》汇集了ExcelVBA大多数基本操作实例。这些实例都很简短，部分实例很基础，也很容易理解，一个实例基本上演示了使用VBA实现Excel某一功能的操作。此外，在各实例中，配有一些说明和图片以帮助对实例所演示功能的理解，很适合VBA初学者或对VBA有一定了解的人对所学的知识进行整理并提高。下面是目录，共分16章。
Ch00 前言
Ch01 Excel应用程序对象(Application对象)及常用方法基本操作应用示例
Ch02 窗口(Window对象)基本操作应用示例
Ch03 工作簿(Workbook)基本操作应用示例
Ch04 工作表(Worksheet)基本操作应用示例
Ch05 单元格/单元格区域基本操作应用示例
Ch06 图表基本操作应用示例
Ch07 数据透视表基本操作应用示例
Ch08 格式化基本操作应用示例
Ch09 形状(图形)基本操作应用示例
Ch10 工作表和用户窗体的控件基本操作应用示例
Ch11 菜单和工具栏基本操作应用示例
Ch12 Office帮助助手基本操作应用示例
Ch13 文件基本操作应用示例
Ch14 常用方法、函数及技巧应用示例
Ch15 其它
前言
很喜爱VBA，喜欢使用她对Excel操作实现所需的功能，更喜欢使用VBA控制Excel以及实现结果后的那种感觉。
一直都想对ExcelVBA进行系统的整理和归纳，但由于对Excel及VBA了解得不够深入，总觉得无从下手。再加上又是利用少得可怜的业余时间进行学习，时断时续，学习的主线和思路也经常因为工作或其它的事情而打断。但喜欢学习的人总会挤得出时间来的，要想掌握或者是精通一门知识和技术不能有任何借口。幸运的是，有网络这个大平台，更有ExcelHome众多网友的帮助和鼓励，这几个月，总算坚持了下来。对Excel的痴迷没有停留在头脑和心中，而是体现在了具体的行动以及积极的学习和参与上来，因此，收获很大，感觉水平也有明显的提高。
现在，我计划利用点滴的业余时间，将基本的ExcelVBA操作用简短的实例进行演示，编辑成《ExcelVBA编程入门范例》，以此对ExcelVBA基础知识进行一次归纳和整理，从而理清学习ExcelVBA的线条，同时也希望能对热衷于Excel的朋友以及ExcelVBA初学者快速了解和步入ExcelVBA编程殿堂有所帮助。这是我第一次偿试对所学知识进行较大规模的整理，希望大家能多提改进意见和建议，以利于改进和提高，也有助于以后的学习和编写出更好的作品呈献给大家。
主要内容和特点
《ExcelVBA编程入门范例》主要是以一些基础而简短的VBA实例来对ExcelVBA中的常用对象及其属性和方法进行讲解，包括应用程序对象、窗口、工作簿、工作表、单元格和单元格区域、图表、数据透视表、形状、控件、菜单和工具栏、帮助助手、格式化操作、文件操作、以及常用方法和函数及技巧等方面的应用示例。这些例子都比较基础，很容易理解，因而，很容易调试并得以实现，让您通过具体的实例来熟悉ExcelVBA编程。
■ 分16章共14个专题，以具体实例来对大多数常用的ExcelVBA对象进行讲解；
■ 一般而言，每个实例都很简短，用来说明使用VBA实现Excel某一功能的操作；
■ 各章内容主要是实例，即VBA代码，配以简短的说明，有些例子可能配以必要的图片，以便于理解；
■ 您可以对这些实例进行扩充或组合，以实现您想要的功能或更复杂的操作。

前6章下载：

给Word中的表格行自动编号

excelperfect — Thu, 14 Jan 2010 14:10:47 +0800

在使用Word制表时，通常涉及到为表格中的行编号。有时，为表格行编号后，需要在表格中插入或删除行，此时又需要重新编号。其实，Word可以为表格行自动编号。
例如，有如下图所示的表格。

我们希望序号列自动编号，不受插入或删除行的影响。
首先，选中序号列中需要自动编号的单元格，如下图所示。
然后，单击“格式——项目符号和编号”，出现“项目符号和编号”对话框。单击“编号”选项卡，选择一种格式的编号，如下图所示。

当然，可以单击上图所示的对话框中的“自定义”按钮，在“自定义编号列表”中设置编号的格式，如下图所示。

单击“确定”，完成自动编号，如下图所示。

这样，当你在表格中插入或删除行时，编号会自动调整。

使用VBA列出斐波那契数

excelperfect — Mon, 11 Jan 2010 13:22:53 +0800

在数列中，除前两个数外，每个元素都是前两个元素的和，例如：

0，1，1，2，3，5，8，13，21，34，55，89，……

称之为斐波那契（Fibonacci）数列。
通用定义为：

斐波那契数列有许多用途，并且具有许多有趣的属性，例如斐波那契数列的商：

可以发现，前后元素的比例符合黄金分割法。
下面，我们编写一个程序，在Excel工作表中打印出斐波那契数以及它的商。
程序代码如下：

Sub Fibonacci()
    Dim f0 As Long, f1 As Long
    Dim strNum As String
    Dim Max As Long, temp As Long
    Dim n As Long
 
    strNum = InputBox(Prompt:="请输入Fibonacci数列的数字:")
 
    f0 = 0
    f1 = 1
 
    If IsNumeric(strNum) Then
        Max = CLng(strNum)
        Range("A1") = "数字序号"
        Range("B1") = "数字"
        Range("C1") = "商"
        Range("A2") = 0
        Range("B2") = 0
        Range("A3") = 1
        Range("B3") = 1
        For n = 2 To Max
            temp = f1
            f1 = f1 + f0
            f0 = temp
            Range("A" & n + 2) = n
            Range("B" & n + 2) = f1
            Range("C" & n + 2) = f1 / f0
        Next n
    Else
        MsgBox "您没有输入数字!程序退出!"
    End If
End Sub

其中，变量f1包含当前斐波那契数的值，f0包含前一个斐波那契数的值。
在For循环中：

将f1（即当前的斐波那契数）的值保存到一个临时变量中。
将f0和f1相加，并将结果保存到f1中，这就是新的斐波那契数。
在f0中存储临时变量的值，即f0就是前一个斐波那契数。
将结果输出到工作表中，并重复这个过程。