vba操作excel经典的通用的代码之一.pptVIP

MyColumn=Target.Column　获取活动单元格所在的列号MyRow=Target.Row　获取活动单元格所在的行号工作表名.U sedRange.Rows.Count　指定工作表已使用的行的总数工作表名.UsedRange.Columns.Count　指定工作表已使用 的列的总数工作表名.Cells(行号, 列号).Value　获取指定工作表某个单元格的值Worksheets(m).Cells(i, j). Value　使用通用的序号方法，调用某个工作表的某个单元格的数据for i=1 to Worksheets.Count　循环当前工作薄的每一 个工作表下面来一段如上代码的综合使用：If (Target.Column=1) ThenFor j=1 To Sheet2.UsedRang e.Rows.CountIf (Trim(Sheet1.Cells(Target.Row, 1).Value)=Trim(Sheet2.Ce lls(j, 1).Value)) ThenSheet1.Cells(Target.Row, 2).Value=Sheet2.Cells( j, 2).ValueEnd IfNext jEnd If如下的代码，是在VBA环境下调用Excel的函数。Worksheets(1).Ra nge(Cells(6, 1), Cells(6, 1))=Application.WorksheetFunction.Sum(Range( Cells(1, 1), Cells(4, 2)))Worksheets(1).Range(Cells(6, 2), Cells(6, 2) )=Application.WorksheetFunction.Average(Worksheets(1).Range(Cells(1, 1 ), Cells(4, 2))) 最好的调用方法了Worksheets(1).Range(C6)=Application.Max(W orksheets(Sheet1).Range(A1:B4))Worksheets(1).Range(D6)=Applicati on.Min(Range(A1:B4))Worksheets(sheet1).Range(E6)=WorksheetFuncti on.Median(Range(A1:B4))再来一下特殊的代码：Excel检查用户按了F1或F3，自动执行某过程的代码Applicat ion.onKey key:={F1}, procedure:=MyAutoInput1Application.onKey key: ={F3}, procedure:=MyAutoInput2下面是完整的代码：Private Sub Workbook_Open() Application.onKey key:={F1}, procedure:=MyAutoInput1Application.on Key key:={F3}, procedure:=MyAutoInput2End SubPrivate Sub Workbook_ SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)MyRow= Target.RowEnd SubPublic MyRow As IntegerSub MyAutoInput1()ActiveSheet. Cells(MyRow, 4).Value=200End SubSub MyAutoInput2()ActiveSheet.Cells(My Row, 4).Value=300End Sub最后，再贴上一组代码，分别返回Excel文件所在的路径，注意，返回的路径方式是不一样的：M sgBox Application.PathActiveSheet.Cells(1, 1).Value=Application.PathM sgBox ThisWorkbook.PathActiveSheet.Cells(2, 1).Value=ThisWorkbook.Path MsgBox Application.DefaultFilePathActiveSheet.Cells(3, 1).Value=Appli cation.DefaultFilePathMsgBox Application.ActiveWorkbook.PathActiveShe et.Cells(4, 1).Value=Application.ActiveWorkbook.PathMsgBox Applicatio n.ActiveWorkbook.FullNameActiveSheet.Cells(5, 1).V