在 Excel 2016 中使用返回數組的 VBA 函數

數組公式是 Excel 最強大的功能之一。如果您熟悉數組公式，您會很高興知道您可以創建返回數組的 VBA 函數。

返回月份名稱數組

讓我們從一個簡單的例子開始。MonthNames 函數返回一個由 12 個元素組成的數組——你猜對了——月份名稱。

函數 MonthNames()
  MonthNames = Array(“一月”、“二月”、“三月”、_
   “四月”、“五月”、“六月”、“七月”、“八月”、_
   “九月”、“十月”、“十一月”、“十二月”）
結束函數

要在工作表中使用 MonthNames 函數，您必須將其作為 12 個單元格的數組公式輸入。例如，選擇範圍 A2:L2 並輸入=MonthNames()。然後按 Ctrl+Shift+Enter 在所有 12 個選定單元格中輸入數組公式。檢查結果。

在 Excel 2016 中使用返回數組的 VBA 函數

使用 MonthNames 函數返回一個 12 元素數組。

如果您希望月份名稱顯示在一列中，請選擇一列中的 12 個單元格並使用此數組公式。（不要忘記按 Ctrl+Shift+Enter 輸入它。）

=TRANSPOSE(MonthNames())

您還可以從數組中選擇一個月。這是一個公式（不是數組公式），它顯示數組的第四個元素：April。

=INDEX(MonthNames(),4)

返回一個排序列表

假設您有一個要在另一個單元格區域中按排序順序顯示的名稱列表。有一個工作表功能為您做這件事不是很好嗎？

這個自定義函數就是這樣做的：它將單列單元格範圍作為其參數，然後返回一個由這些單元格排序的數組。範圍 A2:A13 包含一些名稱。範圍 C2:C13 包含此多單元格數組公式。（請記住，您必須按 Ctrl+Shift+Enter 輸入公式。）

在 Excel 2016 中使用返回數組的 VBA 函數

使用自定義函數返回排序範圍。

=排序（A2：A13）

這是 Sorted 函數的代碼：

函數排序（Rng As Range）
  Dim SortedData() 作為變體
  將單元格調暗為範圍
  Dim Temp As Variant, i As Long, j As Long
  Dim NonEmpty As Long
' 將數據傳輸到 SortedData
  對於 Rng 中的每個單元格
    如果不是 IsEmpty(Cell) 那麼
      非空 = 非空 + 1
      ReDim 保留已排序數據（1 到非空）
      SortedData(NonEmpty) = Cell.Value
    萬一
  下一個單元格
'對數組進行排序
  對於 i = 1 到非空
    對於 j = i + 1 到非空
      如果 SortedData(i) > SortedData(j) 那麼
        溫度 = SortedData(j)
        SortedData(j) = SortedData(i)
        SortedData(i) = 溫度
      萬一
    下一個
  接下來我
'轉置數組並返回它
  排序 = Application.Transpose(SortedData)
結束函數