vba操作Excel经典的通用的代码PPT

2020/3/15
24
sgBox ThisWorkbook.PathActiveSheet.Cells(2, 1).Value=ThisWorkbook.Path
2020/3/15
25
'MsgBox Application.DefaultFilePathActiveSheet.Cells(3, 1).Value=Appli
2020/3/15
6
lls(j, 1).Value)) ThenSheet1.Cells(Target.Row, 2).Value=Sheet2.Cells(
2020/3/15
7
j, 2).ValueEnd IfNext jEnd If如下的代码，是在VBA环境下调 用Excel的函数。Worksheets(1).Ra
2020/3/15
8
nge(Cells(6, 1), Cells(6, 1))=Application.WorksheetFunction.Sum(Range(
2020/3/15
9
Cells(1, 1), Cells(4, 2)))Worksheets(1).Range(Cells(6, 2), Cells(6, 2)
MyColumn=Target.Column 获取活动单元格所在的列号 MyRow=Target.Row 获取活动单元格所在的行号工作表 名.U
2020/3/15
1
sedRange.Rows.Count 指定工作表已使用的行的总数工 作表名.UsedRange.Columns.Count 指定工作表已使用
2020/3/15
12
orksheets("Sheet1").Range("A1:B4"))Worksheets(1).Range(" D6")=Applicati
2020/3/15
13
on.Min(Range("A1:B4"))Worksheets("sheet1").Range("E6")= WorksheetFuncti
2020/3/15
18
Key key:="{F3}", procedure:="MyAutoInput2"End SubPrivate Sub Workbook_
2020/3/15
19
SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)MyRow=
2020/3/15
4
个工作表下面来一段如上代码的综合使用：If (Target.Column=1) ThenFor j=1 To Sheet2.UsedRang
2020/3/15
5
e.Rows.CountIf (Trim(Sheet1.Cells(Target.Row, 1).Value)=Trim(Sheet2.Ce
2020/3/15
2
的列的总数工作表名.Cells(行号, 列号).Value 获取指定工 作表某个单元格的值Worksheets(m).Cells(i, j).
2020/3/15
3
Value 使用通用的序号方法，调用某个工作表的某个单 元格的数据for i=1 to Worksheets.Count 循环当前工作薄 的每一
2020/3/15
28
n.ActiveWorkbook.FullNameActiveSheet.Cells(5, 1).Value=Application.Act
2020/3/15
29
iveWorkbook.FullName'MsgBox Application.ActiveWorkbook.NameActiveSheet
2020/3/15
22
Row, 4).Value=300End Sub最后，再贴上一组代码，分别返 回Excel文件所在的路径，注意，返回的路径方式是不一 样的：'M
2020/3/15
23
sgBox Application.PathActiveSheet.Cells(1, 1).Value=Application.Path'M
2020/3/15
14
on.Median(Range("A1:B4"))再来一下特殊的代码：Excel检 查用户按了F1或F3，自动执行某过程的代码Applicat
2020/3/15
15
ion.onKey key:="{F1}", procedure:="MyAutoInput1"Application.onKey key:
2020/3/15
26
cation.DefaultFilePath'MsgBox Application.ActiveWorkbook.PathActiveShe
2020/3/15
27
et.Cells(4, 1).Value=Application.ActiveWorkbook.Path'MsgBox Applicatio
2020/3/15
20
Target.RowEnd SubPublic MyRow As IntegerSub MyAutoInput1()ActiveSheet.
2020/3/15
21
Fra Baidu bibliotek
Cells(MyRow, 4).Value=200End SubSub MyAutoInput2()ActiveSheet.Cells(My
2020/3/15
10
)=Application.WorksheetFunction.Average(Worksheets(1).Ra nge(Cells(1, 1
2020/3/15
11
), Cells(4, 2))) '最好的调用方法了 Worksheets(1).Range("C6")=Application.Max(W
2020/3/15
16
="{F3}", procedure:="MyAutoInput2"下面是完整的代码： Private Sub Workbook_Open()
2020/3/15
17
Application.onKey key:="{F1}", procedure:="MyAutoInput1"Application.on
2020/3/15
30
.Cells(6, 1).Value=Application.ActiveWorkbook.Name如上的 代码够经典了，适合擅长于使用智能化