Excel VBA常用技巧代碼解析

excel379 2014-03-04

展開(kāi)全文

技巧13       單元格中的公式
13-1       在單元格中寫(xiě)入公式
   使用Range對(duì)象的Formula屬性可以在單元格區(qū)域中寫(xiě)入公式，如下面的代碼所示。

#001 Sub rngFormula()
#002 Sheet1.Range("C1:C10").Formula = "=SUM(A1+B1)"
#003 End Sub

復(fù)制代碼

代碼解析：
   應(yīng)用于Range對(duì)象的Formula屬性返回或設(shè)置A1樣式表示的Range對(duì)象的公式，語(yǔ)法如下：
expression.Formula
   參數(shù)expression是必需的，返回一個(gè)Range對(duì)象。
   還可以使用FormulaR1C1屬性返回或設(shè)置以R1C1-樣式符號(hào)表示的公式，如下面的代碼所示。

#001 Sub rngFormulaRC()
#002 Sheet2.Range("C1:C10").FormulaR1C1 = "=SUM(RC[-2]+RC[-1])"
#003 End Sub

復(fù)制代碼

如果需要在單元格中寫(xiě)入數(shù)組公式則使用Range對(duì)象的FormulaArray屬性。如下面的代碼所示。

#001 Sub RngFormulaArray()
#002 Sheet3.Range("C1").FormulaArray = "=A1:A2*B1:B2"
#003 End Sub

復(fù)制代碼

Range對(duì)象的FormulaArray屬性返回或設(shè)置單元格區(qū)域的數(shù)組公式。
13-2 檢查單元格是否含有公式
使用單元格的HasFormula屬性檢查單元格是否含有公式,如下面的代碼所示。

#001 Private Sub CommandButton1_Click()
#002 Select Case Selection.HasFormula
#003 Case True
#004 MsgBox "公式單元格!"
#005 Case False
#006 MsgBox "非公式單元格!"
#007 Case Else
#008 MsgBox "公式區(qū)域：" & Selection.SpecialCells(xlCellTypeFormulas, 23).Address(0, 0)
#009 End Select
#010 End Sub

復(fù)制代碼

代碼解析：
   工作表中按鈕的單擊過(guò)程，檢查所選擇的單元格區(qū)域是否含有公式。
   第2行代碼返回所選擇單元格區(qū)域的HasFormula屬性值。如果區(qū)域中所有單元格均包含公式，則該值為T(mén)rue；如果所有單元格均不包含公式，則該值為False。
   第3、4行代碼，如果返回True，說(shuō)明區(qū)域中所有單元格均包含公式。
   第5、6行代碼，如果返回False，說(shuō)明區(qū)域中所有單元格均不包含公式。
   第7、8行代碼，如果是混合區(qū)域，則顯示包含公式的單元格地址。
13-3       判斷單元格公式是否存在錯(cuò)誤
   當(dāng)需要獲取的單元格的值由公式返回時(shí)，公式返回的結(jié)果可能是一個(gè)錯(cuò)誤文本，包含#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A等。此時(shí)，當(dāng)單元格公式返回結(jié)果為錯(cuò)誤文本時(shí)，如果試圖通過(guò)Value屬性來(lái)獲得公式的返回結(jié)果，將得到類(lèi)型不匹配的錯(cuò)誤信息，如圖所示。
VBA常用技巧代碼解析 - ExcelHome - Snap1.jpg

通過(guò)Range對(duì)象的Value屬性的返回結(jié)果是否為錯(cuò)誤類(lèi)型，來(lái)判斷公式是否存在錯(cuò)誤，如下面的代碼所示。

#001 Sub FormulaIsError()
#002 If VBA.IsError(Range("A1").Value) = True Then
#003 MsgBox "A1單元格錯(cuò)誤類(lèi)型為:" & Range("A1").Text
#004 Else
#005 MsgBox "A1單元格公式結(jié)果為" & Range("A1").Value
#006 End If
#007 End Sub

復(fù)制代碼

代碼解析：
FormulaIsError過(guò)程代碼判斷單元格A1中公式結(jié)果是否為錯(cuò)誤，如果為錯(cuò)誤則顯示該錯(cuò)誤類(lèi)型，否則顯示公式的結(jié)果，如圖所示。
VBA常用技巧代碼解析 - ExcelHome - Snap2.jpg

   第2行代碼使用IsError函數(shù)返回Boolean值，指出表達(dá)式是否為一個(gè)錯(cuò)誤值，如果表達(dá)式表示一個(gè)錯(cuò)誤，則IsError函數(shù)返回True，否則返回False。
13-4       取得單元格中公式的引用單元格
   如果需要取得單元格中公式的引用單元格對(duì)象，可以使用Range對(duì)象的Precedents屬性，如下面的代碼所示。

#001 Sub RngPrecedent()
#002 Dim rng As Range
#003 Set rng = Sheet1.Range("C1").Precedents
#004 MsgBox "公式所引用的單元格有：" & rng.Address
#005 Set rng = Nothing
#006 End Sub

復(fù)制代碼

代碼解析：
   在工作表的C1單元格中寫(xiě)有公式“SUM(“A1:B1”)”，RngPrecedent過(guò)程使用Range對(duì)象的Precedents屬性取得其引用的單元格A1:B1。
   Precedents屬性返回一個(gè)Range對(duì)象，該對(duì)象代表單元格的所有引用單元格。如果有若干引用單元格，那么該區(qū)域可能是多個(gè)的選定區(qū)域（Range 對(duì)象的聯(lián)合）。
   運(yùn)行RngPrecedent過(guò)程結(jié)果如圖所示。
VBA常用技巧代碼解析 - ExcelHome - Snap3.jpg

13-5       將單元格中的公式轉(zhuǎn)換為數(shù)值
   工作表中如果存在過(guò)多的公式將影響操作速度，將單元格中的函數(shù)與公式的結(jié)果轉(zhuǎn)換為數(shù)值，可以提高工作表運(yùn)算效率，有下面幾種方法可以實(shí)現(xiàn)。
   使用選擇性粘貼的方法可以將函數(shù)與公式的結(jié)果轉(zhuǎn)換為數(shù)值，如下面的代碼所示。

#001 Sub SpecialPaste()
#002 With Range("A1:A10")
#003 .Copy
#004 .PasteSpecial Paste:=xlPasteValues
#005 End With
#006 Application.CutCopyMode = False
#007 End Sub

復(fù)制代碼

代碼解析：
   SpecialPaste過(guò)程使用選擇性粘貼方法將單元格區(qū)域的公式轉(zhuǎn)換為數(shù)值。
   第3行代碼將單元格區(qū)域復(fù)制到剪貼板中。
   應(yīng)用于Range對(duì)象的Copy方法將單元格區(qū)域復(fù)制到指定的區(qū)域或剪貼板中，語(yǔ)法如下：
expression.Copy(Destination)參數(shù)expression是必需的，該表達(dá)式返回一個(gè)Range對(duì)象。
   參數(shù)Destination是可選的，指定區(qū)域要復(fù)制到的目標(biāo)區(qū)域。如果省略該參數(shù)，Microsoft Excel 將把該區(qū)域復(fù)制到剪貼板中。
   第4行代碼將剪貼板中的Range對(duì)象僅復(fù)制值到單元格區(qū)域中。
   應(yīng)用于Range對(duì)象的PasteSpecial方法將剪貼板中的Range對(duì)象粘貼到指定區(qū)域中，語(yǔ)法如下：
expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
   參數(shù)expression是必需的，該表達(dá)式返回一個(gè)Range對(duì)象。
   參數(shù)Paste是可選的，指定要粘貼的區(qū)域部分。在本例中設(shè)置為xlPasteValues，僅復(fù)制值到單元格區(qū)域中。
   使用Value屬性可以將函數(shù)與公式的結(jié)果轉(zhuǎn)換為數(shù)值，如下面的代碼所示。

#001 Sub UseValue()
#002 Range("A1:A10").Value = Range("A1:A10").Value
#003 End Sub

復(fù)制代碼

代碼解析：
UseValue過(guò)程使用Value屬性將函數(shù)與公式的結(jié)果轉(zhuǎn)換為數(shù)值。
使用Formula屬性可以將函數(shù)與公式的結(jié)果轉(zhuǎn)換為數(shù)值，如下面的代碼所示。

#001 Sub UseFormula()
#002 Range("A1").Formula = Range("A1").Value
#003 End Sub

復(fù)制代碼

代碼解析：
UseFormula過(guò)程Formula屬性將函數(shù)與公式的結(jié)果轉(zhuǎn)換為數(shù)值。當(dāng)Formula屬性值為非公式時(shí)，返回的結(jié)果與Value屬性一致。

[ 本帖最后由 yuanzhuping 于 2009-2-16 23:06 編輯 ]

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶(hù)發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自： excel379 > 《excel服務(wù)器文章》

舉報(bào)/認(rèn)領(lǐng)