[原创]Excel VBA 快速上手之宝典

zhoujibin1 · 发表于 2006-7-22 21:52

第四节图表的操作
Excel让人喜爱的原因就是它有强大得计算功能和数图相关联的数据作图功能。本节就介绍下VBA代码作图方面内容。通过代码作重复的图，可以大大提高效率，比如我的作品GeoPlot（www.geoplot.net）。Excel图的代码绘制，最好是学习录制的宏来分析VBA代码的作用，最后优化而成通用程序块。

一．新建及类型
1）设置一个对象等于新增图表对象就建立好了一个新图
1.1 新图赋值给对象变量
Set mychart = Sheets(1).ChartObjects.Add (ChrLeft, ChrTop, ChrWidth, ChrHeight)
说明：增加一个嵌入图表，其左上角的位置坐标为（ChrLeft, ChrTop），长宽为ChrWidth×ChrHeight，单位为磅。1磅为1/72 英寸和0.035 厘米，字体大小通常用磅数量度。
1.2 直接增加一个图对象到图对象集Charts
Charts.Add

2）图的类型可以更改Chart对象的属性ChartType来实现
2.1）图表对象变量的使用
mychart.Chart.ChartType = xlXYScatterLines '散点图折线类型
2.2）未设置对象变量的图
它的类型更改需要在增加它后，连续操作让它处于激活状态（ActiveChart），这样可通过当前活动图ActiveChart来使用。如：ActiveChart.ChartType = xlPie ‘饼图

3）图表有两大类，一是嵌入工作表上的图，另一类是作为新工作表图，其设置如下：
3.1）嵌入工作表上的图
ActiveChart.Location Where:=xlLocationAsObject, Name:= "Sheet1" 或如1.1生成新图时就定义为生成嵌入图ChartObjects.Add
3.2）新工作表图
ActiveChart.Location Where:=xlLocationAsNewSheet 或用图表对象变量属性更改 mychart.location where:= xlLocationAsNewSheet

二．设置图表的数据
一般是使用Excel表格上数据，所以可以通过设置Chart图表对象的数据来源属性来获得作图需要的数据。如：ActiveChart.SetSource Date Source:=Sheets("Sheet1").Range("A1:B6"), PlotBy:= xlColumns
这里的PlotBy:= xlColumns表示按列绘制数据；也可以按行绘制，其参数为PlotBy:= xlRows。
另外一种数据设置是对图的横、纵坐标数据分别赋值。如：
        mychart.chart.SeriesCollection.NewSeries                         ‘增加一个系列数据
        mychart.chart.SeriesCollection(1).XValues = Array(45, 100)       ‘横坐标数据组或单元对象
        mychart.chart.SeriesCollection(1).Values = Array(50, 180)        ‘纵坐标数据组或单元对象

三．图表格式设置
对于图的格式设置，一般是录制一个宏，再删除不需要的语句，这样是开发者最省事的方法。在本节的示例中有详细的格式设置，可以更改坐标轴的名称，大小范围，刻度大小等。对不清楚的图的类型名称，格式参数，我们都可以通过录制宏来了解学习。时刻记住，录制宏来分析代码，是最好的学习方法。

四．散点图增加一个系列和增加文字标签
1）用图表的系列集合对象的方法NewSeries，就可以增加图的系列，然后再对新系列进行设置，如下：
mychart.chart.SeriesCollection.NewSeries                         ‘增加一个系列数据
mychart.chart.SeriesCollection(1).XValues = Array(45, 100)       ‘横坐标数据组或单元对象
mychart.chart.SeriesCollection(1).Values = Array(50, 180)        ‘纵坐标数据组或单元对象

2）对于散点图，我们可以增加文字标签于图上，方法如下：增加一个新的系列，不过数据就一点，不显示数据点，而仅显示它的数据标签即可。如下示例。
       With mychart.chart.SeriesCollection(2)
         .MarkerStyle = xlNone                                  '不显示点标记
         .Points(1).HasDataLabel = True                         '数据标记及显示文字标签
         .Points(1).DataLabel.Text =”标签文字”                '标签文字
         .DataLabels.Position = xlLabelPositionCenter           '位置风格，居中
       End With

zhoujibin1 · 发表于 2006-7-22 21:52

五．实例
1）生成一个散点嵌入图于工作表窗口中心，绘制一条直线（45,50）—(100,180), 在第一点显示文字标签 “Test”，此外还加一个点（80，100）。

代码和详细注释如下：
Sub DrawChart()
'*******************************图表对象的定义和生成***********************************
    '定义对象变量，以便设置它为图表（chart）对象及图位置大小变量
    Dim mychart As Object, mysheet As Object
    Dim ChrLeft As Long, ChrTop As Long, ChrWidth As Long, ChrHeight As Long

    On Error Resume Next
    Application.ScreenUpdating = False

    ChrWidth = 250: ChrHeight = 250
    ChrLeft = Abs(Windows(ThisWorkbook.Name).Width - ChrWidth) / 2
    ChrTop = Abs(Windows(ThisWorkbook.Name).Height - ChrHeight) / 2    '计算图表在窗口中心坐标
    Set mychart = Sheets(1).ChartObjects.Add(ChrLeft, ChrTop, ChrWidth, ChrHeight)

   With mychart.Chart
        .ChartType = xlXYScatterLines                                  '散点折线图类型
        .SeriesCollection.NewSeries                                    '增加一次投点,画条直线
        .SeriesCollection(1).XValues = Array(45, 100)
        .SeriesCollection(1).Values = Array(50, 180)
        .SeriesCollection(1).Points(1).HasDataLabel = True             '点1是否显示数据标签
        .SeriesCollection(1).Points(1).DataLabel.Text = "Test"         '点1的标签文字
        .SeriesCollection.NewSeries                                    '增加一次投点,就投个点（80，100）
        .SeriesCollection(2).XValues = 80
        .SeriesCollection(2).Values = 100
    End With
'*******************************图表风格定义***********************************
    With mychart.Chart
        .ChartArea.Font.Size = 10                                       '图表字符的大小
        .HasLegend = False                                              '图是否显示图例
        .Axes(xlCategory, xlPrimary).HasTitle = True                    'X轴是否有坐标名
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y" 'Y轴坐标名"Y"
        .PlotArea.Interior.ColorIndex = xlNone                          '定义投图区为透明
    End With

With mychart.Chart.Axes(xlCategory)
        .MinimumScale = 0                                         '定义X坐标轴最小值
        .MaximumScale = 200                                       '定义X坐标轴最大值
        .MinorUnit = 10                                           '定义次刻度线单位
        .MajorUnit = 50                                           '定义主刻度线单位
        .CrossesAt = 0                                            'X轴交汇点设为0
        .MajorTickMark = xlInside                                 '主刻度线方向朝内
        .MinorTickMark = xlInside                                 '次刻度线方向朝内
        .HasMajorGridlines = False                                '是否显示主网格线
        .HasMinorGridlines = False                                '是否显示次网格线
   End With

   With mychart.Chart.Axes(xlValue)
        .MinimumScale = 0                                         '定义y坐标轴最小值
        .MaximumScale = 200                                       '定义y坐标轴最大值
        .MinorUnit = 10                                           '定义次刻度线单位
        .MajorUnit = 50                                           '定义主刻度线单位
        .CrossesAt = 0                                            'Y轴交汇点设为0
        .MajorTickMark = xlInside                                 '主刻度线方向朝内
        .MinorTickMark = xlInside                                 '次刻度线方向朝内
        .HasMajorGridlines = False                                '是否显示主网格线
        .HasMinorGridlines = False                                '是否显示次网格线
   End With

    Set mychart = Nothing                                         '删除图表对象变量
    Application.ScreenUpdating = True                             '恢复屏幕刷新
End Sub

2）根据工作表一的A1：B10数据生成柱状图嵌入表一，位置为数据右下角位置。

Sub Pic2()
    On Error Resume Next
    Application.ScreenUpdating = False
    Charts.Add

    With ActiveChart                                                           '使用With...End with块，减少对象引用次数，提高效率
        .ChartType = xlColumnClustered
        .SetSourceData Source:=Sheets("sheet1").Range("A1:B10"), PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
    End With

    With Sheets("sheet1")
        .Shapes(.Shapes.Count).Left = .Cells(11, 3).Left     '定位于单元格(10,2)右下角
        .Shapes(.Shapes.Count).Top = .Cells(11, 3).Top     '图表建成后，其位置由其形状（Shape）对象的位置确定
    End With
    Application.ScreenUpdating = True
End Sub

[此贴子已经被作者于2006-7-23 8:54:46编辑过]

zhoujibin1 · 发表于 2006-7-22 21:54

请见绘图示例，仔细看看，相信这两个例子解决了大家很想知道的一些东西。

还是建议大家通过录制宏，修改宏代码，学习图表设置的参数。

dBmSFKV8.rar (23.38 KB, 下载次数: 13228)

[此贴子已经被作者于2006-7-23 8:56:03编辑过]

zhoujibin1 · 发表于 2006-7-22 22:03

QUOTE:

以下是引用zhuxiangji在2006-7-22 21:15:03的发言：

致楼主：

太感谢你无私的写出这些给我们共享，这也是我看到的最容易看懂的有关VBA的系统的文章。向您辛勤的劳动表示最崇高的敬意！

但同时也要向你提一个小小的建议：能否多一些示例。从我是VBA初学者的角度来看，虽然楼主写的相当通俗易懂，但缺乏相应的示例。我们即使看懂了，却不知道怎么使用或用在什么地方。这个建议希望楼主考虑考虑，我们希望楼主在完成该书后，该书能够成为VBA初学者的最经典、最实用的入门书籍之一。让更多的VBA爱好者受益。

[em17][em23][em24]

谢谢您的关注。例子少了的原因是我较忙，没时间准备。但大家记住一句话，解决实际问题是学习进步的关键。所以建议大家要多动手写代码，亲身体验。我只是告诉了大家Excel VBA这个东西及其用法功能，如何用那要自己锻炼。我相信大家能很容易地理解我写地内容，因为我也写的够口水话了。如果完成了后，我会加上更多示例，重新排版，加上图表，希望能成为一本对学习VBA有用的书。因为我看了一些书，感觉写的不清楚，要不清楚了就太罗嗦。一些关键的核心东西，很多书上都没说明白，比如ADO增加记录不能重复关键词内容，VBA作图的位置定位、大小设置等。

[此贴子已经被作者于2006-7-22 22:05:59编辑过]

zhoujibin1 · 发表于 2006-7-22 23:05

给大家提几个问题来解决，不然光看是学不到实际东西的，要动手。

问题1：26个英文字母大小写，任意四个组合，把组合结果存放在Excel 表1的A列。

问题2：同1题，把结果写入一个文本文件中。

问题3：使用文件打开对话框，打开上一个文本文件，读入数据到Excel表。

问题4：把上一Excel表的组合数据存到一Access数据库中，自己建立库文件。

问题5：对26个小写字母，赋予随机的坐标（x，y），并把26个（x,y）绘制在一张Excel 散点图上，每点旁边都有对应的字母。

希望能看到大家的结果，请上传。

zhoujibin1 · 发表于 2006-7-23 10:05

第三章 Excel VBA高级使用

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

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

第一节 Win API的使用

烟雨任平生 · 发表于 2006-7-23 11:14

好东西啊

最想看的是 VBA 的高级使用啊

等待续级！

xbbao · 发表于 2006-7-23 11:27

最希望看到的资料就要出现了，等待中.......

着实佩服楼主的无私精神，辛苦了

zhoujibin1 · 发表于 2006-7-23 11:56

我都不好意思啦，大家都这么鼓励我。

我也没有搞的那么深刻，对API我也只会需要时去查下函数，调用调用，对每个函数需要自己亲身体验才深刻。如果要对API搞精通，那是需要花很多时间，估计要3个月吧(还是对于有些基础的)。

zhoujibin1 · 发表于 2006-7-23 12:02

第一节 Win API的使用
Windows API 是英文Application Programming Interface的缩写，Win32 API也就是微软Windows 32位操作系统的应用程序编程接口。我们可以认为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 "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

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

二、使用API函数或过程
以API函数Beep来说明API函数的几种使用方法，Beep函数的介绍如下：

【VBA声明】
Public Declare Function Beep Lib "kernel32" Alias "Beep" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
【说明】
用于生成简单的声音
【返回值】
Long，非零表示成功，否则返回零。
【参数表】
dwFreq --------- Long，声音频率（从37Hz到32767Hz）。
dwDuration ----- Long，声音的持续时间，以毫秒为单位。如为-1，表示一直播放声音，直到再次调用该函数为止。

可采用以下几种方式使用API函数或过程，以Beep为例：
（1）忽略函数返回值的调用：
　　　　Beep 1000, 5000
　　注意此时函数的参数是不加括号的。
（2）Call方法调用：
　　　　Call Beep(1000, 5000)
　　注意这里需要加上括号，但我们不取回函数的返回值。
（3）取得函数返回值的调用：
MyLng = Beep(1000, 5000)
　　此时需要加上括号，而且我们必须事先定义一个变量（变量的类型与函数返回值类型相同）来存储API函数的返回值。

三、声明的一些说明
（1）声明中的Lib 和 Alias 是怎么回事
   一般情况下Win32 API函数总是包含在Windows系统自带的或是其它公司提供的动态连接库DLL中，而Declare语句中的关键字Lib就是用来指定DLL（动态连接库）文件路径是系统库路径的，这样VBA才能找到这个DLL文件，然后才能使用其中的API函数。
   如果我们只是列出DLL文件名而不指出其完整路径的话，VBA会自动到 Excel文件所在目录、当前工作目录、Windows\System目录、Windows目录下搜寻这个DLL文件。所以如果所要使用的DLL文件不在上述几个目录下的话，我们应该指明其完整路径。
   Alias用于指定API函数的别名，如果我们调用的API函数要使用字符串（参数中包含String型）的话，Alias关键字是必须的。这是因为在ANSI和Unicode字符集中同一API函数的名称可能不一样，为了保证不出现声明错误，所以我们使用Alias关键字指出API函数的别名。　　

（2）常见API参数类型的说明
API函数的参数中最常见的是长整型数据（Long）类型，例如API中的句柄、一些特定的常量、函数的返回值都是此类型的值；另外几种常见的参数类型有：整型Integer、Byte型、String型等。

（3）声明中的ByVal是作什么用的
这跟VBA的参数传递方式有关，在默认情况下VBA是通过传值方式传递函数的参数、而有些API函数要求必须采用地址传递方式（ByRef）来传递函数参数（这两种参数传递方式是不同的，前者传递的是参数真实的值，而后者要求是一个地址指针）。声明中的 ByVal 表明参数是传递一个值。

（4）怎样轻松得到完整API函数声明
Visual Basic 6.0 自带API文本查看器API Text Viewer，我们可以使用它来找到API函数的完整声明，然后把它粘贴到程序就可使用。如果未安装VB6，大家可以到网上下载，此外网络上还有很多API函数的介绍，大家也可以下载来学习。
大家使用API有必要对它进行有一定了解，然后再去使用API文本查看器。虽然不必刻意研究每个API函数（如果真的知道100来个API函数的使用，相信绝对有用），但是需要我们了解一下该函数的作用。而对API函数功能的介绍，网络也有现成的软件供大家下载使用。

[此贴子已经被作者于2006-7-23 16:43:16编辑过]

		自动登录	找回密码
密码			免费注册