VBA电子书

Abs函数返回参数的绝对值，其类型和参数相同。

Array函数返回一个包含数组的Variant。

Asc函数返回一个Integer，代表字符串中首字母的字符代码。

Atn函数返回一个Double，指定一个数的反正切值。

CallByName函数执行一个对象的方法，或者设置或返回一个对象的属性。

Choose函数从参数列表中选择并返回一个值。

Chr函数返回String，其中包含有与指定的字符代码相关的字符。

Cos函数返回一个Double，指定一个角的余弦值。

CreateObject函数创建并返回一个对ActiveX对象的引用。

CurDir函数返回一个Variant(String)，用来代表当前的路径。

CVErr函数返回Error子类型的Variant，其中包含指定的错误号。

Date函数返回包含系统日期的Variant(Date)。

DateAdd函数返回包含一个日期的Variant(Date)，这一日期还加上了一段时间间隔。

DateDiff函数返回Variant(Long)的值，表示两个指定日期间的时间间隔数目。

DatePart函数返回一个包含已知日期的指定时间部分的Variant(Integer)。

DateSerial函数返回包含指定的年、月、日的Variant(Date)。

DateValue函数返回一个Variant(Date)。

Day函数返回一个Variant(Integer)，其值为1到31之间的整数，表示一个月中的某一日。

DDB函数返回一个Double，指定一笔资产在一特定期间内的折旧。

可使用双下落收复平衡方法或其它指定的方法进行计算。

Dir函数返回一个String，用以表示一个文件名、目录名或文件夹名称，它必须与指定的模式或文件属性、或磁盘卷标相匹配。

DoEvents函数转让控制权，以便让操作系统处理其它的事件。

Environ函数返回String，它关连于一个操作系统环境变量。

在Macintosh中不可用EOF函数返回一个Integer，它包含Boolean值True，表明已经到达为Random或顺序Input 打开的文件的结尾。

目录第一讲VBA与宏及VBE窗口的介绍 (2)第二讲录制宏修改宏保存宏指定宏自定义按钮加载宏 (3)第三讲VBA的语法基础 (7)第四讲Range对象 (8)第五讲VBA语句(一) (12)第六讲VBA语句(二) (15)第七讲错误处理和代码调试 (17)第八讲工作表和工作薄对象 (19)第九讲目录的制作 (21)第十讲工作表和工作薄事件OnTime和OnKey事件 (24)第十一讲自定义函数 (27)第十二讲添加快捷菜单 (30)第十三讲添加菜单 (32)第十四讲窗体与控件和音乐播放器的制作 (34)第十五讲怎样才能不断的提高VBA水平 (38)附录一最常用的语句 (39)附录二最常用关键字 (44)前言Excel作为使用最广泛的电子表格软件，它以操作简便，功能丰富和效率高而著称，非常适合对计算机熟悉程度不高的人员使用。

但是随着学习的深入，工作上的需求，提高工作效率，用户期望Excel提供更加自动化的操作，这就需要借助Excel VB A。

VBA的优点：1.解决重复的操作。

（大大提高了工作效率）2.在Excel的现有基础上再增添一些更适合自己的新功能3.可以自定义函数（来简化冗长的公式和计算,方便初,中级用户）4.设置个性化的操作界面（更适合自己工作的菜单，快捷菜单）5.二次开发本教材包括的内容，前面第一讲到第七讲是VBA基础内容；第八讲到第十五讲是提高篇和实例讲解篇附：书中代码前面#表示行,如4#表示第4行。

EXCEL学习网站:佛山小老鼠编2010年12月第 1 页共45 页第一讲VBA与宏及VBE窗口的介绍一．VBA的定义：内置在Excel里一种最简单的编程语言。

打个比方我们去英国，那我们要会英文，才能和当地的人进行沟通，我们要用和Excel进行人机对话，那我们要会VBA，也就是说VBA是Excel能识别的编程语言，也是编程员能识别的一种最简单的编程语言。

二．什么是宏：宏是一连串动作的集合，动作是由VBA代码组成的，打个比方说，我们家里以前的洗衣机是有加水，加洗衣粉，转动，放水，甩干，现在我们把其它打包成一个命令按钮，这样我们只要按一下，衣服就洗好了，以前要用按五下，这样就大提高了工作效率，实现办公自动化。

第06章编写SUB过程及开发函数VBA的主体结构就是过程。

VBA包括子过程、函数过程和属性过程三种，本书主要介绍子过程（也称Sub过程）和函数过程（也称Function过程）。

本章要点◆认识过程◆Sub过程◆Function过程◆关于过程参数◆开发自定义函数◆编写函数帮助6.1认识过程VBA中每一个程序都包含过程。

录制的宏是一个过程，一个自定义函数也是一个过程。

掌握好单个过程的编写思路，就可以组合成一个大中型插件或者专业程序。

6.1.1 过程的分类与调用方式过程主要分为三类：子过程、函数过程和属性过程。

这三类过程的格式如下。

Sub 子过程()……End SubFunction 函数过程(rng As Range)……End FunctionProperty Get 属性过程() As Variant……End Property本书主要讲述Sub子过程和Function函数过程的开发。

Sub过程是VBA中应用最广的过程，录制宏所产生的过程就是Sub过程。

Sub过程的执行方式包括五种。

1.【Alt+F8】执行如果在工作表命令窗口、ThisWorkBook命令窗口或者标准模块窗口中存在Sub过程，那么在工作表界面可以通过快捷键【Alt+F8】来执行该过程。

假设在VBE界面中的Sheet1代码窗口中有一个Sub过程“汇总”，在模块1中有一个名为“新建菜单”的Sub过程，那么通过快捷键【Alt+F8】打开“宏”对话框后，将在对话框中产生两个可执行程序名，其中工作表命令窗口的Sub过程会连同工作表名一起出现在宏名列表中，而模块中的过程则仅仅列出过程名。

用户选择目标程序并单击【执行】按钮即可启动Sub子过程。

2.快捷键执行Sub过程可以与某个快捷键进行关联，在后续的使用中就可以利用这个快捷键来调用对应的过程。

设置Sub过程的快捷键主要有两种方式：利用宏对话框设置及用VBA代码指定。

后者在本书其他章节将会讲述，在此演示一下“宏”对话框设置宏的快捷键的方法：假设VB工程中有两个名为“汇总”和“新建菜单”的Sub过程，在工作表界面中按下快捷键【Alt+F8】调出图6.1所示对话框，然后选择“新建菜单”，并单击“选项”按钮，在弹出的“宏选项”对话框中指定快捷键，如图6.2所示。

VBA教程PDF介绍VBA（Visual Basic for Applications）是一种在Microsoft Office应用程序中编程的语言。

通过使用VBA，您可以自动化执行各种任务，如创建自定义函数、编写宏、操作数据等。

本教程将介绍VBA的基本概念、语法和用法，并帮助您开始编写自己的VBA代码。

目录1.VBA简介2.VBA开发环境3.VBA语法– 3.1 变量和数据类型– 3.2 控制流程– 3.3 过程和函数– 3.4 数组和集合– 3.5 常用对象和方法4.VBA实例5.VBA调试和错误处理6.VBA与其他编程语言的比较7.VBA资源和进阶学习8.参考文献VBA简介VBA是基于Visual Basic语言的一种宏编程语言。

它由微软推出，专门用于自动化执行任务和处理数据的需求。

VBA 可以与Microsoft Office套件中的各种应用程序进行集成，包括Excel、Word、PowerPoint等。

通过编写VBA代码，您可以自定义Office应用程序的功能，提高工作效率。

VBA开发环境在开始学习VBA之前，您需要一个VBA开发环境。

通常情况下，您可以使用Microsoft Office套件中的任何应用程序，如Excel或Word作为VBA开发环境。

打开这些应用程序后，您可以按下Alt+F11键打开VBA编辑器。

VBA编辑器是编写、编辑和运行VBA代码的地方。

VBA语法VBA语法与Visual Basic语言非常相似，但是有一些特定于VBA的语法。

在本节中，我们将介绍VBA的基本语法元素，包括变量和数据类型、控制流程、过程和函数、数组和集合、常用对象和方法等。

变量和数据类型在VBA中，您可以使用变量来存储和操作值。

变量需要指定一个特定的数据类型，以确定变量可以存储的值的类型和范围。

VBA支持多种数据类型，包括整数、浮点数、字符串、布尔值等。

```vba Dim i As Integer i = 10Dim d As Double d = 3.14Dim s As String s =。

第1学时Visual Basic forApplication是什么欢迎使用E x c e l和Visual Basic for Application开发应用程序！作为已经能够熟练使用E x c e l 的用户，你一定对E x c e l强大的功能感到满意。

现在，你将逐步提高到更高的水平。

即使你以前从来没有编写过程序，也能够使用Visual Basic for Application开发出解决方案。

Vi s u a l Basic for Application是现在可用的最容易学习、最容易使用同时也是最复杂的应用程序自动化语言（过去常常称为宏语言）之一。

在这个学时中，通过熟悉宏录制器，将开始学习有关的基础知识。

本学时的重点包括：• Visual Basic for Application是什么• Excel环境中基于应用程序的自动化的优点• 录制一个简单的宏• 执行宏• 编辑宏• 宏录制器的局限性1.1 Visual Basic for Application是什么直到9 0年代早期，使应用程序自动化还是充满挑战性的领域。

对每个需要自动化的应用程序，人们不得不学习一种不同的自动化语言。

例如，可以使用E x c e l的宏语言使E x c e l自动化，使用Word Basic使Microsoft Wo r d自动化，等等。

M i c r o s o f t决定让它开发出来的应用程序共享一种通用的自动化语言—Visual Basic for Application（V B A），而不是使用不同的自动化语言。

可以认为Visual Basic for Application是非常流行的应用程序开发语言—Visual Basic的子集。

实际上，V B A是“寄生于”Visual Basic应用程序的版本。

V B A与Visual Basic的区别包括如下几个方面：• Visual Basic是设计用于创建标准的应用程序，而V B A是用于使已有的应用程序自动化。

第2 章VBA 语法基础和其他编程语言类似，VBA 程序代码也是有基础语句组成的。

在本章中，将结合具体的例子来介绍VBA 语法的基础内容。

主要包括输入、输出语句、分支结构和循环结构等。

在讲解各例子的时候，将详细分析例子的应用范围。

2.1 输入和输出语句在本小节中，将详细讲解最基础的语法结构：顺序结构。

顺序结构不需要使用特殊的控制语句，编辑工具按照用户编写的程序语句依次编译，依次执行。

本小节中，将详细讲解最基础的顺序语句：输入和输出语句。

案例 8 输出数据表1．功能说明在Excel VBA 中，用户可以使用Print 方法输入各种类型的数据。

同时，用户可以在程序代码中设置不同的输出，来检测程序代码。

2．语法说明在Excel VBA 中，Print 方法组要应用于Debug 对象，其语法格式如下：Debug.Print [outputlist]参数outputlist 表示需要打印的表达式或列表。

如果省略参数，则打印空白行。

Print 方法先计算表达式的数值，然后输出结果。

在outputlist 参数中还可以使用分隔符，格式化输出的结果，分隔符有以下几种：•Spc(n)：插入n 个空格到输出数据之间；•Tab(n)：移动光标到适当位置，n 为移动的列数；•分号：表示前后两个数据项连在一起输出；•逗号：以14 个字符为一个输出区，每个数据输出到对应的输出区。

3．案例说明本例使用Print 方法向VBE 的立即窗口中输出数据表。

4．编写代码输出数据表的代码如下：Sub GetNumbers()Dim i As Integer131314145．运行结果按功能键“F5”运行子过程，在“立即窗口”输出数据计算表，如图 2.1 所示。

图 2.1 输出结果6．程序分析在上面的程序代码中，利用循环结构依次输出不同情况的数字计算结果。

关于循环结 构的知识，将在后面章节中详细讲解。

案例 9 输入用户名1．功能说明在 Excel VBA 开发过程中，经常需要和用户进行交互。

目录第一章引言 (1)1.1 程序设计语言 (1)1.2 面向对象的基本概念 (2)1.3 Visual Basic的语言特点 (4)1.4 Visual Basic的版本简介 (6)1.5 Visual Basic的安装、启动与退出 (6)1.5.1 Visual Basic的安装 (6)1.5.2 Visual Basic的启动 (6)1.5.3 Visual Basic的退出 (7)1.6 Visual Basic的集成开发环境 (7)1.6.1 主窗口 (8)1.6.2 工具箱窗口 (11)1.6.3 工程资源管理器窗口 (12)1.6.4 对象窗口 (13)1.6.5 属性窗口 (16)1.6.6 窗体布局窗口 (17)1.6.7 代码窗口 (17)习题一 (19)第二章 Visual Basic程序设计入门 (20)2.1 开发应用程序的一般步骤 (20)2.1.1 用Visual Basic开发应用程序的一般步骤 (20)2.1.2 Visual Basic应用程序的三种工作状态 (25)2.2应用程序的语法规则及常用语句 (26)2.2.1 Visual Basic中的语法规则 (26)2.2.2 Visual Basic中的几个常用语句 (27)2.3 窗体及其属性、方法和事件 (29)2.3.1 窗体的结构 (30)2.3.2 窗体的常用属性 (30)2.3.3 窗体的常用方法 (37)2.3.4 窗体的常用事件 (40)2.4 控件及其属性、方法和事件 (41)2.4.1 Visual Basic的控件种类 (42)2.4.2 控件的命名和控件值 (42)2.4.3 标签控件 (43)2.4.4 文本框控件 (43)2.4.5 命令按钮控件 (47)习题二 (50)第三章 Visual Basic 数据类型及其运算 (54)3.1基本数据类型 (54)I3.1.1 数值型 (54)3.1.2 字符串型（String） (55)3.1.3 布尔型（Boolean） (55)3.1.4 日期时间型（Date） (56)3.1.5 对象型（Object） (56)3.1.6 变体型（V ariant） (56)3.2 常量 (56)3.2.1 文字常量 (56)3.2.2 符号常量 (58)3.3 变量 (59)3.3.1 命名规则 (59)3.3.2变量的种类 (60)3.3.2 定义变量 (60)3.3.3 使用变量 (63)3.4 常用内部函数 (65)3.4.1显式数据类型转换函数 (66)3.4.2 数学函数 (66)3.4.3字符串型和数值型转换函数 (67)3.4.4日期时间函数 (67)3.4.5 字符串处理函数 (68)3.4.6 格式输出函数 (69)3.4.6 InputBox函数 (70)3.4.7 MsgBox函数 (72)3.5表达式 (74)3.5.1 算术表达式 (74)3.5.2 字符串表达式 (76)3.5.3关系表达式 (76)3.5.4逻辑表达式 (77)3.5.5 各种运算符优先级比较 (79)习题三 (81)第四章 Visual Basic控制结构 (88)4.1 算法的传统流程图及N-S结构化流程图表示 (88)4.2 选择结构程序设计概述 (90)4.3 用条件语句实现选择结构 (91)4.3.1 行If语句 (91)4.3.2 嵌套的行If语句 (92)4.3.3 块If语句 (94)4.4 用情况语句实现多分支选择结构 (97)4.5 选择结构程序举例 (102)4.6 循环结构程序设计概述 (109)II4.7 For-Next循环结构 (109)4.7.1 For-Next循环的格式 (109)4.7.2 For-Next循环的执行过程 (110)4.7.3 Exit For子句 (113)4.7.4 For-Next循环举例 (114)4.8 While-Wend循环结构 (116)4.9 Do-Loop循环结构 (120)4.9.1 Do-Loop循环的五种格式 (120)4.9.2 最简单的Do-Loop循环结构 (121)4.9.3 用Exit Do子句终止Do-Loop循环 (121)4.9.4 带While条件的Do-Loop循环结构 (122)4.9.5 带Until条件的 Do-Loop循环结构 (124)4.10嵌套的循环结构 (126)4.11 循环结构程序举例 (128)习题四 (136)第五章过程 (147)5.1 Sub过程 (147)5.1.1定义Sub过程 (147)5.1.2 调用Sub过程 (149)5.1.3 通用过程和事件过程 (150)5.2 Function过程 (152)5.2.1 建立Function过程 (152)5.2.2 调用Function过程 (153)5.3 参数的传递 (154)习题五 (158)第六章数组 (166)6.1 数组的概念 (166)6.2 数组的定义 (167)6.2.1 常规数组的定义 (168)6.2.2 动态数组的定义 (169)6.3 数组元素的使用 (170)6.3.1 数组元素的引用、赋值、输出和复制 (170)6.3.2数组函数 (172)6.3.3 数组参数 (174)6.4数组的清除 (176)6.5 数组元素的常用操作 (176)6.5.1 一维数组元素的常用操作 (177)6.5.2 二维数组元素的常用操作 (183)6.6 数组的应用 (187)6.7控件数组 (190)III6.7.2 控件数组的建立 (190)习题六 (195)第七章 Visual Basic的标准控件 (202)7.1 选择控件 (202)7.1.1 单选按钮 (202)7.1.2 复选框 (203)7.1.3 列表框 (205)7.1.4 组合框 (208)7.2 图形控件 (210)7.2.1 图像框 (210)7.2.2 图片框 (212)7.2.3 直线和形状 (213)7.3 滚动条 (214)7.4 计时器 (216)7.5 框架 (218)7.6 焦点和Tab顺序 (219)7.6.1 焦点（Focus） (220)7.6.2 Tab顺序 (221)习题七 (222)第八章菜单和通用对话框设计 (230)8.1 Visual Basic的菜单 (230)8.1.1 菜单编辑器 (230)8.1.2建立菜单 (231)8.1.3菜单项的控制 (234)8.1.4 菜单项的增减 (234)8.1.5 弹出式菜单 (237)8.2 Visual Basic的通用对话框 (237)8.2.1打开（open）文件对话框 (239)8.2.2 保存（save as）文件对话框 (241)8.2.3 颜色（color）对话框 (242)8.2.4 字体（font）对话框 (243)8.2.5 打印（Print）对话框 (245)习题八 (247)第九章 Visual Basic的多重窗体程序设计与环境应用 (251)9.1 多重窗体程序的设计 (251)9.1.1 与多重窗体的程序设计有关的语句和方法 (251)9.1.2 多重窗体程序的建立 (251)9.1.3 多重窗体程序的执行与保存 (257)9.2 Visual Basic工程结构 (259)IV9.2.2 窗体模块 (260)9.2.3 Sub Main过程 (261)9.3 闲置循环与DoEvents语句 (261)习题九 (263)第十章 Visual Basic的键盘与鼠标事件 (265)10.1 Visual Basic的键盘事件 (265)10.1.1 KeyPress事件 (265)10.1.2 KeyDown和KeyUp事件 (267)10.2 Visual Basic的鼠标事件 (270)10.2.1 鼠标位置 (271)10.2.2 鼠标键 (272)10.2.3 鼠标光标 (274)10.3 对象拖放 (276)10.3.1 与拖放有关的属性、事件和方法 (276)10.3.2 自动拖放 (277)10.3.3 手动拖放 (279)习题十 (281)第十一章 Visual Basic的数据文件 (286)11.1 文件概述 (286)11.1.1 文件结构及用户自定义数据类型 (286)11.1.2 文件种类 (288)11.2 文件的打开与关闭 (289)11.2.1 文件的打开 (289)11.2.2 文件的关闭 (291)11.3 文件操作语句和函数 (292)11.3.1 文件指针 (292)11.3.2 其他语句和函数 (293)11.4 顺序文件 (294)11.4.1 顺序文件的写操作 (294)11.4.2 顺序文件的读操作 (296)11.5 随机文件 (298)11.5.1 随机文件的打开与读写操作 (298)11.5.2 随机文件中记录的增加与删除 (302)11.6 文件系统控件 (302)11.7 文件基本操作 (305)习题十一 (307)附录习题参考答案 (312)习题一 (312)习题二 (312)V习题三 (312)习题四 (313)习题五 (315)习题六 (317)习题七 (318)习题八 (318)习题九 (318)习题十 (318)习题十一 (319)VI第一章引言1.1 程序设计语言程序是指人们使用编程语言开发的、为解决一定问题的、计算机能够执行的指令代码。

About the T utorialVBA stands for V isual B asic for A pplications, an event-driven programming language from Microsoft. It is now predominantly used with Microsoft Office applications such as MS-Excel, MS-Word and MS-Access.This tutorial teaches the basics of VBA. Each of the sections contain related topics with simple and useful examples.AudienceThis reference has been prepared for the beginners to help them understand the basics of VBA. This tutorial will provide enough understanding on VBA from where you can take yourself to a higher level of expertise.PrerequisitesBefore proceeding with this tutorial, you should install MS Office, particularly MS-Excel.Disclaimer & CopyrightCopyright 2016 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher.We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or inthistutorial,******************************************.T able of ContentsAbout the Tutorial (i)Audience (i)Prerequisites (i)Disclaimer & Copyright (i)Table of Contents .................................................................................................................................... i i1.VBA ─ O VERVIEW (1)2.VBA ─ EXCEL MACROS (3)3.VBA ─ EXCEL TERMS (8)Modules (8)Procedure (9)4.VBA ─ MACRO COMME NTS (10)5.VBA ─ MESSAGE BOX (11)6.VBA ─ INPUTBOX (16)7.VBA ─ V ARIABLES (19)Data Types (19)8.VBA ─ CONSTANTS (22)9.VBA ─ O PERATORS (24)The Arithmetic Operators (24)The Comparison Operators (26)The Logical Operators (29)The Concatenation Operators (32)10.VBA ─ DECISIONS (35)If Statement (36)If Else Statement (37)If Elseif - Else statement (40)Nested If Statement (42)Switch Statement (44)11.VBA ─ LOOPS (47)For Loop (48)For Each Loops (50)While Wend Loops (52)Do While Loops (54)Do Until Loops (57)Loop Control Statements (61)Exit For (61)Exit Do (63)12.VBA ─ STRINGS (65)Instr (66)InString Reverse (67)LCase (69)UCase (70)Left (70)Right (72)Mid (73)Ltrim (74)Rtrim (74)Trim (75)Len (75)Space (78)StrComp (79)String Function (80)String Reverse Function (81)13.VBA ─ DATE-TIME FUNCTION (83)Date Functions (83)Date Function (84)CDate Function (84)DateAdd Function (85)DateDiff Function (87)DatePart Function (90)DateSerial Function (91)Format DateTime Function (92)IsDate Function (93)Day Function (94)Month Function (95)Year Function (95)Month Name (96)WeekDay (97)WeekDay Name (98)Time Functions (99)Now Function (100)Hour Function (100)Minute Function (101)Second Function (102)Time Function (102)Time Serial Function (103)TimeValue Function (104)14.VBA ─ ARRAYS (106)Array Declaration (106)Assigning Values to an Array (106)Multi-Dimensional Arrays (107)ReDim Statement (109)Array Methods (110)LBound Function (111)UBound Function (112)Split Function (113)Join Function (115)Filter Function (116)IsArray Function (117)Erase Function (118)15.VBA – USER-DEFINED FUNCTIONS (120)Function Definition (120)Calling a Function (121)16.VBA ─ SUB PROCEDURE (123)Calling Procedures (123)17.VBA ─ EVENTS (125)Worksheet Events (125)Workbook Events (126)18.VBA ─ ERRO R HANDLING (129)Syntax Errors (129)Runtime Errors (129)Logical Errors (130)Err Object (130)Error Handling (130)19.VBA ─ EXCEL OBJECTS (132)Application Objects (132)Workbook Objects (132)Worksheet Objects (133)Range Objects (133)20.VBA ─ TEXT FILES (134)File System Object (FSO) (134)Write Command (139)21.VBA ─ PROGRAMMING CH ARTS (141)22.VBA ─ USER FORMS (144)VBA7VBA stands for V isual B asic for A pplications an event-driven programming language from Microsoft that is now predominantly used with Microsoft office applications such as MS-Excel, MS-Word, and MS-Access.It helps techies to build customized applications and solutions to enhance the capabilities of those applications. The advantage of this facility is that you NEED NOT have visual basic installed on our PC, however, installing Office will implicitly help in achieving the purpose. You can use VBA in all office versions, right from MS-Office 97 to MS-Office 2013 and also with any of the latest versions available. Among VBA, Excel VBA is the most popular. The advantage of using VBA is that you can build very powerful tools in MS Excel using linear programming.Application of VBAYou might wonder why to use VBA in Excel as MS-Excel itself provides loads of inbuilt functions. MS-Excel provides only basic inbuilt functions which might not be sufficient to perform complex calculations. Under such circumstances, VBA becomes the most obvious solution.For example, it is very hard to calculate the monthly repayment of a loan using Excel's built-in formulas. Rather, it is easy to program a VBA for such a calculation.1.VBAAccessing VBA EditorIn Excel window, press "ALT+F11". A VBA window opens up as shown in the following screenshot.8VBA9In this chapter, you will learn how to write a simple macro in a step by step manner.Step 1: First, enable 'Developer' menu in Excel 20XX. To do the same, click File -> Options. Step 2: Click ‘Customize the Ribbon ’ tab and check 'Developer'. Click 'OK'.Step 3: The 'Developer' ribbon appears in the menu bar.2.VBA Step 4: Click the 'Visual Basic' button to open the VBA Editor.Step 5: Start scripting by adding a button. Click Insert -> Select the button.Step 6: Perform a right-click and choose 'properties'.10Step 7: Edit the name and caption as shown in the following screenshot.11Step 8: Now double-click the button and the sub-procedure outline will be displayed as shown in the following screenshot.12Step 9: Start coding by simply adding a message.Step 10: Click the button to execute the sub-procedure. The output of the sub-procedure is shown in the following screenshot.Note: In further chapters, we will demonstrate using a simple button, as explained from step#1 to 10. Hence , it is important to understand this chapter thoroughly.13VBA14In this chapter, you will acquaint yourself with the commonly used excel VBA terminologies. These terminologies will be used in further modules, hence understanding each one of these is important.ModulesModules is the area where the code is written. This is a new Workbook, hence there aren't any Modules.To insert a Module, navigate to Insert -> Module. Once a module is inserted 'module1' is created.Within the modules, we can write VBA code and the code is written within a Procedure. A Procedure/Sub Procedure is a series of VBA statements instructing what to do.3.VBAProcedureProcedures are a group of statements executed as a whole, which instructs Excel how to perform a specific task. The task performed can be a very simple or a very complicated task. However, it is a good practice to break down complicated procedures into smaller ones.The two main types of Procedures are Sub and Function.FunctionA function is a group of reusable code, which can be called anywhere in your program. This eliminates the need of writing the same code over and over again. This helps the programmers to divide a big program into a number of small and manageable functions.Apart from inbuilt Functions, VBA allows to write user-defined functions as well and statements are written between Function and End Function.Sub-proceduresSub-procedures work similar to functions. While sub procedures DO NOT Return a value, functions may or may not return a value. Sub procedures CAN be called without call keyword. Sub procedures are always enclosed within Sub and End Sub statements.15Comments are used to document the program logic and the user information with which other programmers can seamlessly work on the same code in future.It includes information such as developed by, modified by, and can also include incorporated logic. Comments are ignored by the interpreter while execution.Comments in VBA are denoted by two methods.∙Any statement that starts with a Single Quote (�) is treated as comment. Following is an example.∙Any statement that starts with the keyword "REM". Following is an example.16The MsgBox function displays a message box and waits for the user to click a button and then an action is performed based on the button clicked by the user.SyntaxParameter Description∙Prompt - A Required Parameter. A String that is displayed as a message in the dialog box. The maximum length of prompt is approximately 1024 characters. If the message extends to more than a line, then the lines can be separated using a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between each line.∙Buttons- An Optional Parameter. A Numeric expression that specifies the type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If left blank, the default value for buttons is 0.∙Title - An Optional Parameter. A String expression displayed in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.∙Helpfile - An Optional Parameter. A String expression that identifies the Help file to use for providing context-sensitive help for the dialog box.∙Context- An Optional Parameter. A Numeric expression that identifies the Help context number assigned by the Help author to the appropriate Help topic. If context is provided, helpfile must also be provided.The Buttons parameter can take any of the following values:∙0 vbOKOnly - Displays OK button only.∙ 1 vbOKCancel - Displays OK and Cancel buttons.∙ 2 vbAbortRetryIgnore - Displays Abort, Retry, and Ignore buttons.∙ 3 vbYesNoCancel - Displays Yes, No, and Cancel buttons.∙ 4 vbYesNo - Displays Yes and No buttons.∙ 5 vbRetryCancel - Displays Retry and Cancel buttons.∙16 vbCritical - Displays Critical Message icon.∙32 vbQuestion - Displays Warning Query icon.∙48 vbExclamation - Displays Warning Message icon.17∙64 vbInformation - Displays Information Message icon.∙0 vbDefaultButton1 - First button is default.∙256 vbDefaultButton2 - Second button is default.∙512 vbDefaultButton3 - Third button is default.∙768 vbDefaultButton4 - Fourth button is default.∙0 vbApplicationModal Application modal - The current application will not work until the user responds to the message box.∙4096 vbSystemModal System modal - All applications will not work until the user responds to the message box.The above values are logically divided into four groups: The first group (0 to 5) indicates the buttons to be displayed in the message box. The second group (16, 32, 48, 64) describes the style of the icon to be displayed, the third group (0, 256, 512, 768) indicates which button must be the default, and the fourth group (0, 4096) determines the modality of the message box.Return ValuesThe MsgBox function can return one of the following values which can be used to identify the button the user has clicked in the message box.∙ 1 - vbOK - OK was clicked∙ 2 - vbCancel - Cancel was clicked∙ 3 - vbAbort - Abort was clicked∙ 4 - vbRetry - Retry was clicked∙ 5 - vbIgnore - Ignore was clicked∙ 6 - vbYes - Yes was clicked∙7 - vbNo - No was clickedExampleOutputStep 1: The above Function can be executed either by clicking the "Run" button on VBA Window or by calling the function from Excel Worksheet as shown in the following screenshot. Step 2: A Simple Message box is displayed with a message "Welcome" and an "OK" Button19VBA Step 3: After Clicking OK, yet another dialog box is displayed with a message along with "yes, no, and cancel" buttons.Step 4: After clicking the ‘No’ button, the value of that button (7) is stored as an integer and displayed as a message box to the user as shown in the following screenshot. Using this value, it can be understood which button the user has clicked.20VBA21The InputBox function prompts the users to enter values. After entering the values, if the user clicks the OK button or presses ENTER on the keyboard, the InputBox function will return the text in the text box. If the user clicks the Cancel button, the function will return an empty string ("").SyntaxParameter Description∙Prompt - A required parameter. A String that is displayed as a message in the dialog box. The maximum length of prompt is approximately 1024 characters. If the message extends to more than a line, then the lines can be separated using a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between each line.∙Title - An optional parameter. A String expression displayed in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.∙Default - An optional parameter. A default text in the text box that the user would like to be displayed.∙XPos - An optional parameter. The position of X axis represents the prompt distance from the left side of the screen horizontally. If left blank, the input box is horizontally centered.∙YPos - An optional parameter. The position of Y axis represents the prompt distance from the left side of the screen vertically. If left blank, the input box is vertically centered.∙Helpfile - An optional parameter. A String expression that identifies the helpfile to be used to provide context-sensitive Help for the dialog box.∙Context - An optional parameter. A Numeric expression that identifies the Help context number assigned by the Help author to the appropriate Help topic. If context is provided, helpfile must also be provided.6.ExampleLet us calculate the area of a rectangle by getting values from the user at run time with the help of two input boxes (one for length and one for width).OutputStep 1: To execute the same, call using the function name and press Enter as shown in the following screenshot.22Step 2: Upon execution, the First input box (length) is displayed. Enter a value into the input box.Step 3: After entering the first value, the second input box (width) is displayed.Step 4: Upon entering the second number, click the OK button. The area is displayed as shown in the following screenshot.23VBA24Variable is a named memory location used to hold a value that can be changed during the script execution. Following are the basic rules for naming a variable.∙ You must use a letter as the first character.∙You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.∙ Name can't exceed 255 characters in length.∙You cannot use Visual Basic reserved keywords as variable name.SyntaxIn VBA, you need to declare the variables before using them. Data T ypesThere are many VBA data types, which can be divided into two main categories, namely numeric and non-numeric data types.Numeric Data TypesFollowing table displays the numeric data types and the allowed range of values.7.Non-Numeric Data TypesFollowing table displays the non-numeric data types and the allowed range of values.ExampleLet us create a button and name it as 'Variables_demo' to demonstrate the use of variables.OutputUpon executing the script, the output will be as shown in the following screenshot.26VBA27Constant is a named memory location used to hold a value that CANNOT be changed during the script execution. If a user tries to change a Constant value, the script execution ends up with an error. Constants are declared the same way the variables are declared. Following are the rules for naming a constant.∙ You must use a letter as the first character.∙You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.∙ Name can't exceed 255 characters in length.∙You cannot use Visual Basic reserved keywords as variable name.SyntaxIn VBA, we need to assign a value to the declared Constants. An error is thrown, if we try to change the value of the constant.ExampleLet us create a button "Constant_demo" to demonstrate how to work with constants.8.VBAOutputUpon executing the script, the output will be displayed as shown in the following screenshot.28VBA29An Operator can be defined using a simple expression - 4 + 5 is equal to 9. Here, 4 and 5 are called operands and + is called operator . VBA supports following types of operators:∙ Arithmetic Operators ∙ Comparison Operators∙ Logical (or Relational) Operators ∙Concatenation OperatorsThe Arithmetic OperatorsFollowing arithmetic operators are supported by VBA: Assume variable A holds 5 and variable B holds 10, then -9.Arithmetic Operators ─ ExampleAdd a button and try the following example to understand all the arithmetic operators available in VBA.When you click the button or execute the above script, it will produce the following result.30The Comparison OperatorsThere are following comparison operators supported by VBA.Assume variable A holds 10 and variable B holds 20, then -Comparison Operators ─ ExampleTry the following example to understand all the Comparison operators available in VBA.31When you execute the above script, it will produce the following result.The Logical OperatorsFollowing logical operators are supported by VBA.Assume variable A holds 10 and variable B holds 0, then -33Logical Operators ─ ExampleTry the following example to understand all the Logical operators available in VBA by creating a button and adding the following function.When you save it as .html and execute it in the Internet Explorer, then the above script will produce the following result.The Concatenation OperatorsFollowing Concatenation operators are supported by VBA.Assume variable A holds 5 and variable B holds 10 then -Assume variable A = "Microsoft" and variable B = "VBScript", then -Note: Concatenation Operators can be used for both numbers and strings. The output depends on the context, if the variables hold numeric value or string value.Concatenation OperatorsFollowing table shows all the Concatenation operators supported by VBScript language. Assume variable A holds 5 and variable B holds 10, then -35ExampleTry the following example to understand the Concatenation operator available in VBScript:Try the following example to understand all the Logical operators available in VBA by creating a button and adding the following function.Concatenation can also be used for concatenating two strings. Assume variable A = "Microsoft" and variable B = "VBScript" then -Example36Try the following example to understand all the Logical operators available in VBA by creating a button and adding the following function.When you save it as .html and execute it in the Internet Explorer, then the above script will produce the following result.37End of ebook previewIf you liked what you saw…Buy it from our store @ https://38。