vba常用代碼有哪些？

我以前總結過我常用的一些，見

https://github.com/mzlogin/learn-vba-the-hard-way

主要的實用代碼片段如下：

## 實用代碼片段

### 文件操作

#### 引用打開的工作簿

使用索引號（從 1 開始）

```vbnet

Workbooks(1)

```

使用工作簿名稱

```vbnet

Workbooks("1.xlsx")

```

#### 創建一個 EXCEL 工作簿對象

```vbnet

Dim wd As Excel.Application

Dim wb As Workbook

Set wd = CreateObject("excel.application")

wd.Visible = True

Set wb = wd.Workbooks.Open(ThisWorkbook.Path & "/test.xls")

' ...

wb.Close

wd.Quit

```

#### 打開/保存/關閉工作簿

```vbnet

Dim wb As Workbook

wb = Workbooks.Open(ThisWorkbook.Path & "/test.xls")

' ...

wb.Save

wb.Close

```

關閉所有工作簿

```vbnet

Workbooks.close

```

另存為（自動打開新文件關閉源文件）

```vbnet

ThisWorkbook.SaveAs FileName:="D:\1.xls"

```

另存為（保留源文件不打開新文件）

```vbnet

ThisWorkbook.SaveCopyAs FileName:="D:\1.xls"

```

#### 拷貝文件

```vbnet

oldfile = ThisWorkBook.Path & "/old.xlsx"

newfile = ThisWorkBook.Path & "/new.xlsx"

FileCopy oldfile, newfile

```

#### 拷貝文件夾

```vbnet

Set fso = CreateObject("Scripting.FileSystemObject")

fso.copyfolder srcDir, dstDir

```

#### 刪除文件夾下的所有文件

```vbnet

base = ThisWorkBook.Path & "/文件夾/"

pattern = base & "*.*"

file = Dir(pattern, vbReadOnly)

While file <> ""

Kill base & file

file = Dir

Wend

```

#### 創建文件夾

```vbnet

MkDir(directory)

```

#### 判斷文件夾是否存在

以下為不存在即創建

```vbnet

If Dir(outputDir, 16) = Empty Then

MkDir (outputDir)

End If

```

#### 判斷文件是否存在

方法 1：

```vbnet

Dim fileSystemObject As Object

Set fileSystemObject = CreateObject("Scripting.FileSystemObject")

If fileSystemObject.FileExists(<filepath>) = True Then

MsgBox "文件存在"

End If

```

方法 2：

```vbnet

Dim file As String

file = Dir("E:\MyPictures\Pic\logo.gif")

If file <> "" Then

MsgBox "文件存在"

Endif

```

### 格式操作

#### 設置邊框與自動篩選

```vbnet

Set Rng = MyWorkSheet.UsedRange

With Rng

.Borders.LineStyle = xlContinuous

.Borders.Weight = xlThin

.AutoFilter

End With

```

#### 獲取或者設置單元格背景色

```vbnet

MyWorkSheet.Cells(i, j).Interior.ColorIndex

```

#### 讓某表格選中的單元格變成指定顏色

在 thisworkbook 中添加如下代碼段：

```vbnet

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If ActiveSheet.Name = "yoursheet" Then

ActiveSheet.UsedRange.Interior.ColorIndex = 0

Target.Interior.ColorIndex = 6

End If

End Sub

```

#### 在單元格里回車 / 換行

設置單元格 Value 里使用 `Chr(10)` 和 `Chr(13)`，分別表示回車、換行。

#### 隱藏行

```vbnet

MyWorkSheet.Rows(i).Hidden = True

```

#### 單元格內容為純文本

```vbnet

sheet.Cells(m, n).NumberFormatLocal = "@"

```

#### 設置單元格公式

```vbnet

For Each cel In ActiveSheet.Range("C1:C10")

cel.Formula = "=SUBSTITUTE(A" & cel.Row() & ", ""."", CHAR(10) & ""."")"

Next

```

### 選擇

#### 引用單元格 / 區域

```vbnet

Range("A1") '表示 A1 單元格

Range("A2:D1") '表示 A2 到 D1 區域

Range("A2:D1")(3) '表示該區域里的第三個單元格

Range("D" & i) 'i 為變量

Range("D3:F4,G10") '引用多個區域

Range("2:2") '引用第二行

Range("2:12") '引用第二行到第十二行

Range("D:A") '引用第 A 到 D 列

Rows(2) '引用第二行

Rows("2:4") '引用第二到四行

Columns("B")

Columns("B:D")

Range(Clee1, Cell2) '左上與右下

Range(Range1, Range2) '取最大范圍

```

#### 選中單元格 / 區域

```vbnet

Range("1:1").Select '選中第一行

```

#### 獲取當前選中區域

```vbnet

MyWorkSheet.Application.Selection

```

### 數據結構

#### Dictionary

```vbnet

Dim dict

Set dict = CreateObject("Scripting.Dictionary")

' 新增，各種類型都可以，包括 Dictionary

dict.Add "hello", "world"

' 數量

dict.Count

' 刪除

dict.Remove("hello")

' 判斷是否存在

dict.exists("hello")

' 取值，需要先判斷存在再取

dict.Item("hello")

' 修改、新增

dict.Item("hello") = "world"

' 循環

k = dict.Keys

v = dict.Items

For i = 0 to dict.count - 1

key = k(i)

value = v(i)

Next

' 清空

dict.RemoveAll

```

參考：[Excel vba map/dictionary](http://www.cnblogs.com/zhjh256/p/6428333.html)

### 語言基礎

#### String to Integer、Double

```vbnet

CInt(MyWorkSheet.Cells(1,7))

CDbl(MyWorkSheet.Cells(1,7))

```

#### 字符串分割/獲取數組長度

```vbnet

Dim arr() As String

arr() = Split(ws.Cells(a, b).Value, "-")

alen = UBound(arr) - LBound(arr) + 1

```

#### 字符串替換

```vbnet

s1 = Replace(s2, ".", Chr(10) & ".")

```

#### 判斷單元格是否為空

判斷單元格的 value 是否為 ""。

#### 退出

主要使用 Exit 表達式。

```

Exit { Do | For | Function | Property | Select | Sub | Try | While }

```

參見 [Exit Statement (Visual Basic)](

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/exit-statement)

進入

https://github.com/mzlogin/learn-vba-the-hard-way

另外，我的一個建議是樓主也可以關注下用 Python 代替 VBA 處理 Excel 等文檔里的數據，更加高效和通用，編程體驗也更加好。