EXCEL VBA初步(4)

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

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库EXCEL VBA初步(4)在线全文阅读。