在Excel中，我們經(jīng)常使用各種各樣的公式來進(jìn)行數(shù)據(jù)的計算分析和處理，在VBA中也不例外。本文將介紹VBA中使用公式的相關(guān)屬性。

為了更好地使用公式，讓我們先看看Excel中的A1引用樣式和R1C1引用樣式，再來介紹VBA中與使用公式相關(guān)的屬性。

理解A1引用樣式和R1C1引用樣式

Excel默認(rèn)設(shè)置為A1樣式引用，也就是我們熟悉的行以數(shù)字表示，列以字母表示。例如，A1表示工作表或者單元格區(qū)域左上角的單元格。

Excel還可以設(shè)置為R1C1樣式引用，行列都以數(shù)字表示。例如，使用R1C1表示工作表左上角的單元格。此時，需要調(diào)出“Excel選項”對話框，勾選“R1C1引用樣式”。

在下面的工作表中，我們使用了計算公式。其中，

• 列E = 列C * 列D

• 列F = 列E * 單元格I2

• 列G = 列E – 列F

• 單元格G8 = 單元格區(qū)域G2:G7之和

我們就以這張工作表來分析理解A1樣式和R1樣式。

使用A1樣式公式如下圖：

通常，我們在單元格中輸入公式后，往下拉即可復(fù)制公式。例如，在單元格E2中輸入公式=C2*D2，下拉至單元格E7，Excel會自動調(diào)整單元格相對引用并快速得出列E中的結(jié)果，列F、列G中也類似。但列F中的單元格I2不變，因為我們使用了絕對引用，即在行或列前加了$符號。

使用R1C1樣式公式如下圖：

可以看出，第5列、第6列、第7列中每列的公式都是相同的。也就是說，整個區(qū)域都可以使用同樣的公式。

R1C1樣式中，字母R代表行，字母C代表列，字母后面的方括號代表相對引用。字母R后面的方括號中的數(shù)字代表相對于當(dāng)前單元格移動的行數(shù)，負(fù)數(shù)表示向上移動，正數(shù)表示向下移動。字母C后面的方括號中的數(shù)字代表相對于當(dāng)前單元格移動的列數(shù)，負(fù)數(shù)表示向左移動，正數(shù)表示向右移動。例如，如果當(dāng)前單元格D5中輸入=R[1]C[-1]，則引用的單元格為C6。

若字母R或C后面就是數(shù)字，沒有方括號，則表示引用為絕對單元格。例如，=R3C2引用單元格B3，不管當(dāng)前單元格處在哪里。

若字母R或C后面既沒有方括號也沒有數(shù)字，則表示引用與當(dāng)前單元格相同的行或列。

為了更好地理解，下圖演示了一些A1樣式和R1C1樣式的相對引用和絕對引用的例子。

在VBA中，Range對象的Formula屬性和FormulaR1C1屬性可以讓我們分別使用A1樣式和R1C1樣式的公式。

認(rèn)識Formula屬性

我們通過一些簡單的示例來認(rèn)識Range對象的Formula屬性。

例如，對于下面的工作表，要求在單元格C1中放置單元格區(qū)域A1:A5中的數(shù)值之和。代碼如下：

結(jié)果如下圖所示。從編輯欄可以看出，VBA代碼在單元格C1中放置了公式：=SUM($A$1:$A$5)，該公式執(zhí)行相應(yīng)的求和并得到結(jié)果。

如果要求在單元格區(qū)域C1:C5中的每個單元格中都放置單元格區(qū)域A1:A5中的數(shù)值之和，那么只需要將上面的代碼中的引用區(qū)域Range(“C1”)修改為Range(“C1:C5”)。代碼如下：

結(jié)果如圖下圖所示：

認(rèn)識FormulaR1C1屬性

在上面的示例中，使用FormulaR1C1屬性，也能達(dá)到相同的效果。例如，代碼：

在單元格D1中放置對單元格區(qū)域A1:A5中的數(shù)值求和的結(jié)果，如下圖所示。

我們注意到，在單元格D1中顯示的公式與前面使用Formula屬性顯示的公式相同，這是為什么呢？因為Excel默認(rèn)設(shè)置為A1引用樣式。

但是在錄制宏時，宏錄制器會以R1C1樣式來錄制輸入的公式。例如，對于上面的工作表，當(dāng)前單元格為C1，并在該單元格中輸入公式=SUM($A$1:$A$5)求單元格區(qū)域A1:A5中的數(shù)值之和。我們使用宏錄制器錄制上述操作，代碼如下：

關(guān)于Formula屬性和FormulaR1C1屬性的官方說明

兩個屬性都返回或者設(shè)置對象的公式，其中Formula屬性表示使用A1引用樣式表示法，FormulaR1C1屬性表示使用R1C1樣式引用。

說明：

• 如果單元格中是常量，則返回該常量。

• 如果單元格為空，則返回空字符串。

• 如果單元格中是公式，那么Formula屬性以字符串形式返回該公式，該字符串與公式欄中顯示的格式相同（包括等號）。

• 如果設(shè)置單元格中的值或公式為日期格式，Excel將驗證單元格是否已經(jīng)格式化為日期或者時間數(shù)字格式。如果不是，Excel將修改數(shù)字格式為默認(rèn)的短日期數(shù)字格式。

• 如果單元格區(qū)域是一維

• 或二維單元格區(qū)域，那么設(shè)置該公式為相同維度的VB數(shù)組。類似地，可以將公式放置到VBA數(shù)組中。

• 設(shè)置多單元格區(qū)域的公式，使用該公式填充該區(qū)域內(nèi)的所有單元格。

為什么要在VBA中使用R1C1樣式的公式

至少有以下三個原因：

• 使用R1C1樣式的公式，代碼更簡潔有效。

• R1C1樣式的公式更穩(wěn)定，通用性強，不會因為單元格的變化而修改公式。

• 在VBA中的數(shù)組公式需要使用R1C1樣式的公式。

下面舉例說明。

在本文開頭的工作表中，我們使用Excel公式計算相應(yīng)的數(shù)據(jù)?，F(xiàn)在，我們理解了A1引用樣式和R1C1引用樣式，以及Formula屬性和FormulaR1C1屬性后，使用VBA程序來計算表中的數(shù)據(jù)（見下圖中紅色陰影部分）。

使用A1樣式的公式，程序代碼如下：

使用R1C1樣式的公式，程序代碼如下：

下面是兩段程序的執(zhí)行過程視頻：

可以看出，在某一區(qū)域中使用R1C1樣式的公式，只需一個公式，即可對其進(jìn)行全部計算，非常靈活，代碼也更簡潔。

再看下面的例子，我們需要在第11行計算合計分。

使用R1C1樣式的公式，只需一句代碼即可完成：

如果您對本文介紹的內(nèi)容還有什么好的示例，歡迎發(fā)送郵件給我：xhdsxfjy@163.com

也可以在本文下方留言，提出您的看法或建議。

本文屬原創(chuàng)文章，轉(zhuǎn)載請聯(lián)系我或者注明出處。