VBA中调用Excel函数

VBA中编写的事件，不断的触发循环 ：先程序开始时关闭  Excel.Application.EnableEvents =False；结束时打开：Excel.Application.EnableEvents =True

 

调用excel 函数：j= excel.Application.WorksheetFunction.vlookup() （on error resume next）

j= excel.Application.WorksheetFunction.xxxx

example:

求ExcelA列非空单元格:excel.Application.WorksheetFunction.CounA(Range("A:A"))

求ExcelA列  男性个数:excel.Application.WorksheetFunction.Counif(Range("A:A"),"男")

求数组最大值，excel.Application.WorksheetFunction.Max(arr)

求数组最大值下标，excel.Application.WorksheetFunction.Match(Max(arr),arr）

求数组某个值（123） 下标，excel.Application.WorksheetFunction.Match(“123”,arr）

Range("C3：G10").Rows("3:5").Select

Cells

UsedRange

ActiveSheet.Cells.Rows.Count

如果有空行，set r=ActiveSheet.UsedRange。i=r.Row+r.Rows.Count-1

ActiveSheet.Range("A65535").End(xlUp).Row

 有空行总数，set r=Range("A" &Rows.Count).End(xlup)

r.Row

第一列的总行数：Cells(Rows.Count, 1).End(xlUp).Row

第一行的总数：Cells(1, Columns.Count).End(xlToLeft).Column

 

 第AB列数值总和：

 Dim xRng As Range
    Set xRng = Range("AB1:AB" & lngTotal)
    Sums = WorksheetFunction.Subtotal(9, xRng)

禁用弹出框： Application.DisplayAlerts = False

程序结尾要释放： Application.DisplayAlerts = True

禁用刷新：Application.ScreenUpdating = False

 

打开文件：Set Wb1 = Application.Workbooks.Open(sourcefile, False)

Set w1 = Workbooks.Open(source1)

 

拷贝文件：FileCopy Terget_file1, Terget_file

 

判断A1是合并单元格 If ThisWorkbook.Sheets("sheet1").Cells(1, 1).MergeCells Then
取合并单元格行数:   merge_rows = ThisWorkbook.Sheets("sheet1").Cells(1, 1).MergeArea.Rows.Count
先取消合并: ThisWorkbook.Sheets("sheet1").Cells(1, 1).CurrentRegion.UnMerge
在原表头前插入一行:ThisWorkbook.Sheets("sheet1").Rows(1).Insert Shift:=xlDown
取单元格列数:Rollout_Plan_cl_count = ThisWorkbook.Sheets("sheet1").Cells(1, 1).CurrentRegion.Columns.Count

WorksheetFunction.CountA (Range("A:A"))

WorksheetFunction.Subtotal(9, xRng)

1 、1 AVERAGE（算术平均值）

2、 2 COUNT（数值个数）

3、 3 COUNTA（非真空单元格数量）

4 、4 MAX（最大值）

5 、5 MIN（最小值）

6 、6 PRODUCT（括号内所有数据的乘积）

7 、7 STDEV（估算样本的标准偏差）

8 、8 STDEVP（返回整个样本总体的标准偏差）

9、 9 SUM（求和）

10 、10 VAR（计算基于给定样本的方差）

11 、11 VARP（计算基于整个样本总体的方差）

 

带属性粘贴， Rang("A1").CurrentRegion.Copy

With sheets("Sheets2").Range("A1")

.PasteSpecial xlPasteColumnWiths

.PasteSpecial xlPasteAll

End With

 

粘贴后加减乘除：xlPasteSpecialOperationNone,xlPasteSpecialOperationAdd,xlPasteSpecialOperationSubtract,xlPasteSpecialOperationMultiply,xlPasteSpecialOperationDivide

 

排序：sort(key1,order1)

Range("A1").CurrentRegion.Sort  key1:="mingzi", Order1:=xlDescending,Header:=xlYes

Range("A4：A10").Sort  key1:="mingzi", Order1:=xlDescending,Header:=xlYes

透视图刷新：透视图中 active 时间 ActiveWorkbook.RefreshALL

第几周（周日开始）：Excel.Application.WorksheetFunction.WeekNum(Now() - 1)

第几周（周一开始）：Excel.Application.WorksheetFunction.WeekNum(Now() )