excelvba常用代码总结

Excel VBA常用代码总结1

改变背景色

Range("A1"). = xlNone

ColorIndex一览

改变文字颜色

Range("A1"). = 1

获取单元格

Cells(1, 2)

Range("H7")

获取范围

Range(Cells(2, 3), Cells(4, 5))

Range("a1:c3")

'用快捷记号引用单元格

Worksheets("Sheet1").[A1:B5]

选中某sheet

Set NewSheet = Sheets("sheet1")

选中或激活某单元格

'“Range”对象的的Select方法可以选择一个或多个单元格，而Activate 方法可以指定某一个单元格为活动单元格。

'下面的代码首先选择A1:E10区域，同时激活D4单元格：

Range("a1:e10").Select

Range("d4:e5").Activate

'而对于下面的代码：

Range("a1:e10").Select

Range("f11:g15").Activate

'由于区域A1:E10和F11:G15没有公共区域，将最终选择F11:G15，并激活F11单元格。

获得文档的路径和文件名

'路徑

'名稱

'路徑＋名稱

'或将ActiveWorkbook换成thisworkbook

隐藏文档

= False

禁止屏幕更新

= False

禁止显示提示和警告消息

= False

文件夹做成

strPath = "C:\temp\"

MkDir strPath

状态栏文字表示

= "计算中"

双击单元格内容变换

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If >= 5And <= 8) Then

If = "●"Then

= ""

Else

= "●"

End If

Cancel = True

End If

End Sub

文件夹选择框方法1

Set objShell = CreateObject("")

Set objFolder = (0, "文件", 0, 0)

If Not objFolder Is Nothing

Then path= & "\"

end if

Set objFolder = Nothing

Set objShell = Nothing

文件夹选择框方法2（推荐）

Public Function ChooseFolder() As String

Dim dlgOpen As FileDialog

Set dlgOpen = (msoFileDialogFolderPicker)

With dlgOpen

.InitialFileName = & "\"

If .Show = -1Then

ChooseFolder = .SelectedItems(1)

End If

End With

Set dlgOpen = Nothing

End Function

'使用方法例：

Dim path As String

path = ChooseFolder()

If path <> ""Then

MsgBox"open folder"

End If

文件选择框方法

Public Function ChooseOneFile(Optional TitleStr As String = "Please choose a file", Optional TypesDec As String = "*.*", Optional Exten As String = "*.*") As String

Dim dlgOpen As FileDialog

Set dlgOpen = (msoFileDialogFilePicker)

With dlgOpen

.Title = TitleStr

.

. TypesDec, Exten

.AllowMultiSelect = False

.InitialFileName =

If .Show = -1Then

' .AllowMultiSelect = True

' For Each vrtSelectedItem In .SelectedItems

' MsgBox "Path name: " & vrtSelectedItem

' Next vrtSelectedItem

ChooseOneFile = .SelectedItems(1)

End If

End With

Set dlgOpen = Nothing

End Function

某列到关键字为止循环方法1(假设关键字是end)

Set CurrentCell = Range("A1")

Do While <> "end"

……

Set CurrentCell = (1, 0)

Loop

某列到关键字为止循环方法2(假设关键字是空字符串)

i = StartRow

Do While Cells(i, 1) <> ""

……

i = i + 1

Loop

"For Each...Next 循环(知道确切边界)

For Each c In Worksheets("Sheet1").Range("A1:D10").Cells If Abs < Then = 0

Next

"For Each...Next 循环(不知道确切边界),在活动单元格周围的区域内循环For Each c In If Abs < Then = 0

Next

某列有数据的最末行的行数的取得(中间不能有空行)

lonRow=1

Do While Trim(Cells(lonRow, ２).Value) <> ""

lonRow = lonRow + 1

Loop

lonRow11 = lonRow11 - 1

A列有数据的最末行的行数的取得另一种方法

Range("Ａ65536").End(xlUp).Row

将文字复制到剪贴板

Dim MyData As DataObject

Set MyData = New DataObject

Range("H7").Value

取得路径中的文件名

Private Function GetFileName(ByVal s As String)

Dim sname() As String

sname = Split(s, "\")

GetFileName = sname(UBound(sname))

End Function