Msgbox activecell.address
Worksheets(“sheet1”).range(“a1:d13”).calculate
Worksheets(“sheet1”).range(“a1:d13”).cells.count
Worksheets(“sheet1”).range(“a1:d13”).cells.checkspelling
Worksheets(“sheet1”).range(“a1:d13”).clear
Worksheets(“sheet1”).range(“a1:d13”).clearcomments
Worksheets(“sheet1”).range(“a1:d13”).clearcontents
Msgbox worksheets(“sheet2”).range(“b3:d12”).column
Msgbox worksheets(“sheet2”).range(“b3:d12”).row
Dim i As Long, j As Long Dim rng As rang
Set rng = ActiveSheet.Range(\With rng
For i = 1 To .Columns.Count For j = 1 To .Rows.Count .Cells(j, i).Value j & \ Next j Next i End With
Worksheets(“sheets1”).activate Activecell.currentregion.select
Range(“b4”).end(xlup).select
Range(“b4”).end(xltoright).select
Range(“b4”).end(xlup).select
Worksheets(\
Range(\
Worksheets(“sheets1”). Range(“f19:g20”).copy
Worksheets(“sheets1”). Range(“f19:g20”).pastespecial
Worksheets(“sheets1”). Range(“f19:g20”).pastespecial type=xlpastevalues
Worksheets(“sheets1”).activate
Activecell.offset(rowoffset:=3,columnoffset:=3).activate
Expression.pastespecial(paste,operation,skipblanks,transpose)
Worksheets(“sheets2”).range(“f19:g20”).select
Vdate=activesheet.range(“a1:b10”).value activesheet.range(“a1:b10”).value =Vdate
EXCEL VBA 应用开发专题篇 表格处理
基于过程方法的数据处理 基于面向对象方法的数据处理 操作数据文件
Workbooks.Open \
ActiveWorkbook.RunAutoMacros xlAutoOpen
Workbooks.OpenText filenmae:=\
Sub useopenxml()
Application.Workbooks.OpenXML Filename:=\End Sub
Sub useopendatebase()
Workbooks.OpenDatabase \End Sub
Workbook.save
Activeworkbook.save
For each w in application.workbooks w.save next w
application .quit
workbooks(“book1.xls”).close savechanges:=false 使用VBA语句操作文件 Public Function getattrdescr() Dim result As String, attr As Long attr = GetAttr(Filename)
If attr And vbDirectory Then result = result & \If attr And vbredonly Then result = result & \If attr And vbHidden Then result = result & \If attr And vbSystem Then result = result & \If attr And vbArchive Then result = result & \getattrdescr = Mid(result, 2) End Function
Debug.Print FileLen(\
Debug.Print FileDateTime(\
Public Sub 测试() Debug.Print CurDir Debug.Print CurDir(\End Sub
处理文本文件
Open filename for input 文件输入，读取 Open filename for output文件输出，写入
Open filename for appending 添加内容到文件末尾
Public Sub 文件操作() Dim i, filename For i = 1 To 3
filename = \
Open filename For Output As #i Print #i, \Next i Close End Sub
Line input #filename,varname
Public Sub 插入行() Dim textline
Open \Do While Not EOF(1) Line Input #1, textline Debug.Print textline Loop Close #1 End Sub
Input #filenumber,varlist
Open \Print #1, \Print #1,
Print #1, \Print #1, \
Print #1, Spc(5); \Print #1, Tab(10); \
Dim mybool, mydate, mynull, myerror
mybool = False: mydate = #2/12/1969#: mynull = Null myerror = CVErr(32767)
Print #1, mybool; \Print #1, mydate; \Print #1, mynull; \Print #1, myerror; \Close #1
Open \write #1, \write #1,
write #1, \write #1, \
write #1, Spc(5); \write #1, Tab(10); \
Dim mybool, mydate, mynull, myerror
mybool = False: mydate = #2/12/1969#: mynull = Null myerror = CVErr(32767)
write #1, mybool; \write #1, mydate; \write #1, mynull; \write #1, myerror; \Close #1
Application.worksheetfunction.linest(y,x,,false) 应用实例 格式化数据 RS234类型
Public Function getnumber(cell As Range) Application.Volatile Dim temp As Variant Dim t As String Dim n As Long t = \
temp = cell.Value
For n = 1 To Len(temp)
If IsNumeric(Mid(temp, n, 1)) Then t = t & Mid(temp, n, 1) End If Next n
getnumber = Val(t) End Function
RS23.34情况
Public Function getnumberplusa(cell As Range) Application.Volatile Dim temp As Variant Dim t As String Dim n As Long t = \
temp = cell.Value
For n = 1 To Len(temp)
If IsNumeric(Mid(temp, n, 1)) Then Exit For End If Next n
t = Mid(temp, n)
getnumberplusa = Val(t) End Function
RS23.34AS类型
Public Function getnumberplusB() Application.Volatile Dim temp As Variant Dim t As String
Dim n As Long, s As Long, e As Long t = \
temp = cell.Value
For n = 1 To Len(temp)
If IsNumeric(Mid(temp, n, 1)) Then s = n Exit For End If Next n
For n = Len(temp) To 1 Step -1
If IsNumeric(Mid(temp, n, 1)) Then e = n