基于VBA的Excel测量程序开发技术.pdf

基于VBA的Excel测量程序开发技术.pdf

《基于VBA的Excel测量程序开发技术.pdf》由会员分享，可在线阅读，更多相关《基于VBA的Excel测量程序开发技术.pdf（4页珍藏版）》请在冰点文库上搜索。

1994-2010ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http:

/文章编号:

049420911（2005）0620037204中图分类号:

P209文献标识码:

B基于VBA的Excel测量程序开发技术周卫1,2（1.南京大学城市与资源学系,江苏南京210093;2.南京师范大学地理科学学院,江苏南京210097）TheDevelopmentofSurveyingProgramswithExcelBasedonVBAZHOUWei摘要:

基于VBA开发Excel测量程序具有简便、高效和通用的特点,程序的使用具有直观、实时、动态、灵活、交互性与图形功能强等明显的优势。

由于Office软件使用的普遍性及其组件之间数据良好的交互性和共享性,Excel在数据处理与相应报告的生成等方面更具优势。

以开发实例介绍使用VBA开发Excel测量程序的主要技术方法。

关键词:

Excel;VBA;测量;程序;函数收稿日期:

2005201227作者简介:

周卫（19622）,男,江苏南京人,教授,主要研究方向为测量工程与地理信息系统。

一、引言基于VBA开发Excel测量程序具有简便、高效和通用的特点,程序的使用具有直观、透明、实时、动态、灵活、交互性与图表功能强等明显的优势。

由于Office软件使用的普遍性及其组件之间数据良好的交互性和共享性,Excel在测量数据处理与相应报告的生成等方面更具优势。

如果采用其他系统进行开发,要实现上述功能特点和优势,需要耗时费力进行复杂的开发,一些功能的实现甚至是十分困难的。

目前,基于VBA开发Excel测量程序的相关文献尚不多见,本文以典型的测量数据处理实例,介绍使用VBA开发Excel测量程序的主要技术与方法。

二、程序开发的主要技术VBA的基本架构包括对象、属性、方法、事件等,在架构以及编程方法、对象的使用等方面与Vi2sualBasic（VB）完全相同,VB的代码略做修改或不做修改即可移植成为Excel的VBA代码。

只是它无法像VB程序可以独立存在。

在Excel中,保存文件,即保存了所有文件模块。

下面着重介绍ExcelVBA的编程技术,与VB相同的部分可能会提及,但不再赘述,有关内容可参阅VB等相关书籍。

1.VBE的使用Alt+F11可以实现VBE与Excel的worksheet之间的切换,在VBE中应注意调试工具栏的使用,以提高调试的速度和效率。

代码修改后,返回Excel表,按F9重算。

为了在编写代码的时候,显示VBA函数列表,可以键入VBA,后加点,VBE会显示所有函数的列表。

对于对象、函数、属性或方法,可将鼠标移至相应的单词上,按F1查询。

开发的代码可以通过工程属性加密,密码的输入应在“中断”状态下输入。

2.宏的利用一些代码可以通过录制宏（macro2recorder）并修改编辑的方式转换获取,录制一个操作步骤,宏的程序区就会加入一行语句。

宏记录器不能记录函数过程,记录的宏都是Sub过程,但宏记录器能帮助用户识别各种可以用于自定义函数的属性。

然而,采用宏录制的代码不够简捷,不能实现条件判断等较复杂的功能等。

3.Excel函数的使用Excel是数值数据处理应用软件,提供了约300余个函数。

VB编程可以使用的函数大约有200个,而不是全部,例如,VBA不能访问Excel的SQRT工作表函数,因为这个函数VBA有自己的版本:

Sqr。

使用工作表函数,即Application集合对象,在编程时需要在函数前加“application.”或“worksheetfunc2tion.”,但后者不能与Excel97之前的版本兼容。

如,函数Index（）的VBA表达应为application.index（）。

4.单元格的控制与引用编程处理数据需要进行数据的读写,在Excel中,数据的读写涉及单元格的控制,而单元格的控制是VBA开发的最重要和较复杂的技术。

其中,几个对象和方法十分重要,如Range,Cells,Index,Offset等,这几个函数的使用方法介绍如下。

Range用来控制单元格范围,其自变量是列编732005年第6期测绘通报1994-2010ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http:

/号与行编号,Range不可以输入数值自变量;Cells用来控制单元格,其自变量是行数和列数,因此,Cells可以输入数值自变量。

Index返回表格或区域中的数值或对数值的引用。

函数INDEX（）有两种形式:

数组和引用。

数组形式通常返回数值或数值数组;引用形式通常返回引用。

这里仅简介引用形式。

INDEX（reference,row-num,column-num,area-num）返回引用中指定单元格或单元格区域的引用。

Row-num引用中某行的行序号,函数从该行返回一个引用;Column-num引用中某列的列序号,函数从该列返回一个引用;Area-num选择引用中的一个区域,并返回该区域中row-num和column-num的交叉区域。

选中或输入的第1个区域序号为1,第2个为2,以此类推。

如果省略area-num,函数INDEX使用区域1。

如果将row-num或column-num设置为0,函数INDEX分别返回对整个列或行的引用。

OFFSET（reference,rows,cols,height,width）,其中,Reference作为偏移量参照系的引用区域。

Rows相对于偏移量参照系的左上角单元格上（下）偏移的行数。

如果使用5作为参数Rows,则说明目标引用区域的左上角单元格比reference低5行。

行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。

Cols相对于偏移量参照系的左上角单元格,左（右）偏移的列数。

如果使用5作为参数Cols,则说明目标引用区域的左上角的单元格比reference靠右5列。

列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

Height,Width分别表示所要返回的引用区域的行数和列数,均应为正数。

如果省略这两个参数,则假设其高度或宽度与reference相同。

例如,SUM（OFFSET（C2,1,2,3,1）将计算比单元格C2靠下1行并靠右2列的3行1列的区域的和。

5.其他重要的函数与语言结构函数array（）可用于多于一个数值（数组）的输出,即返回按数组排列的多个值。

按Ctrl+Shift+Enter键,可以把数组公式输入到一个单元中。

这里单元的含义是与数组维数相同的单元个数同时被选中的一组单元格,以便输出数组数据。

但也可以选中一个或几个单元格（少于数组维数）,仅仅输出数组的前几个数值。

可以将该单元组继续拉选,直到输出全部数组数据,但数组单元一旦拉出就不可缩退。

例如,表2中可只输出Xp和Yp这2项,后3项根据需要输出。

一般地,在水平行中返回一个数组,需要在垂直行返回数组使用Transpose。

另外,一些有用的范围属性,诸如Formula,Ad2dress,Count,Parent,Name,NumberFormat,font,Colums和Rows等在引用编程时常用。

Foreach2Next结构,对于数据范围内每个单元的操作是十分有用的。

上述函数等对于程序开发十分重要,下面“程序开发与使用”中主要以函数的开发为例,其实掌握了上述函数的使用,基本可以开发出较复杂的测量数据处理程序,实现数据处理与输出的自动化。

三、程序开发与使用根据上述函数的语法等,结合后方交会和方位角推算的实例,介绍采用VBA开发Excel测量函数的方法,这些函数又称为用户自定义函数（User-DefinedFunction,UDF）。

应该注意的是,函数是被动的（Passive）,只能返回一个（或一组）值,不能对对象执行任何其他操作。

在函数开发方面可大致分为两类,后方交会等属于公式类,而方位角推算属于推算类,这两类函数的开发具有不同的特点,公式类的输入与输出比较固定,而推算类则不然,需要根据输入的数据并读取相关的数据进行“相对”的推算。

1.后方交会在Excel中输入后方交会计算的必要数据如表1。

表1后方交会计算数据XAYAXBYBXCYCAFBTGM840.134844.4221001.5421620.616659.1911282.629291.5724351.354676.2654374.876564.144512.975541.715144.966083.07106.1422118.5818134.47283测绘通报2005年第6期1994-2010ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http:

/在任意组合格（组合格特指选中的一个及一个以上的单元范围）中可以获得如下计算结果,见表2。

表2后方交会计算成果XpYpPaPbPc503.7011500.0770.53319010.1208785-1.5406824657.7366074.2880.89201940.83308050.7654039后方交会编程采用重心公式,代码及其解释如下:

后方交会-HFJH输入:

XA,YA,XB,YB,XC,YC,AF,BT,GM;输出:

X,Y（Pa,Pb,Pc）选取两个以上单元（任意位置）以便显示输出Xp,Yp（Pa,Pb,Pc）鼠标选取第一行往下拉继续计算后续其他行。

FunctionHFJH（SJ）AsVariantApplication.VolatileTrueXA=Application.Index（SJ,1）XA是选择数据行的第1个数字,依次类推。

YA=Application.Index（SJ,2）XB=Application.Index（SJ,3）YB=Application.Index（SJ,4）XC=Application.Index（SJ,5）YC=Application.Index（SJ,6）AF=Application.Index（SJ,7）BT=Application.Index（SJ,8）GM=Application.Index（SJ,9）使用TS（）函数反算方位角,为避免使用TAB,2代替B,则TA2表示AB方向的方位角TA2=TS（XA,YA,XB,YB）TAC=TS（XA,YA,XC,YC）TBA=TS（XB,YB,XA,YA）TBC=TS（XB,YB,XC,YC）TCA=TS（XC,YC,XA,YA）TCB=TS（XC,YC,XB,YB）求算三角形内角a=TAC-TA2:

b=TBA-TBC:

c=TCB-TCAIfa0ThenLeta=360+aIfb0ThenLetb=360+bIfc0Andtt=180Andtt=540Andtt“宏”“宏”命令（或者按Alt+F8）,在宏名框中键入创建的函数名。

此时,“选项”应激活,否则,函数名输错,更正后单击“选项”,在说明中键入对该函数使用的说明。

再单击“确定”、取消。

在“插入函数”的“用户定义”中可见该函数。

例如上述后方交会函数,可以在任意组合格手输“=HFJH（sj）”,再选择SJ对应的数据范围,或加载宏以后插入函数“HFJH”,选择数据时可以回车。

数组成果输出时,需要操作Shift+Ctrl,Enter。

函数过程与公式如果不在相同工作簿中,需要采用3种方式中的一种引用,即在函数名前使用一个文件引用;对工作簿设置引用;创建一个加载宏,使用时加载。

一般采用此种方式。

如果“宏”源改变,需要通过“编辑”“链接”更改“宏”源。

四、结束语本文以较简单的实例介绍了面向测量工程,利用VBA开发Excel程序的技术方法。

其实,这一技术可开发出较复杂和规模较大的测量应用程序,覆盖测量常用的数据处理功能,具有实时、可视化等特点,在测量数据的处理方面表现出了其他测量程序难以比拟的优越性。

笔者利用VBA开发出的数十个Excel的测量函数和一些测量数据的处理程序,在工程应用上发挥了很好的作用,尤其在复杂的大型工程测量的数据处理等方面,充分体现了现代工程测量的实时、动态和可视化等要求,为诸如放样方案的比选等创造了良好的条件。

参考文献:

1WALKENBACHJ.Excel2003公式与函数应用宝典（Ex2cel2003formulas,WileyPublishing,Inc）M.北京:

电子工业出版社,2004.2VK工作室.Excel2000VBA入门M.北京:

科学出版社,2001.3顾孝烈,等.测量学（第2版）M.上海:

同济大学出版社,1999.精彩升温,好奖连连惠普大幅面打印机俱乐部一期注册抽奖结果公布,二期活动正在开展中本刊讯继2005年3月惠普大幅面打印机俱乐部成功举办“HP有您更精彩答谢大幅面打印机老用户注册抽奖活动”以来,广大行业用户给予了此次活动热切的关注和积极的参与。

截止到4月底,已有上千名用户成为惠普大幅面打印机俱乐部的正式成员。

上期抽奖活动的结果已于近期公布,10名幸运的注册会员赢得了iPod数码大奖。

（获奖名单详见惠普网站http:

/Photosmart7458幸运大奖。

本次注册抽奖活动的中奖名额由上期的10个增加到20个,中奖机会更多,奖项更精彩!

更多活动详情可登陆http:

/答谢大幅面打印机老用户注册大抽奖”活动旨在加强与老用户的互动,进而为他们提供更贴心的产品和服务。

除了注册抽奖外,俱乐部还将推出更多的会员活动和促销优惠。

赢取客户的信任和支持,是惠普永远精彩的理由!

04测绘通报2005年第6期