(有关宏与VB,参见《EXCE_VB.XLS》)
用Selection.Offset(ROW, COL)访问单元格
用Selection.Offset(ROW, COL)在单元格中写入计算公式
用ActiveCell.Offset(ROW,COL)在单元格中写入数据、公式
如果经常在 Microsoft Excel中重复某项任务,可以通过“工具>宏>录制宏”取得宏(代码)。然后再用它来自动执行该任务。
除了通过“录制宏”,还可以通过“工具>宏>VB编辑器”,在VB编辑器菜单中选“插入>模块”来创建宏。
从某种角度看,一个EXCEL文件就是一个VisualBasic工程,通过“工具>宏>VB编辑器”可以打开此工程(VBAProject),并可看到工程资源管理器(如看不到,可通过“视图>工程资源管理器”打开它)。
工程资源管理器界面与VB6.0很相似。在这里可以看到VBAProject所包含的对象:
一个工作簿对象(ThisWorkBook);
若干个工作表对象(Sheet1,Sheet2……,类似于VB6.0中的窗体).
一个含有宏的EXCEL文件的VBAProject还有一个或若干个模块。宏就存储在这些模块中。可以通过VB编辑器菜单中“插入>模块”添加新的模块,还可以通过VB编辑器菜单中“插入>窗体”、“插入>类模块”来添加所需的窗体与类模块。
1)直接运行宏,方法:
菜单: 工具>宏…宏>执行
2)通过单击按钮运行宏.方法:
a)菜单: 视图>工具栏>窗体,在窗体中选按钮,拖到工作表适当位置.
b)鼠标移到按钮,出现十字箭头时,按右键,在弹出的菜单中选"指定宏"……..
C)鼠标在按钮上呈"I"形时,可以修改按扭的名称(CAPTION)
注意:工具栏中有一个控件工具箱,控件工具箱的按钮不适用于执行简单的宏.(若想用控件工具箱中控件调用宏,参见第4种方法)
3)通过单击图片运行宏.方法:
a)菜单: 插入>图片>来自文件...,插入一个图片.
b)右击图片,在弹出的菜单中选"指定宏"……..
4)利用事件调用宏
在VB编辑器中,可以看到工程管理视图中有:EXCEL对象(含工作簿,若干个工作表)及若干个模块(宏就在模块中).
又:如果在VB编辑器中创建一FUCTION()可以在单元格输入“=宏名”来调用宏
(参见自定义函数)
在单元格中输入计算公式时可以使用自定义函数(Function)。例如,可以创建一个小写金额转换为大写金额的函数FunctionDaXie(ByValNum),在A2单元格填上小写金额,在B2单元格中输入“=DaXie(A2)”就可以在B2中显示大写金额。(参见《小写金额转换为大写.doc》)
(以下摘自“银行付款NEW”,改变E5单元格数值时将执行宏GetData,该宏将在另一工作表中查找与E5相匹配的收款人代码,并取得该收款人的账号与开户行等资料)
Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox "单元格:" & Target.Address
If Target.Address = "$E$5" Then
GetData
End If
End Sub
EXCEL中VBA代码中常量书写规则:以“xl”开头,如:
LookIn:=xlValue (查找值)
LookIn:=xlFormulas (查找公式)
SearchOrder:=xlByColumns(以列为序查找)
SearchDirection:=xlNext(向后查找)
(以下摘自《银行付款1.xls》,根据工作表“兴行”的E5单元格中收款人代码,在另一名为“收款人”的工作表[该表有收款人相关资料的数据清单]中查找该收款人开户行及账号资料,注意Find变量的使用)
Sub GetData()
Counter = 0
Do Until Selection.Offset(Counter, 0).Value = "" Or Find = 1
Loop
Sheets("兴行").Select
End Sub
以下取自《银行存款4.xls》,该文件有两个工作表:“收款人”、“兴行”。“收款人”工作表为数据清单,字段分别为:序号(A栏)、收款人名称(B栏)、账号(C栏)、开户行(D栏)、省份(E栏)、县市(F栏)。
用户在工作表“兴行”的E2单元格输入收款人序号后,该表的收款人其它资料将自动填列。
实现方法-----
在收款人名称单元格输入以下函数:
LOOKUP($E$2,收款人!$A$4:$A$100,收款人!B$4:B$100)
在收款人账号单元格输入以下函数:
LOOKUP($E$2,收款人!$A$4:$A$100,收款人!C$4:C$100)
在收款人开户行单元格输入以下函数:
LOOKUP($E$2,收款人!$A$4:$A$100,收款人!D$4:D$100)
在收款人所在省区单元格输入以下函数:
LOOKUP($E$2,收款人!$A$4:$A$100,收款人!E$4:E$100)
在收款人所在县市单元格输入以下函数:
LOOKUP($E$2,收款人!$A$4:$A$100,收款人!F$4:F$100)
注意:
1)在输入收款人名称的函数后,可以将该函数粘贴到账号、开户行、省区、县市等单元格,而后只要作少量更改即可。(因为函数中的大多数单元格是绝对引用“$”)
2)收款人!$A$4:收款人!$A$100 指明数据清单所在区域,可简化为:收款人!$A$4:$A$100
(以下摘自《基材消耗计算表.XLS》调用前应先声明一个:nCheck=0。密码通过验证时,nCheck=0;密码为空时,返回1;密码不对时,返回9)
Sub MyCheckpass()
Dim PassWords As String
Dim MyMesText As String
MyMesText = "初始化将完成以下工作:1)用期末在产品的数据替换期初在产品的数据"
MyMesText = MyMesText &"
MyMesText = MyMesText &"
MyMesText = MyMesText &"
PassWords = InputBox(MyMesText, "皮革基材消耗计算表初始化对话框")
If PassWords <> "8813054" Then
End If
Exit Sub
End Sub
Selection.Offset(ROW,COL)中的ROW为当前激活单元格竖向偏移量(行偏移),COL为当前激活单元格横向偏移量(列偏移),向左、下为正,向右、上为负。
例:
Range("E5").Activate '以下Selection.Offset(ROW,COL)中的ROW,COL均为相对于E5单元格的偏移量
Selection.Offset(-1, -1).Value = "-1-1" '在D4单元格中写入"-1-1"
Selection.Offset(1, 2).FormulaR1C1 ="=SUM(R[0]C[-3]:R[0]C[-1])"
'以上在G6单元格中写入"=SUM(D6:F6)" 其中R[0]C[-3]是相对于公式所在单元格的行偏移与列偏移。
Selection.Offset(3, 2).FormulaR1C1 ="=SUM(R[0]C[-3]:R[0]C[-1])"
'以上在G8单元格中写入"=SUM(D8:F8)"
……
参见Selection.Offset(ROW,COL)访问单元格
……
Range("B2").Activate
ActiveCell.Offset(1, 1).Value = 50 ‘在C3格中写入50
ActiveCell.Offset(2, 2).FormulaR1C1 ="=SUM(R[0]C[-3]:R[0]C[-1])" ‘在D4格中写入“=SUM(A4:C4)”
ActiveCell.Offset(2, 2).Range("D3").Value = 100
‘在G6格中写入100。此处ActiveCell.Offset(0, 0)为B2,ActiveCell.Offset(2,2)为D4,
……
(参见《应交税金账.xls》)
ActiveWorkbook.Names.Add Name:="_55",RefersToR1C1:="=R12C1:R12C3" '将A12:C12区域取名为"_12"
ActiveWorkbook.Names.Add Name:="_1", RefersToR1C1:="=R1C1"'将A1单元格取名为"_1"
ActiveWorkbook.Names.Add Name:="_2", RefersToR1C1:="=R1C2"'将B1单元格取名为"_2"
Range("A1").Activate'以下Selection.Offset(row,col)中的row,col均为相对于A1单元格的偏移量
Selection.Offset(10, 1).FormulaR1C1 = "=SUM(_2:R[-1]C[0])"'在B11格中写入"=SUM(_2:A10)", 等同于"=SUM(B1:B10)
注:
在《应交税金账》中,因为不知道如何在R[]C[]中写入变量名,所以只好用为单元格命名的方法来。后来发现,在R[]C[0]中定入变量名很简单,以下就是一例(参见《三栏式账页》):
n=15
Selection.Offset(10,10).FormulaR1C1 = "=SUM(R[" & -n &"]C[0]:R[-1]C[0])"
(以下摘自《三栏式账页.xls》,在工作表左上的一个连续区域查找Value ="借方金额"的单元格。区域大小:宽MAX_COL(列),高为MAX_ROW(行),找到后,中止查找。注意find变量的使用方法。
……
Range("a1").Activate
find = 0
For n = 0 To MAX_COL
Next
……
(以下摘自《三栏式页.xls》)
Application.ScreenUpdating = False
以下摘自《三栏式账页.xls》,当余额为贷方时,用红字显示“贷”字
(通过菜单“格式>条件格式”可以手工为单元格设置条件格式)
联系客服