Excel VBA常用代码总结1

Excel VBA常用代码总结1

改变背景色

Range("A1").Interior.ColorIndex = xlNone ColorIndex一览

改变文字颜色

Range("A1").Font.ColorIndex = 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")

NewSheet.Select

选中或激活某单元格

'“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.Path'路徑

'名稱

ActiveWorkbook.FullName'路徑＋名稱

'或将ActiveWorkbook换成thisworkbook

隐藏文档

Application.Visible = False

禁止屏幕更新

Application.ScreenUpdating = False

禁止显示提示和警告消息

Application.DisplayAlerts = False

文件夹做成

strPath = "C:\temp\"

MkDir strPath

状态栏文字表示

Application.StatusBar = "计算中"

双击单元格内容变换

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

If(Target.Cells.Row >= 5And Target.Cells.Row <= 8) Then

If Target.Cells.Value = "●"Then

Target.Cells.Value = ""

Else

Target.Cells.Value = "●"

End If

Cancel = True

End If

End Sub

文件夹选择框方法1

Set objShell = CreateObject("Shell.Application")

Set objFolder = objShell.BrowseForFolder(0, "文件", 0, 0)

If Not objFolder Is Nothing

Then path= objFolder.self.Path & "\"

end if

Set objFolder = Nothing

Set objShell = Nothing

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

Public Function ChooseFolder() As String

Dim dlgOpen As FileDialog

Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)

With dlgOpen

.InitialFileName = ThisWorkbook.path & "\"

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 = Application.FileDialog(msoFileDialogFilePicker) With dlgOpen

.Title = TitleStr

.Filters.Clear

.Filters.Add TypesDec, Exten

.AllowMultiSelect = False

.InitialFileName = ThisWorkbook.Path

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 CurrentCell.Value <> "end"

……

Set CurrentCell = CurrentCell.Offset(1, 0)

Loop

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

i = StartRow

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