Excel VBA 的基本使用与代码示例

前言

本文整理了 Excel 中 VBA 的常用操作代码示例。

使用了 ms office 2016 。

开始

step.1 新建excel文件

新建excel文件，命名为test.xlsx ,如下图所示。

step.2 启用开发工具

文件->选项->自定义功能区，将【开发工具】全打钩后，点击确定。

step.3 VBA调用Excel函数

使用录制宏功能，实现VBA调用Excel的SUM函数。

首先准备测试数据。在 Sheet1 的 A1 单元格输入 1， B1 单元格输入 2 。

下面开始录制宏。 点击工具栏的[开发工具]->[录制宏]。

在弹出框中直接点击确定

然后选中 A1：C1 单元格，点击工具栏的[开始]->[自动求和]

点击工具栏的[开发工具]->[停止录制], 然后点击【宏】，选中刚刚录制的【宏1】后，点击右侧的编辑按钮。可以看到宏1的代码如下

Sub 宏1()
'
' 宏1 宏
'

'
    Range("A1:C1").Select
    Range("C1").Activate
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
    Range("A1:C1").Select

End Sub

从上面的操作可以看出，使用VBA时，在【录制宏】的基础上修改代码 是一种不错的选择。VBA中可以直接调用EXCEL的公式，对于其他公式也可以用录制宏的方法来实现代码。

step.4 获得行数列数

首先，构造测试数据：

测试数据中，有3行4列，下面我们要获得3和4这两个数字。同样，我们采用先录制宏的方式。

点击录制宏后，选中A10单元格，按Ctrl+↑方向键，焦点将转移到A3单元格，然后点击K1单元格，按Ctrl+←方向键，焦点将转移到D1单元格。点击停止录制后，查看所录制宏的VBA代码。

Sub 宏3()
'
' 宏3 宏
'

'
    Range("A10").Select
    Selection.End(xlUp).Select
    Range("K1").Select
    Selection.End(xlToLeft).Select
End Sub

将该宏的内部代码替换为下面代码

Dim rowCount As Integer
Dim colCount As Integer

Range("A10").Select
Selection.End(xlUp).Select
rowCount = Selection.row

Range("K1").Select
Selection.End(xlToLeft).Select
colCount = Selection.Column

MsgBox ("行数为" & rowCount & " ,列数为" & colCount)

执行该宏，运行结果符合预期

在行数列数未知的情况下，应将上面示例中的A10替换为A10000等非常靠下的格子。K1同理。

step.5 构造SQL的insert语句

首先，构造测试数据(纯属虚构)：

假设4个字段分别为 id,name,job,salary，需要插入到employee表中。拿第一条数据为例：

insert into employee(id,name,job,salary) values (‘10001’,‘吴凡’,‘开发工程师’,‘21000’);

下面我们要构造SQL语句，并将SQL语句放到G1单元格。 VBA代码如下：

Dim i As Integer
Dim sqlStr As String
Dim sqlTemp As String

sqlStr = ""
For i = 1 To 20
    sqlTemp = "insert into employee(id,name,job,salary) values ('" & Range("A" & i).Value & "','" & Range("B" & i).Value & "','" & Range("C" & i).Value & "','" & Range("D" & i).Value & "');"
    sqlStr = sqlStr & sqlTemp & vbCrLf
Next i

Range("G1").Value = sqlStr

运行该代码后：

将G1单元格内容复制出来：

"insert into employee(id,name,job,salary) values ('10001','吴凡','开发工程师','21000');
insert into employee(id,name,job,salary) values ('10002','马里奥','美工','15000');
insert into employee(id,name,job,salary) values ('10003','任天堂','总经理','65000');
insert into employee(id,name,job,salary) values ('10004','张益达','扫地','10000');
insert into employee(id,name,job,salary) values ('10005','娃哈哈','扫地','10000');
insert into employee(id,name,job,salary) values ('10006','爽歪歪','扫地','10000');
insert into employee(id,name,job,salary) values ('10007','张三','扫地','5000');
insert into employee(id,name,job,salary) values ('10008','李四','扫地','5000');
insert into employee(id,name,job,salary) values ('10009','王五','扫地','5000');
insert into employee(id,name,job,salary) values ('10010','赵六','扫地','5000');
insert into employee(id,name,job,salary) values ('10011','小七','扫地','5000');
insert into employee(id,name,job,salary) values ('10012','小八','扫地','5000');
insert into employee(id,name,job,salary) values ('10013','小九','扫地','5000');
insert into employee(id,name,job,salary) values ('10014','赵云','扫地','5000');
insert into employee(id,name,job,salary) values ('10015','王宝','扫地','5000');
insert into employee(id,name,job,salary) values ('10016','孙1','扫地','5000');
insert into employee(id,name,job,salary) values ('10017','周2','扫地','5000');
insert into employee(id,name,job,salary) values ('10018','李11','扫地','5000');
insert into employee(id,name,job,salary) values ('10019','薇11','扫地','5000');
insert into employee(id,name,job,salary) values ('10020','歌11','扫地','5000');
"

结果符合预期。

结束

这里整理了几个小例子，如果读者有其他需求的话可以直接留言，博主会将留言的内容做成代码示例补充到该博客。