為什么使用數組？

1. 縮減工作薄文件大小，提高運行效率

一般而言只是使用 Excel 的內置工作表函數，在運算方面還是很高效的，但有時因為一個單元格牽扯的計算太多，比如調用多單元格數據，對結果文本進行部分替換，按照優先級和條件判斷來確定不同的返回結果，這都會造成一個單元格的公式文本過長，當以此單元格為基礎進行數千行的相對引用填充時，必然會導入工作薄快速的膨脹；

如果因為某些復雜的處理，而使用了自定義函數，且應用的單元格也是幾千行級別的，那么用戶甚至會被迫選擇在 "公式選項卡->計算選項"，將自動計算變更為手動計算的方式來避免這種 Excel 自動進行全局計算的引發卡頓問題；

使用數組避免這一問題的方式就是將計算在內存完成，單元格只寫入一個數值或字符串；

2. 數組的運算效率遠高于讀寫單元格

看個實例來體會一下單元格逐個處理，和數組內存處理后統一導入的效率差異；

插入代碼塊的高亮顯示實在不理想，先貼圖吧；

FillRandom 在 A1:D10000 這四萬個單元格中生成隨機數；拷貝隨機數為常數，避免觸發自動計算帶來的開銷；

任務目標：對每個隨機數求平方，結果放置回工作表；

ForEachCellImplement 使用逐個單元格取值，計算后放回的方式實現；

ArrayImplement 使用將 A1:D10000 導入數組，在內存中完成計算，再統一放回工作表的方式實現；

前者耗時 12718 毫秒，后者 47 毫秒；約 270 倍的效率差異；

3. 在某些應用場景下，數組處理問題最簡單

把一行或一列單元格的數據以逗號拼接成字符串，在不使用數組的情況下一般會這樣做；

即便省略 resStr，If 語句整體壓縮在一行，也需要 5 行代碼，其中包含循環結構和條件判斷處理字符串拼接的開頭部分 ；

如果使用數組，只需要一行就可以了；

4. 強化部分 Excel 功能

如，SpecialCells，也就是 Excel 快捷鍵 F5 定位條件，Excel 中可以批量定位一種類型的單元格區域，但對定位結果進行多種條件處理，再將數據放回或者以這些數據為基礎再進行其他數據的生成，Excel 菜單功能是無法完成的；

Find 也就是 Excel 的 Ctrl + F，查找功能，同樣可以利用 VBA 來完成，一旦拿到返回的一組單元格區域，處理方式就靈活多樣了；

整體來看數組可以極大的拓展 “返回值為單元格區域” 的功能的操作邊界；

5. VBA 其他容器使用效果也不理想

比如 Collection（類似 Python 中的 List），Dictionary等，但支持的方法太有限，同時轉換類型也沒有便捷的方法來支持；

這一點我個人理解是 Microsoft 還是希望用戶以 Range 為核心，配合 Excel 本身的功能，以及這些功能對應的 VBA 調用，以此來完成任務目標； 如果用戶的任務目標超越了這個界限用戶就應該去使用 Microsoft 的其他產品如 Power BI，VSTO（Visual Studio Tools for Office）借助 C# 應該是想干什么都可以了；或者干脆極易上手 Python，借助 pandas 和 numpy 這些工具來處理 .xlsx .csv 等等；

坑的原因

VBA 數組的坑主要是由三個原因引起的：

1.腳本語言的用戶對數據類型的重視度不足；

2.Range對象的存在，造成了數組一些潛規則式的轉換機制；可以在下文 “感受 Range 的混亂” 部分體會一下；

3.靜態數組和動態數組的一些限制和數據導入規則；這遠沒有靜態語言數組聲明后全生命周期大小不可變，擴容需要重新創建來的清晰；

感受 Range 的混亂

如果這部分看蒙了就暫時過的它吧，后面看過 “Array() 和 Range 對象”，“數組初始化方式3”，“數組導入到單元格區域”，在回過頭來理解一下這個部分會更有收獲；

1.Range("") 賦值給未聲明的變量，TypeName 變為 Variant；TypeName() 是獲取變量的數據類型；

2.Variant() 不能使用 Range 類型的屬性，如，.Address；

3.想要使用 Range 類型的屬性，先 Set varName = Range("")；

4.對于被 Set 成 Range 類型的變量：

（1）IsArray() 仍然是 True；

（2）不能將 Range 類型的變量賦值給已經聲明的數組，報錯 “類型不匹配”；

（3）可以賦值給未聲明的變量或 Variant 類型的變量（Dim x 或 Dim x as Variant），該 Range 類型的變量會自動轉型為 Variant()；

最讓人懵逼的地方是 Set 一個 Range() 它的 TypeName 是 Range，IsArray 是 True，但不能把它賦值給一個數組，如果把它賦值給一個 未聲明的 或 Variant 變量，它又被自動轉換成了 Variant()，看下面這個例子，注意 TypeName：

---

以下主要從以下幾個方面來談談數組避坑：

數組聲明

Array() 方法 和 Range 對象

數組初始化

數組導入到單元格區域

數組作為參數和返回值

對于腳本語言我個人更傾向，不開啟強制變量聲明；

在不聲明的狀態下，直接對一個變量賦值，某些時候是更好的策略，比如，Filter() 的返回值，如果聲明了 Variant() 去接收則報錯（必須聲明為 String()），For Each 的臨時變量，會強制用戶聲明等等；如果要開啟強制聲明，在模塊最上方加入如下語句：

Option Explicit

數組聲明

靜態數組

在 Option Base 的指定值不同的情況下，不設置編號的靜態數組聲明的大小是不同的；

動態數組

Array() 和 Range 對象

討論初始化之前，先來看兩個給數組賦值的常用形式；

所謂一次性裝入，就是以一個數組或對象為數組賦值，賦值在一條語句中完成，Array() 和 Range 對象都屬于一次裝入；

非一次性裝入，就是利用循環結構逐個調用數組元素并進行賦值；

1. Array() 函數

Array(ParamArray ArgList() As Variant)

注意 Array() 方法的參數和數據類型是 Variant，其函數的返回值是 Variant()；

官方文檔 Variant 數據類型?docs.microsoft.com

Variant 包含除固定長度 String 數據以外的任何類型的數據；也就是說 Array() 的元素可以是任意類型，也可以是數組；

2. Range 對象

先說明一下本文用到的幾個稱呼，它們都是 Range對象 ：

（1）單元格區域，特指工作表中由單元格（Cell）所組成的 Range；

（2）Range 類型，將 Range("A1:C3") 這種形式通過 Set 設置后得到的對象變量；

（3）Range("")，特指賦值給變量，數據類型轉型為 Variant() 的 Range；

（4）Range 對象，指代 （1）-（3）中的情況，需要結合上下文來理解；

數組初始化

幾條規則：

1.靜態數組不能一次性裝入數據；

2.只聲明是數組、大小及編號的數組，系統會分配為 Variant類型數組；

3.只聲明一個變量 (如，Dim varTemp)，默認的數據類型 TypeName(varTemp) 是 Empty，varTemp 可以接收動態或靜態數組，且可以是任意類型的數組；這種聲明方式實際上就相當于在 非 Option Explicit 情況下，不聲明直接使用變量；

4.可以給 Variant數組 的元素賦值任意類型（逐個賦值的方式），但不能將 非Variant數組，賦值給 Variant數組（一次裝入的模式），實際這一條是特指動態數組的，因為靜態數組已經被不能一次裝入的規則限制了；

5.可以用靜態數組為動態數組賦值，但靜態數組只能是 Variant數組；

在 VBE 本地窗格對比一下數組元素的順序 和 For Each 取元素的順序；

推薦使用不聲明的方式，因為部分函數，如 Filter() 的返回值是 String()，Variant 數組使用元素逐個賦值的方式可以接收 String 類型的數據，但 Variant數組 不能接收 String數組 的一次性賦值，如果一定要為數組聲明類型則需要聲明為 Dim arrDyn() as String，這點個人覺得挺坑的；

數組清理

這個用的不多順便提一下吧；

Erase arraylist

arraylist 是一個或多個用逗號分隔開的需要清除的數組變量；只給出數組名，不帶括號和下標；

Erase 釋放動態數組所使用的內存，下次引用該動態數組之前，必須 ReDim

數組導入到單元格區域

數組導入到單元格區域：

如果 數組 的大小超過了單元格區域的大小，則相當于數組被單元格區域截取，如，例1；

如果 單元格區域 的大小超過了數組的大小，則不足的部分以 "#N/A" 來填充，如，例2；

例2，數組的大小是 3行4列，單元格區域的大小是 4行5列，不足的部分被 #N/A填充了；

雖然在數組初始化 方式3 中，提到 Range("") 賦值給數組，即便只有一行，它也是二維數組，但是將數組賦值給單元格區域時，可以使用一維數組；

數組的轉置

前面提到從 Range("") 導入的數組，即是是一行，也是二維數組，即 Range("A1:J1") 是 (1 to 1, 1 to 10) 的二維數組；

(1 to 1, 1 to 10) 形式的數組，也可以通過 Transpose 轉置為一列數組，因此可以利用這個機制將一個 (1 to 1, 1 to 10) 轉變為一個 (1 to 10) 的數組；

這也就文章開頭部分的兩次轉置的原理；在 VBE 本地窗口看一下結構

數組作為參數和返回值

---

結束

數組日常使用能看到的出現概率較高的坑應該是都在這里了，以后想到其他的再補充吧，歡迎交流！

最后在補充一句，在文章開頭的部分測試了四萬個單元格的逐個讀取和逐個寫入，后來我又單獨跑了一下逐個讀取，時間是16毫秒左右，所以可以確定消耗資源的是單元格寫入，在操作中盡量避免對單元格的寫入吧。

總結

以上是生活随笔為你收集整理的access vba 常量数组赋值_聊聊 VBA 数组的那些坑的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。