Excel VBA programming basics

Excel VBA 程序设计基础
Excel VBA Programming Basics
XJTLU Library Training Session, May 8th 7-9pm
Hang Dong | 董行
PhD student in CSSE
Acknowledgement to all resources used in this
presentation: they are for educational use only.

上课之前
Before the class
 Let me know about you
 Chinese or English?
 Prior knowledge in programming?
 Prior knowledge in Excel (functions, charts, pivotTables)?
 Prior knowledge in VB (Microsoft Visual Basics)?
 Prior knowledge in Excel VBA?
 Know nothing above? (小白？)
 It doesn’t matter~~~

VBA, Visual Basics for Applications
简而言之，VBA是微软创造的一种编程语言，方便用户来自动地控制Excel等工具。
In a nutshell, it is a program language developed by Microsoft for users to
customise and automate tools like Excel.
用Excel VBA语言写出来的指令或操作过程，称为宏，即巨大的、可重复的操作。
Commands or operations written by Excel VBA are called Macros, which mean
large and repeatable operations.
什么是 VBA 和宏: What is VBA and
Macro
+ =
Robot icon in Wikimedia, CC BY-SA 3.0,
https://commons.wikimedia.org/wiki/Robot#/med
ia/File:Animation2.gif

Advantage & disadvantages for VBA
Advantages:
 省时省力处理数据 Saving time for your tasks
 VBA自带安装在Excel中 Not need to install
 你会在职场中更加顺利 You become popular around your office
 在办公领域(尤其是金融会计领域)非常流行 Popular in Finance, accounting…
Disadvantages:
 你需要学习，虽然不难 You need to learn about coding, fortunately not hard
 VBA跨版本的兼容性不强，在提升 Compatibility across versions is not strong

Excel VBA is
still prospering
 A survey in 2016 shows that
VBA is alive, well and
prospering.
 Acknowledgement to an image in
ANALYSTCAVE. VBA IS DEAD? WHAT’S
THE FUTURE OF VBA?. 2016.2.
http://analystcave.com/vba-dead-
whats-future-vba/

Excel VBA 适用于哪类问题？
What problems can Excel VBA solve?
任何对Excel自动化的任务 Tasks related to automating some parts of Excel
 需要大量重复工作的任务，不止于 Repeated tasks not limited to,
 批量文本的格式化处理 Text formatting
 批量单元格，表，工作簿的处理 Cell, sheet and workbook processing
 批量图表、报告的生成 Charts and Reports generation
 批量数据的导入、加工和导出 Data import, processing and export
 创建Add-in加载项和Ribbon选项卡 [本节课不涉及] Create custom add-ins and Ribbon Tabs. [not be
covered today]
 其实什么都可以做…比如小型的信息管理系统或者游戏 Actually much more than you can
imagine…such as small information management system or a game.

主要内容 Content
 如何编写 Excel VBA 代码? How to use Excel VBA to solve problems?
 Variables
 Objects (Cell, Range, Sheet), Properties and Actions
 Basic Syntax: Loop and Control
 Basic Applications: data aggregation, import/export data, data
formatting
 How/Where to learn more about Excel VBA?
 Recording Macro
 Looking for tutorials online and books

新技能·开：启用开发工具 Enable “Developer”
 File -> Options -> Customize Ribbon -> Tick Developer

 Enable all macros (not necessary)

VBE (Visual Basic Editor)
 Developer Ribbon -> Visual Basic

Common Shortcuts in VBE in Windows
 Open VBE in Excel：Alt+F11
 属性窗口 Properties：F4
 对象浏览器 Object Browser：F2，Shift+F2
 帮助文档 Help Document：F1
 逐行调试 Step Into Debugging：F8
 设置断点 Toggle Breakpoint：F9

第一个程序 The first program
 “Hello World”
 通过按钮1，在第一个工作表的单元格“A1”上显示”Hello World!”.
 Show “Hello World!” on cell A1 in the first Sheet through Button1.
 通过按钮2，在Excel中弹出”Hello World!”.
 Pop dialog box “Hello World!” through Button2.
 hello_world.xlsm

录制宏
The 1st programme: Recording Macro
 录制宏 Recording Macro
 测试宏 Testing Macro
 检查/学习宏 Examining the Macro / Learn from the Macro code
 修改宏 Modify the Macro
 保存宏 Saving the Macro

录制宏 Recording Macro
 Developer -> Record Macro -> Set shortcut -> OK
 Performing some operations
 Stop Recording

测试宏 Testing Macro
 打开新的工作表，运行刚刚录制的宏
Run the recorded Macro on a new worksheet
 Method 1: 用快捷键运行 Ctrl + h Using the Shortcut Ctrl + h
 Method 2: 创建新的按钮来运行这个宏 Insert a button on the sheet
to run the Macro
 Method 3: 直接在VBE中运行 Run it directly in VBE

检查/学习宏 Examining the Macro /
Learn from the Macro code
 打开VBE环境编辑器，查看生成的宏代码
Check Macro Code in VBE.
 天啦噜！右边这一串是什么意思呢？
Oh!!! What does these mean on the right?
绿色部分不执行哦，只是注释
The green part does not execute: It is notes.

修改宏 Modify the Macro
 我们来一起加几行代码
Let put some new lines of code
 Add “For i=1 To 10” under Sub XX()
 Add “Next i” just before End Sub
 Change Range(A1).Select to
Range(“A & i”).Select
 Run it!

 我们再来一起加几行代码
Let put some new lines of code
 Add “If i Mod 2 = 0” before With XX.XX
 Add “End If” just after End With
 Run it!

保存宏 Save the Macro
 Developer -> Insert -> Button (From Control)
 File -> Save As -> set format as .xlsm

知识点1：Excel VBA 中的过程
Basic 1: Sub in Excel VBA
 过程即执行任务的一组语句。A Sub procedure is a group of VBA statements
that performs an action (or actions) with Excel.
 Sub sub_name([parameters])
 End Sub
 从其它过程中调用某过程 Executing the procedure from another procedure
 Call sub_name

知识点2: 对象、属性和方法
Basic 2: Objects, properties and actions
 对象.方法/属性 Object.Action/Property
 Range(“A1”).Select ‘选择单元格A1 Select the Cell A1.
 ActiveCell.FormulaR1C1 ‘返回或设置指定对象的公式 Returns or sets the formula for the object.
 With-End With 结构
 对象.对象 Object.Object
 Application.Selection ‘选中的对象 Returns the selected object in the active window for
an Application object.
 Selection.Interior ‘单元格的内部 Returns an Interior object that represents the interior of the
specified object

对象与属性 Object and properties
 Range对象的Value属性（单元格.内容）
类似有 Range(“A1”).Row（单元格.行数）
Range(“A1”).Column（单元格.列数）
Range(“A1:B10”).Count（单元格.数量） Acknowledgement to the image from: Excel Home
编著. 别怕，Excel VBA其实很简单. 人民邮电出版社.
2012

对象与方法 Object and actions
 Range对象的Select方法
类似还有 Range(”A1”).Clear
(单元格.清除内容和格式)
Range(“A1”).ClearContents
(单元格.清除内容但不清除格式)
[a1].copy destination:=[a2]
(单元格.复制到:=单元格)
Acknowledgement to the image from: Excel Home
编著. 别怕，Excel VBA其实很简单. 人民邮电出版社.
2012

对象层次结构 Hierarchical Structure of Objects
Application
Workbooks WorkSheets
Charts
Windows
AddIns
ChartObjects
Range
PageStep
PivotTable
…………
………… …………
Excel库下的类 Application类的对象 Workbooks类的对象 WorkSheets的对象

获取单元格并赋值 Access and set value to a
Range/Cell on Excel VBA
 Application.Workbooks(“Book1”).WorkSheets(1).Range(
“A1”).Value=“I love VBA”
 ThisWorkbook.WorkSheets(1).Range(“A1”).Value=“I
love VBA”
 Range(“A1”)=“I love VBA”
 [A1]=“I love VBA”

基本的运算符
 ‘赋值 ‘give a value
 a = 3
 a = a+1
 Cells(1,2).Value = “Hello”
 Range(“A1”).Value = “Hello”
 ‘加减乘除和求余
 +,-,*,/,mod cells(1,1).Value = 17 mod 3
 And, Or, Not
 If a>3 Or b>3 then c=2
 If Not c=2 then c=3 ‘If c<>2 then c=3
 If Range(“A1”).Value <> “” AND Range(“B1”).Value <> “” then …

知识点3: 程序控制结构之判断与循环
Basic 3: Control Flow, Loop and Decision Structures
 控制结构 Decision Structure
 ' Multiple-line syntax:
 If condition [ Then ]
 [ statements ]
 [ ElseIf elseifcondition [ Then ]
 [ elseifstatements ] ]
 [ Else
 [ elsestatements ] ]
 End If
 Example: see “if_then_else_show discount.xlsm”
Image from https://docs.microsoft.com/en-
us/dotnet/articles/visual-basic/programming-
guide/language-features/control-flow/decision-
structures

 循环结构用于遍历和重复执行代码
Loop Structure for repetitions
 比如你可以在代码中遍历以下内容 You can do a loop in
对一个区域中的所有单元格 all cells in a range
对所有打开的工作簿 all open workbooks
对所有工作表 all worksheets in a workbook
对一个单元格中的所有字符 all characters in a cell
对一个文件夹中的所有文件 all files in a folder
 循环结构
 For … Next …
 For Each … in Next
 Do … Loop
Continued: Loop Structure
Image from https://docs.microsoft.com/en-
us/dotnet/articles/visual-basic/programming-
guide/language-features/control-flow/loop-
structures

“For Next” 循环结构
 For counter [ As datatype ] = start To end [ Step step ]
 [ statements ]
 [ Continue For ]
 [ statements ]
 [ Exit For ]
 [ statements ]
 Next [ counter ]

Nested “For Next” 嵌套循环结构
 Sub FillRange ()
 Dim Col As Integer
 Dim Row As Long
 For Col = 1 To 5 ‘ 外部的循环 The outer loop
 For Row = 1 To 12 ‘ 内部的循环 The inner loop
 Cells(Row, Col) = Rnd
 Next Row
 Next Col
 End Sub
 See example “fill in range.xls”

“For Each … Next” 循环结构
 For Each element [ As datatype ] In group
 [ statements ]
 [ Continue For ]
 [ statements ]
 [ Exit For ]
 [ statements ]
 Next [ element ]
 See example “get worksheet names.xlsm”
‘write worksheet names into column A
Sub shtname()
Dim sht As Worksheet, i As Integer
i = 1
For Each sht In Worksheets
Range("A" & i).Value = sht.Name
i = i + 1
Next sht
End Sub

“Do Loop” 循环结构
 Do { While | Until } condition
 [ statements ]
 [ Continue Do ]
 [ statements ]
 [ Exit Do ]
 [ statements ]
 Loop
 Do
 [ statements ]
 [ Continue Do ]
 [ statements ]
 [ Exit Do ]
 [ statements ]
 Loop { While | Until } condition

Continued: Nested Control Structures
嵌套控制结构
 循环之中的循环 Loop inside a loop
 循环之中的判断 Decision inside a loop
 For i= 1 to 10
 If i mod 2 = 0 then
 ‘i 是1到10之间的偶数 i is an even number between 1 to 10.
 End If
 Next I
 判断之中的循环 Loop inside a decision

知识点4: VBA中的数据类型和变量
Basic 4: data type and variables in VBA
 Variant (the default data type)
 Boolean
 Integer/Long
 Single/Double
 String
 Date
Sub 按钮1_Click()
‘About the data type Variant
MsgBox TypeName(MyVar)
MyVar = "123"
MsgBox TypeName(MyVar)
MsgBox MyVar + MyVar
'MsgBox TypeName(MyVar)
MyVar = MyVar / 2
MyVar = "Answer: " & MyVar
MsgBox MyVar
End Sub

Acknowledgement to
the image from:
Excel Home编著. 别
怕，Excel VBA其实很
简单. 人民邮电出版
社. 2012

声明变量
Declare variables
 Dim a [as Variant]
 Dim IsLeaf as Boolean
 Dim columnNum as Integer
 Dim rowNum as Long
 Dim Meet_Rate as Single
 Dim Index_Meet_Rate as Double
 Dim FileName as String
 Option Explicit ‘要求强制声明所有变量
Using Option Explicit forces you to declare all your variables.

知识点5: 字符串的操作与函数
String Manipulation
 字符串的连结 We use the & operator to concatenate (join) strings.
 Dim text1 As String, text2 As String
text1 = "Hi"
text2 = "Tim"
MsgBox text1 & " " & text2
 字符串的截取 Extraction of substrings from a string
 Dim text As String
text = “example text”
MsgBox Left(text, 4) ‘To extract the leftmost characters from a string, use Left.
 MsgBox Right("example text", 2) ‘To extract the rightmost characters from a string, use
Right. We can also directly insert text in a function.
 MsgBox Mid("example text", 9, 2) ‘To extract a substring, starting in the middle of a
string, use Mid.
Adapted from http://www.excel-
easy.com/vba/string-manipulation.html

Continued…
 获取字符串的长度 To get the length of a string, use Len.
 MsgBox Len("example text")
 找到某字符子串在字符串中的位置
To find the position of a substring in a string, use Instr.
 MsgBox Instr("example text", "am")
 字符串的分割 split a text string into an array, by making use of a delimiter
character.
 Dim WrdArray() As String
 WrdArray() = Split("How are you")
Adapted from http://www.excel-
easy.com/vba/string-manipulation.html and
http://www.exceltrick.com/formulas_macros
/vba-split-function/

练习1
Exercise (on your handout)
 Recording and modifying a Macro 录制宏并进行修改
 录制一个宏 Record a Macro; 运行宏 Test a Macro
 查看宏 Examine a Macro; 修改宏 Modify a Macro
 再运行宏 Run a Macro again; 保存宏 Save a Macro
 请发挥自己的想象力录制宏，并完成自己的程序 Use your imagination to create a
Macro and complete your programme.
 推荐录制：新建工作表并创建表头，复制数据，改变格式，打开文件，保存文件等
Recommendation for recording as a macro: create new worksheet and titles, copy data,
change format, open and saving files.

练习2
Exercise (on your handout)
 请根据学生 (一共500名) 的雅思成绩以及申报的专业来判断他们是否需要读语言
班。Please decide whether the student (500 of them in total) should take a
language class, according to their IELTS scores and applied majors.
 规则: 不用读语言班的条件 Rule: Criteria for not required taking a language class
Listen Speak Reading Writing In Total
Criteria for "Language" >=7 >=7 >=7 >=7
>=28
(7 in ave.)
Criteria for "Math" >=5.5 >=5.5 >=5.5 >=5.5
>=22
(5.5 in ave.)
Criteria for "Computer_Science" >=6 >=6 >=6 >=6
>=24
(6 in ave.)

应用1:多工作簿的数据汇总
Aggregating data from multiple workbooks
 不同班级成绩表的数据汇总 Put all students’ data together from different class
 一共有约30个不同的工作簿 There are around 30 worksheets
 See folder “data aggregation from workbooks”
序号姓名性别族别出生年月家庭住址班级备注
1 陈发新男汉族 19980726 细岩村翁旮组七1
2 李文雄男汉族 19981003 老院村茶园组七1
3 李涛男汉族 19990912 左八村四组七1
Adapted from 《别怕，其实VBA很简单》
4.7.10 汇总同文件夹下多工作簿数据
Acknowledgement to the case
adapted from: Excel Home编著.
别怕，Excel VBA其实很简单. 人
民邮电出版社. 2012

应用2: 多工作表的数据汇总
Aggregating data from multiple worksheets
 汇总成绩单: 将不同工作表中的数据复制到第一个工作表
Put together the records: To copy all data from multiple worksheets to the
first worksheet.
Acknowledgement to the case
adapted from: Excel Home编著.
别怕，Excel VBA其实很简单. 人
民邮电出版社. 2012

应用3:导入文本文档数据
Import data in .txt format
 可以通过录制宏的方式完成
This can be done through recording a macro.
 See example “Import_txt_data.xlsm”

其它数据获取和处理的应用？
Other apps about data acquisition and processing?
 从网页中获取数据
Getting data from webpage
 从html文档中获取数据
Getting data from html document
 从Access数据库中获取数据
Getting data from Access
 对数据进行格式化的处理
Formatting data to your own needs
Recording Macro
Modify Macro
with loop
with decision
…
Test/Run Macro
Make your program
user-friendly

倒数第二件事情一些tips
The second last thing: some tips
 Use immediate window and local window
 Use F8 to check each line of code
 Set check point using your mouse
 Using Option Explicit to force declaring all variables

Immediate View and Local View
 (1) VBA -> View -> Immediate Window; (2) VBA -> View -> Local Window

今天没有涉及到但重要的知识 Important
knowledge not discussed today
 窗体 Form
 图表对象 The Object “Chart”
 自定义函数 Write your own Functions
 自定义Ribbon选项卡 Write your own Ribbons (XML)
 VSTO (Visual Basics, C#, .Net Framework)
 Apps for Office 2013 and upwards (XML)
 Roadmap for Apps for Office, VSTO, and VBA
https://blogs.msdn.microsoft.com/officeapps/2013/06/18/roadmap-for-
apps-for-office-vsto-and-vba/

资源推荐 Resources
网站 websites
 Excel VBA documentation for Excel 2013 and above
https://msdn.microsoft.com/en-us/library/office/ee861528.aspx [in English]
 Excel Home 论坛中的VBA板块 http://club.excelhome.net/forum-2-1.html [推荐]
 Excel Home. Excel VBA 视频教程 http://www.excelhome.net/video/excel/vba/
 善用百度、Bing、Google… Make good use of search engines.
 Excel Easy. http://www.excel-easy.com/vba.html [in English]
 VBA Q & A on Stack Overflow http://stackoverflow.com/questions/tagged/vba
图书 Books
 Walkenbach, J. (2015). Excel VBA programming for dummies. Hoboken, NJ: For Dummies, A
Wiley Brand. http://10.7.1.204:81/info.php?resid=99493065 (For XJTLU Lib users only)
 Excel Home编著. 别怕，Excel VBA其实很简单. 人民邮电出版社. 2012
 Excel Home编著. 别怕，Excel VBA其实很简单（第二版）. 北京大学出版社. 2016

Books available
@ XJTLU Lib
 Chinese/English Books

Books available
@ XJTLU Lib
 English Books

Thank you for your attention!
Hang Dong
hang.dong@xjtlu.edu.cn

Excel VBA programming basics

In this document

More Related Content

What's hot

Similar to Excel VBA programming basics

More from Hang Dong

Recently uploaded

Excel VBA programming basics