我們平時在工作表單元格的公式中常常使用函式，Excel自帶的常用的函式多達300多個，功能強大，豐富多彩，但是在VBA中不能直接應用，必須在函式名前面加上物件，比如：Application.WorksheetFunction.Sum(arg1,arg2,arg3)。

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

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 等文件裡的資料，更加高效和通用，程式設計體驗也更加好。