ExcelVBA常用技巧第09章1函數(shù)的使用備課講稿

1、Good is good, but better carries it.精益求精，善益求善。ExcelVBA常用技巧第09章1函數(shù)的使用VBA常用技巧目錄TOCo1-4hzuHYPERLINKl_Toc266459881VBA常用技巧PAGEREF_Toc266459881h1HYPERLINKl_Toc266459882第9章函數(shù)的使用PAGEREF_Toc266459882h2HYPERLINKl_Toc266459883技巧1調(diào)用工作表函數(shù)求和PAGEREF_Toc266459883h2HYPERLINKl_Toc266459884技巧2查找最大、最小值PAGEREF_Toc266459

2、884h2HYPERLINKl_Toc266459885技巧3不重復(fù)值的錄入PAGEREF_Toc266459885h4HYPERLINKl_Toc266459886技巧4獲得當(dāng)月的最后一天PAGEREF_Toc266459886h6HYPERLINKl_Toc266459887技巧5四舍五入運算PAGEREF_Toc266459887h7HYPERLINKl_Toc2664598885-1極小值修正法PAGEREF_Toc266459888h7HYPERLINKl_Toc2664598895-2調(diào)用工作表函數(shù)法PAGEREF_Toc266459889h8HYPERLINKl_Toc26645

3、9890技巧6使用字符串函數(shù)PAGEREF_Toc266459890h8HYPERLINKl_Toc266459891技巧7使用日期函數(shù)PAGEREF_Toc266459891h10HYPERLINKl_Toc266459892技巧8判斷是否為數(shù)值PAGEREF_Toc266459892h14HYPERLINKl_Toc266459893技巧9格式化數(shù)值、日期和時間PAGEREF_Toc266459893h15HYPERLINKl_Toc266459894技巧10個人所得稅自定義函數(shù)PAGEREF_Toc266459894h17HYPERLINKl_Toc266459895技巧11人民幣大寫函

4、數(shù)PAGEREF_Toc266459895h19HYPERLINKl_Toc266459896技巧12列號轉(zhuǎn)換為列標(biāo)PAGEREF_Toc266459896h21HYPERLINKl_Toc266459897技巧13判斷工作表是否為空表PAGEREF_Toc266459897h22HYPERLINKl_Toc266459898技巧14查找指定工作表PAGEREF_Toc266459898h23HYPERLINKl_Toc266459899技巧15查找指定工作簿是否打開PAGEREF_Toc266459899h25HYPERLINKl_Toc266459900技巧16取得應(yīng)用程序的安裝路徑PAG

5、EREF_Toc266459900h25HYPERLINKl_Toc266459901技巧17數(shù)組的使用PAGEREF_Toc266459901h27HYPERLINKl_Toc26645990217-1代碼運行時創(chuàng)建數(shù)組PAGEREF_Toc266459902h27HYPERLINKl_Toc26645990317-2文本轉(zhuǎn)換為數(shù)組PAGEREF_Toc266459903h28HYPERLINKl_Toc26645990417-3使用動態(tài)數(shù)組去除重復(fù)值PAGEREF_Toc266459904h30函數(shù)的使用調(diào)用工作表函數(shù)求和在對工作表的單元格區(qū)域進行求和計算時，使用工作表Sum函數(shù)比使用VB

6、A代碼遍歷單元格進行累加求和效率要高得多，代碼如下所示。#001SubrngSum()#002DimrngAsRange#003DimdAsDouble#004Setrng=Range(A1:F7)#005d=Application.WorksheetFunction.Sum(rng)#006MsgBoxrng.Address(0,0)&單元格的和為&d#007EndSub代碼解析：rngSum過程調(diào)用工作表Sum函數(shù)對工作表的單元格區(qū)域進行求和計算。在VBA中調(diào)用工作表函數(shù)需要在工作表函數(shù)前加上WorksheetFunction屬性。應(yīng)用于Application對象的WorksheetFun

7、ction屬性返回WorksheetFunction對象，作為VBA中調(diào)用工作表函數(shù)的容器，在實際應(yīng)用中可省略Application對象識別符。查找最大、最小值在VBA中沒有內(nèi)置的函數(shù)可以進行最大、最小值的查找，借助工作表Max、Min函數(shù)可以快速地在工作表區(qū)域中查找最大、最小值，如下面的代碼所示。#001Subseeks()#002DimrngAsRange#003DimmyRngAsRange#004Dimk1AsInteger,k2AsInteger#005DimmaxAsDouble,minAsDouble#006SetmyRng=Sheet1.Range(A1:F30)#007For

8、EachrngInmyRng#008Ifrng.Value=WorksheetFunction.max(myRng)Then#009rng.Interior.ColorIndex=3#010k1=k1+1#011max=rng.Value#012ElseIfrng.Value=WorksheetFunction.min(myRng)Then#013rng.Interior.ColorIndex=5#014k2=k2+1#015min=rng.Value#016Else#017rng.Interior.ColorIndex=0#018EndIf#019Next#020MsgBox最大值是:&ma

9、x&共有&k1&個_#021&Chr(13)&最小值是:&min&共有&k2&個#022EndSub代碼解析：seeks過程在工作表單元格區(qū)域中查找最大、最小值，并將其所在的單元格底色分別設(shè)置為紅色和藍色。第2行到第5行代碼聲明變量類型。第6行代碼使用關(guān)鍵字Set將單元格引用賦給變量myRng。第7行到第19行代碼遍歷單元格區(qū)域，使用工作表Max、Min函數(shù)判斷單元格數(shù)值是否是所在區(qū)域的最大、最小值，如果是，將其所在的單元格底色設(shè)置為紅色或藍色，并保存其數(shù)值和數(shù)量。第20、21行代碼使用消息框顯示最大、最小值數(shù)值和數(shù)量。運行seeks過程后將工作表區(qū)域最大、最小值所在的單元格的底色設(shè)置為紅色或

10、藍色并用消息框顯示其數(shù)值和數(shù)量，如REF_Ref230603713h圖1541所示。圖STYLEREF3s154SEQ圖*ARABICs31查找最大、最小值不重復(fù)值的錄入在工作表中錄入數(shù)據(jù)時，有時希望能限制重復(fù)值的錄入，比如在示例的A列單元格只能錄入唯一的人員編號，此時可以利用工作表的Change事件結(jié)合工作表的CountIf函數(shù)來判斷所錄入的人員編號是否重復(fù)，示例代碼如下。#001PrivateSubWorksheet_Change(ByValTargetAsRange)#002WithTarget#003If.Column1Or.Count1ThenExitSub#004IfApplica

11、tion.CountIf(Range(A:A),.Value)1Then#005.Select#006MsgBox不能輸入重復(fù)的人員編號!,64#007Application.EnableEvents=False#008.Value=#009Application.EnableEvents=True#010EndIf#011EndWith#012EndSub代碼解析：工作表的Change事件過程，使A列單元格只能錄入唯一的人員編號。第4行代碼使用工作表的CountIf函數(shù)來判斷在A列單元格輸入的人員編號是否重復(fù)。工作表的CountIf函數(shù)計算區(qū)域中滿足給定條件的單元格的個數(shù)，語法如下：COUN

12、TIF(range,criteria)參數(shù)range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。參數(shù)criteria為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式、單元格引用或文本。在示例中以所錄入的人員編號與A列單元格區(qū)域進行比較，如果CountIf函數(shù)的返回值大于1，說明錄入的是重復(fù)編號。第5行代碼，重新選擇該單元格便于下一步清空后重新錄入。第7、8、9行代碼，清除錄入的重復(fù)編號，在清除前將Application對象的EnableEvents屬性設(shè)置為False，禁用事件。因為如果不禁用事件，那么在清除重復(fù)值的過程中會不斷地觸發(fā)工作表的Change事件，從而造成代碼運行的

13、死循環(huán)。經(jīng)過以上的設(shè)置，在工作表的A列中只能錄入唯一的人員編號，如果錄入重復(fù)值會進行提示，如REF_Ref230658280h圖1551所示，點擊確定后自動清除錄入的重復(fù)編號。圖STYLEREF3s155SEQ圖*ARABICs31限制重復(fù)值的錄入獲得當(dāng)月的最后一天在實際工作中經(jīng)常需要根據(jù)給定的日期計算其所屬月份的最后一天，此時可以使用DateSerial函數(shù)完成計算，如下面的代碼所示。#001SubSerial()#002DimDateStrAsByte#003DateStr=Day(DateSerial(Year(Date),Month(Date)+1,0)#004MsgBox本月的最后一

14、天是&Month(Date)&月&DateStr&號#005EndSub代碼解析：Serial過程配合使用了4個VBA內(nèi)置函數(shù)Year、Month、Day和DateSerial完成計算并使用消息框顯示當(dāng)月最后一天的日期。Year、Month和Day函數(shù)分別返回代表指定日期的年、月、日的整數(shù)，語法如下：Year(Date)Month(Date)Day(Date)其中參數(shù)Date可以是任何能夠表示日期的Variant、數(shù)值表達式、字符串表達式或它們的組合。DateSerial函數(shù)返回包含指定的年、月、日的Variant(Date)，語法如下：DateSerial(year,month,day)其中

15、參數(shù)year、month、day分別表示指定的年、月、日。為了指定某個日期，DateSerial函數(shù)中的每個參數(shù)的取值范圍應(yīng)該是可接受的，即日的取值范圍應(yīng)在1-31之間，而月的取值范圍應(yīng)在1-12之間。但是，當(dāng)一個數(shù)值表達式表示某日之前或其后的年、月、日數(shù)時，也可以為每個使用這個數(shù)值表達式的參數(shù)指定相對日期。當(dāng)任何一個參數(shù)的取值超出可接受的范圍時，它會自動地在可接受的時間單位進行調(diào)整，例如本例中的day參數(shù)設(shè)置為0，則被解釋成month參數(shù)指定月的前一天，即表達式Month(Date)+1指定的下一個月的前一天，也就是本月的最后一天。運行Serial過程結(jié)果如REF_Ref230659564h

16、圖1561所示。圖STYLEREF3s156SEQ圖*ARABICs31獲得當(dāng)月的最后一天四舍五入運算在實際工作中經(jīng)常需要對數(shù)值或計算結(jié)果進行四舍五入運算，此時可以使用VBA內(nèi)置的Round函數(shù)。Round函數(shù)返回一個數(shù)值，該數(shù)值是按照指定的小數(shù)位數(shù)進行四舍五入運算的結(jié)果，語法如下：Round(expression,numdecimalplaces)參數(shù)expression是必需的，要進行四舍五入運算的數(shù)值表達式。參數(shù)numdecimalplaces是可選的，數(shù)字值，表示進行四舍五入運算時，小數(shù)點右邊應(yīng)保留的位數(shù)。如果忽略，則Round函數(shù)返回整數(shù)。但是VBA內(nèi)置的Round函數(shù)在對數(shù)值進行四

17、舍五入運算時實行的是Bankre舍入，而不是算術(shù)舍入。按Bankre舍入規(guī)則，如果保留位數(shù)的下一個數(shù)字正好是5則其后沒有其他有效數(shù)字，則按保留位最后一位“偶舍奇入”的方法進行處理。比如Round(1.5)的保留位最后為1，是奇數(shù)，小數(shù)位的5入上去，因此Round(1.5)的運算結(jié)果是2；而Round(4.5)的保留位最后為4，是偶數(shù)，小數(shù)位的5舍去，因此Round(4.5)的運算結(jié)果是4而不是5。Bankre舍入規(guī)則雖然有其合理性，但不符合實際工作的需要。在實際應(yīng)用中使用以下兩種方法避免Bankre舍入：極小值修正法在使用Round函數(shù)時對需要舍入的數(shù)值先加上極小值再調(diào)用VBA內(nèi)置的Round

18、函數(shù)，如下面的代碼所示。#001SubaTestRound()#002MsgBoxRound(4.5)=&Round(4.5)&Chr(13)&Round(4.5)=&Round(4.5+0.0000001)#003EndSub代碼解析：aTestRound過程分別調(diào)用VBA內(nèi)置的Round函數(shù)和加上極小值再調(diào)用VBA內(nèi)置的Round函數(shù)在洗染店框中顯示兩者運算結(jié)果，如REF_Ref230660537h圖1571所示。圖STYLEREF3s157SEQ圖*ARABICs31加上極小值進行運算結(jié)果從運算結(jié)果中可以發(fā)現(xiàn)，加上極小值后Round(4.5)已正確運算為5而不是4。調(diào)用工作表函數(shù)法還可以

19、使用工作表函數(shù)Round代替VBA內(nèi)置的Round函數(shù)。工作表函數(shù)Round和VBA內(nèi)置的Round函數(shù)的用法相同，但它采用算術(shù)舍入而不是Bankre舍入，所以不會有“偶舍奇入”的問題，如下面的代碼所示。#001SubbTestRound()#002MsgBoxRound(4.5)=&Round(4.5)&Chr(13)&Round(4.5)=&Application.Round(4.5,0)#003EndSub代碼解析：bTestRound過程分別調(diào)用VBA內(nèi)置的Round函數(shù)和工作表Round函數(shù)在消息框中顯示兩者運算結(jié)果，如REF_Ref230660671h圖1572所示。圖STYLER

20、EF3s157SEQ圖*ARABICs32工作表函數(shù)運算結(jié)果從運算結(jié)果中可以發(fā)現(xiàn)，使用工作表Round函數(shù)后Round(4.5)已正確運算為5而不是4。使用字符串函數(shù)使用VBA的字符串函數(shù)可以對字符串進行各種操作，如下面的代碼所示。#001SubStrFunctions()#002DimStrAsString#003Str=AbcDEFGhijkLmn#004MsgBox原始字符串為：&Str&Chr(13)_#005&字符串長度為：&Len(Str)&Chr(13)_#006&左邊8個字符為：&Left(Str,8)&Chr(13)_#007&右邊6個字符為：&Right(Str,6)&Ch

21、r(13)_#008&從左邊第2個開始取5個字符為：&Mid(Str,2,5)&Chr(13)_#009&轉(zhuǎn)換為大寫：&UCase(Str)&Chr(13)_#010&轉(zhuǎn)換為小寫：&LCase(Str)&Chr(13)#011EndSub代碼解析：StrFunctions過程使用字符串函數(shù)對字符串進行各種操作，如計算字符數(shù)、取得一定數(shù)量的字符、大小寫轉(zhuǎn)換等。第5行代碼使用Len函數(shù)返回字符串內(nèi)字符的數(shù)目，Len函數(shù)語法如下：Len(string|varname)參數(shù)string為任何有效的字符串表達式。參數(shù)varname為任何有效的變量名稱。兩個可能的參數(shù)必須有一個，而且只能有一個參數(shù)。第6行

22、代碼使用Left函數(shù)從字符串左邊起返回8個字符。第7行代碼使用Right函數(shù)從字符串右邊起返回6個字符Left函數(shù)語法如下：Left(string,length)Right函數(shù)語法如下：Right(string,length)參數(shù)string是必需的，字符串表達式。參數(shù)length是必需的，數(shù)值表達式，將返回的字符數(shù)量。如果為0，返回零長度字符串()；如果大于或等于參數(shù)string的字符數(shù)，則返回整個字符串。第8行代碼使用Mid函數(shù)從字符串第2位起返回5個字符。Mid函數(shù)語法如下：Mid(string,start,length)參數(shù)string是必需的，字符串表達式。參數(shù)start是必需的，s

23、tring中被取出部分的字符位置。如果超過string的字符數(shù)，將返回零長度字符串()。參數(shù)length是可選的，要返回的字符數(shù)。如果省略或超過string的字符數(shù)，將返回字符串中所有字符。第9行代碼使用UCase函數(shù)將字符串轉(zhuǎn)換成大寫的字符串。第10行代碼使用LCase函數(shù)將字符串轉(zhuǎn)換成小寫的字符串。UCase函數(shù)的語法如下：UCase(string)LCase函數(shù)的語法如下：LCase(string)參數(shù)string是必需的，任何有效的字符串表達式。運行StrFunctions過程結(jié)果如REF_Ref230704968h圖1581所示。圖STYLEREF3s158SEQ圖*ARABICs3

24、1使用字符串函數(shù)使用日期函數(shù)使用VBA的日期函數(shù)可以對日期進行各種計算，如下面的代碼所示。#001SubDatFunctions()#002DimStrAsString#003DimWeekAsString#004Str=InputBox(請輸入日期：)#005IfLen(Str)0Then#006IfIsDate(Str)Then#007SelectCaseWeekday(Str,vbMonday)#008Case1#009Week=一#010Case2#011Week=二#012Case3#013Week=三#014Case4#015Week=四#016Case5#017Week=五#01

25、8Case6#019Week=六#020Case7#021Week=日#022EndSelect#023MsgBox你輸入的日期是&DateValue(Str)&Chr(13)_#024&是&Year(Str)&年的第&DatePart(q,Str)&季度&Chr(13)_#025&是星期&Week&Chr(13)_#026&距離今天有&Abs(DateDiff(d,Date,Str)&天&Chr(13)_#027&60天后的日期是&DateAdd(d,60,Str)#028Else#029MsgBox請輸入正確格式的日期!#030EndIf#031EndIf#032EndSub代碼解析：Da

26、tFunctions過程在對話框中輸入日期后使用各種日期函數(shù)對其進行計算并用消息框顯示。第4、5行代碼使用InputBox函數(shù)顯示一個對話框，供用戶在對話框中輸入一個日期。第6行代碼使用IsDate函數(shù)判斷輸入的日期是否正確。IsDate函數(shù)返回Boolean值，指出一個表達式是否可以轉(zhuǎn)換成日期，語法如下：IsDate(expression)參數(shù)expression是必需的，日期表達式或字符串表達式，如果表達式是一個日期，或者可以作為有效日期識別，則IsDate函數(shù)返回True，否則返回False。第7行到第22行代碼使用Weekday函數(shù)判斷所輸入的日期是星期幾。Weekday函數(shù)返回一個整

27、數(shù)，代表某個日期是星期幾，語法如下：Weekday(date,firstdayofweek)參數(shù)date是必需的，能夠表示日期的Variant、數(shù)值表達式、字符串表達式或它們的組合。參數(shù)firstdayofweek是可選的，指定一星期第一天的常數(shù)，如REF_Ref230747531h表格1591所示。常數(shù)值描述vbUseSystem0使用NLSAPI設(shè)置VbSunday1星期日（缺省值）vbMonday2星期一vbTuesday3星期二vbWednesday4星期三vbThursday5星期四vbFriday6星期五vbSaturday7星期六表格STYLEREF3s159SEQ表格*ARAB

28、ICs31firstdayofweek參數(shù)值Weekday函數(shù)返回一個1到7之間的整數(shù)，當(dāng)firstdayofweek參數(shù)設(shè)置為vbMonday（2）時，返回1時說明是星期一，以此類推。第23行代碼根據(jù)系統(tǒng)中指定的短日期格式來顯示所輸入的日期。DateValue函數(shù)的語法如下：DateValue(date)參數(shù)date是必需的，任何表達式，表示從100年1月1日到9999年12月31日之間的一個日期。如果是一個字符串，且其內(nèi)容只有數(shù)字以及分隔數(shù)字的日期分隔符，則DateValue函數(shù)就會根據(jù)系統(tǒng)中指定的短日期格式來識別月、日、年的順序。DateValue函數(shù)也識別明確的英文月份名稱，全名或縮寫

29、均可。例如，除了12/30/1991和12/30/91之外，DateValue函數(shù)也能識別December30,1991和Dec30,1991。如果date參數(shù)中略去了年這一部分，DateValue函數(shù)就會使用由計算機系統(tǒng)日期設(shè)置的當(dāng)前年份。第24行代碼判斷輸入的日期的季度。DatePart函數(shù)返回一個包含已知日期的指定時間部分的值，語法如下：DatePart(interval,date,firstdayofweek,firstweekofyear)其中參數(shù)interval是必需的，字符串表達式，是要返回的時間間隔，設(shè)定值如REF_Ref230748823h表格1592所示。設(shè)置說明yyyy年

30、q季m日y一年的日數(shù)d日w一周的日數(shù)ww周h時n分鐘s秒表格STYLEREF3s159SEQ表格*ARABICs32interval參數(shù)設(shè)定值第26行代碼計算所輸入的日期距當(dāng)天的天數(shù)。DateDiff函數(shù)返回兩個指定日期間的時間間隔數(shù)目，語法如下：DateDiff(interval,date1,date2,firstdayofweek,firstweekofyear)其中參數(shù)interval是必需的，字符串表達式，表示用來計算date1和date2的時間差的時間間隔，設(shè)定值如REF_Ref230748823h表格1592所示。參數(shù)date1和date2是必需的，計算中要用到的兩個日期。因為如果

31、輸入的日期是當(dāng)前日期以前的日期，DateDiff函數(shù)會返回負(fù)值，所以使用Abs函數(shù)返回絕對值將其轉(zhuǎn)換為正值。第27行代碼計算所輸入的日期距當(dāng)天的天數(shù)，DateAdd返回加上了一段時間間隔的一個日期，語法如下：DateAdd(interval,number,date)參數(shù)interval是必需的，字符串表達式，是所要加上去的時間間隔，設(shè)定值如REF_Ref230748823h表格1592所示。參數(shù)number是必需的，是要加上的時間間隔的數(shù)目。其數(shù)值可以為正數(shù)（得到未來的日期），也可以為負(fù)數(shù)（得到過去的日期）。參數(shù)date是必需的，需要加上時間間隔的字符串表達式。運行DatFunctions過程

32、，在顯示的對話框中輸入一個日期，結(jié)果如REF_Ref230750505h圖1591所示。圖STYLEREF3s159SEQ圖*ARABICs31使用日期函數(shù)判斷是否為數(shù)值使用IsNumeric函數(shù)可以判斷表達式的運算結(jié)果是否為數(shù)值，如下面的代碼所示。#001SubNumeric()#002DimiAsInteger#003DimnAsString#004DimsAsString#005WithSheet1#006Fori=1To.Range(A65536).End(xlUp).Row#007IfIsNumeric(.Cells(i,1)Then#008n=n&.Cells(i,1).Addre

33、ss(0,0)&Chr(9)&.Cells(i,1)&Chr(13)#009Else#010s=s&.Cells(i,1).Address(0,0)&Chr(9)&.Cells(i,1)&Chr(13)#011EndIf#012Next#013EndWith#014MsgBoxA列中數(shù)值單元格：&Chr(13)&n&Chr(13)_#015&A列中非數(shù)值單元格：&Chr(13)&s#016EndSub代碼解析：Numeric過程使用IsNumeric函數(shù)判斷工作表的A列單元格是否為數(shù)值，并使用消息框顯示。第7行代碼判斷工作表的A列單元格是否為數(shù)值。IsNumeric函數(shù)返回Boolean值，指

34、出表達式的運算結(jié)果是否為數(shù)，語法如下：IsNumeric(expression)參數(shù)expression是必需的，Variant類型，包含數(shù)值表達式或字符串表達式。如果參數(shù)expression的運算結(jié)果為數(shù)字，則IsNumeric返回True，否則返回False。第8行代碼將數(shù)值單元格的地址和數(shù)值保存在變量e中。第10行代碼將非數(shù)值單元格的地址和內(nèi)容保存在變量s中。在保存時插入制表符對數(shù)據(jù)列進行分隔，使之排列整齊，請參閱技巧REF_Ref231009602rh73-5。運行Numeric過程結(jié)果如REF_Ref231009713h圖1601所示。圖STYLEREF3s160SEQ圖*ARABI

35、Cs31判斷是否為數(shù)值格式化數(shù)值、日期和時間Format函數(shù)是VBA中的常用函數(shù)，可以實現(xiàn)數(shù)值、日期和時間格式的轉(zhuǎn)變，示例代碼如下：#001SubFromatCurrent()#002MsgBoxFormat(123456.789,0.00)&Chr(13)_#003&Format(123456.789,0.00%)&Chr(13)_#004&Format(123456.789,#,#0.00)&Chr(13)_#005&Format(-123456.789,$#,#0.00;($#,#0.00)&Chr(13)_#006&Format(-123456.789,￥#,#0.00;(￥#,#0.

36、00)&Chr(13)_#007&Format(Date,yyyy-mm-dd)&Chr(13)_#008&Format(Date,yyyymmdd)&Chr(13)_#009&Format(Date,LongDate)&Chr(13)_#010&Format(Now,hh:mm:ss)&Chr(13)_#011&Format(Now,hh:mm:ssAMPM)#012EndSub代碼解析：FromatCurrent過程使用消息框顯示格式化后的數(shù)值、日期和時間。Format函數(shù)根據(jù)格式表達式中的指令來格式化的數(shù)值、日期和時間，語法如下：Format(expression,format,firs

37、tdayofweek,firstweekofyear)其中參數(shù)expression是必需的，任何有效的表達式。參數(shù)format是可選的，有效的命名表達式或用戶自定義格式表達式。第2行代碼將數(shù)值格式化為兩位小數(shù)格式顯示。第3行代碼將數(shù)值格式化為兩位小數(shù)的百分比格式顯示。第4行代碼將數(shù)值格式化為千位分隔符顯示。第5行代碼將數(shù)值格式化為以美元符號顯示的兩位小數(shù)，以千位分隔符分隔，如果是負(fù)值則以小括號顯示。第6行代碼將數(shù)值格式化為以人民幣符號顯示的兩位小數(shù)，以千位分隔符分隔，如果是負(fù)值則以小括號顯示。第7行代碼將系統(tǒng)日期格式化為“yyyy-mm-dd”格式顯示。第8行代碼將系統(tǒng)日期格式化為“yyyym

38、mdd”格式顯示。第9行代碼將系統(tǒng)日期格式化為長日期格式顯示。第10行代碼將系統(tǒng)時間格式化為24小時、分鐘和秒的格式顯示。第11行代碼將系統(tǒng)時間格式化為分12小時、分鐘和秒的格式顯示。運行FromatCurrent過程結(jié)果如REF_Ref231048403h圖1611所示。圖STYLEREF3s161SEQ圖*ARABICs31格式化數(shù)值、日期和時間個人所得稅自定義函數(shù)在財務(wù)工作中經(jīng)常需要計算個人所得稅，而在Excel中沒有計算個人所得稅的函數(shù)，此時可以使用自定義函數(shù)來計算，如下面的代碼所示。#001PublicFunctionPITax(Income,OptionalThreshold)As

39、Single#002DimRateAsSingle#003DimDebitAsSingle#004DimTaxliabilityAsSingle#005IfIsMissing(Threshold)ThenThreshold=2000#006Taxliability=Income-Threshold#007SelectCaseTaxliability#008Case0To500#009Rate=0.05#010Debit=0#011Case500.01To2000#012Rate=0.1#013Debit=25#014Case2000.01To5000#015Rate=0.15#016Debit

40、=125#017Case5000.01To20000#018Rate=0.2#019Debit=375#020Case20000.01To40000#021Rate=0.25#022Debit=1375#023Case40000.01To60000#024Rate=0.3#025Debit=3375#026Case60000.01To80000#027Rate=0.35#028Debit=6375#029Case80000.01To10000#030Rate=0.4#031Debit=10375#032CaseElse#033Rate=0.45#034Debit=15375#035EndSel

41、ect#036IfTaxliability0Then#005IfNotExistSh(Sh)Then#006MsgBox對不起,&Sh&表不存在!#007Else#008Sheets(Sh).Select#009EndIf#010EndIf#011EndSub代碼解析：NotSht過程使用自定義的ExistSh函數(shù)判斷工作簿中是否存在指定名稱的工作表，如果不存在則使用消息框進行提示，如REF_Ref231314742h圖1661所示。圖STYLEREF3s166SEQ圖*ARABICs31查找指定工作表查找指定工作簿是否打開如果需要判斷指定名稱的工作簿是否已經(jīng)打開，除了使用REF_Ref231

42、141736rh技巧43的方法外，還可以使用與REF_Ref231141764rh技巧166類似的自定義函數(shù)，如下面的代碼所示。#001FunctionExistWorkbook(WbNameAsString)AsBoolean#002DimwbAsWorkbook#003OnErrorResumeNext#004Setwb=Workbooks(WbName)#005IfErr.Number=0ThenExistWorkbook=True#006Setwb=Nothing#007EndFunction代碼解析：自定義ExistWorkbook函數(shù)判斷指定名稱的工作簿是否已經(jīng)打開。第5行代碼判斷

43、前面的賦值語句是否存在錯誤。如果沒有指定名稱的工作簿，則第4行代碼會產(chǎn)生錯誤，自定義ExistWorkbook函數(shù)返回False。下面使用自定義ExistWorkbook函數(shù)判斷名稱為“ExcelHome”的工作簿是否已經(jīng)打開，如果沒有打開則使用消息框進行提示，如REF_Ref231141813h圖1671所示。#001SubNotWorkbook()#002IfNot(ExistWorkbook(ExcelHome)ThenMsgBox對不起,ExcelHome工作簿沒有打開!#003EndSub圖STYLEREF3s167SEQ圖*ARABICs31消息框提示取得應(yīng)用程序的安裝路徑使用自定

44、義函數(shù)取得應(yīng)用程序的安裝路徑，如下面的代碼所示。#001FunctionGetSetupPath(AppNameAsString)#002DimWSHAsObject#003SetWSH=CreateObject(Wscript.Shell)#004GetSetupPath=WSH.RegRead(HKEY_LOCAL_MACHINESoftware_#005&MicrosoftWindowsCurrentVersionAppPaths_#006&AppName&Path)#007SetWSH=Nothing#008EndFunction#009SubWinRARPath()#010MsgBo

45、xGetSetupPath(WinRAR.exe)#011EndSub代碼解析：自定義GetSetupPath函數(shù)取得應(yīng)用程序的安裝路徑，其中參數(shù)AppName代表指定的應(yīng)用程序的名稱。第3行代碼使用CreateObject函數(shù)將Wscript.Shell對象的引用賦給變量WSH。CreateObject函數(shù)創(chuàng)建并返回一個對ActiveX對象的引用，語法如下：CreateObject(class,servername)參數(shù)class是必需的，Variant(String)，要創(chuàng)建的應(yīng)用程序名稱和類。參數(shù)servername是可選的，Variant(String)，要在其上創(chuàng)建對象的網(wǎng)絡(luò)服務(wù)器名

46、稱。如果servername是一個空字符串()，即使用本地機器。第4行代碼取得AppName參數(shù)指定的應(yīng)用程序在注冊表中的路徑。WinRARPath過程使用消息框顯示由自定義的GetSetupPath函數(shù)取得的應(yīng)用程序“WinRAR”的安裝路徑。運行WinRARPath過程結(jié)果如REF_Ref231141866h圖1681所示。圖STYLEREF3s168SEQ圖*ARABICs31應(yīng)用程序安裝路徑數(shù)組的使用代碼運行時創(chuàng)建數(shù)組使用Array函數(shù)可以在代碼運行時創(chuàng)建數(shù)組并把一系列數(shù)據(jù)保存在數(shù)組中，示例代碼如下：#001OptionBase1#002Subarr()#003DimarrAsVari

47、ant#004DimiAsInteger#005arr=Array(王曉明,吳勝玉,周志國,曹武偉,張新發(fā),卓雪梅,沈煜婷,丁林平)#006Fori=LBound(arr)ToUBound(arr)#007Cells(i,1)=arr(i)#008Next#009EndSub代碼解析：Arr過程使用Array函數(shù)創(chuàng)建一個數(shù)組用來保存數(shù)據(jù)并將其寫入到工作表的單元格區(qū)域。第1行代碼使用OptionBase語句聲明數(shù)組下標(biāo)的缺省下界為1，數(shù)組下標(biāo)的缺省下界默認(rèn)為0。第5行代碼使用Array函數(shù)創(chuàng)建數(shù)組用來保存數(shù)據(jù)。Array函數(shù)返回一個包含數(shù)組的Variant，語法如下：Array(arglist)

48、Arglist參數(shù)是一個用逗號隔開的值表，這些值用于給Variant所包含的數(shù)組的各元素賦值。如果不提供Arglist參數(shù)，則創(chuàng)建一個長度為0的數(shù)組。第6行代碼使用LBound函數(shù)和UBound函數(shù)取得數(shù)組的最小和最大下標(biāo)。LBound函數(shù)返回一個Long型數(shù)據(jù)，其值為指定數(shù)組維可用的最小下標(biāo)，語法如下：LBound(arrayname,dimension)UBound函數(shù)返回一個Long型數(shù)據(jù)，其值為指定數(shù)組維可用的最大下標(biāo)，語法如下：UBound(arrayname,dimension)參數(shù)arrayname是必需的，數(shù)組變量的名稱。參數(shù)dimension是可選的，指定返回哪一維的下界，1

49、表示第一維，2表示第二維，如此類推。默認(rèn)為1。UBound函數(shù)與LBound函數(shù)一起使用，可以用來確定數(shù)組的大小。第7行代碼確定數(shù)組的大小后使用For.Next語句遍歷數(shù)組元素并將數(shù)組元素依次寫入到工作表的A列單元格中，如REF_Ref231490137h圖1691所示。圖STYLEREF3s169SEQ圖*ARABICs31將數(shù)組元素寫入工作表文本轉(zhuǎn)換為數(shù)組在處理字符串時可以使用Split函數(shù)將字符串按指定的分隔符分開并以數(shù)組返回，代碼如下：#001SubSplitarr()#002DimArrAsVariant#003Arr=Split(Sheet2.Cells(1,1),)#004She

50、et1.Cells(1,1).Resize(UBound(Arr)+1,1)=Application.Transpose(Arr)#005EndSub代碼解析：Splitarr過程使用Split函數(shù)將工作表Sheet2中A1單元格的姓名分別寫入到工作表Sheet1中的A列單元格。Split函數(shù)返回一個下標(biāo)從零開始的一維數(shù)組，包含指定數(shù)目的子字符串，語法如下：Split(expression,delimiter,limit,compare)參數(shù)expression是必需的，包含子字符串和分隔符的字符串表達式。參數(shù)delimiter是必需的，用來標(biāo)識子字符串邊界的字符串字符。如果忽略，則使用空格字

51、符()作為分隔符。第4行代碼，首先使用UBound函數(shù)取得返回數(shù)組的最大下標(biāo)后調(diào)整單元格區(qū)域，因為數(shù)組下標(biāo)的缺省下界默認(rèn)為0，所以在使用Resize屬性調(diào)整單元格區(qū)域時參數(shù)RowSize需要在返回數(shù)組的最大下標(biāo)上加一。然后使用工作表Transpose函數(shù)將返回數(shù)組轉(zhuǎn)置后寫入到工作表調(diào)整后的單元格區(qū)域中。工作表Transpose函數(shù)返回轉(zhuǎn)置單元格區(qū)域，即將一行單元格區(qū)域轉(zhuǎn)置成一列單元格區(qū)域，反之亦然，語法如下：TRANSPOSE(array)參數(shù)array為需要進行轉(zhuǎn)置的數(shù)組或工作表中的單元格區(qū)域。Splitarr過程將如REF_Ref231520697h圖1692所示的工作表單元格中的字符串以

52、逗號分隔后依次寫入到工作表的A列單元格中，如REF_Ref231520726h圖1693所示。圖STYLEREF3s169SEQ圖*ARABICs32工作表單元格中的字符串圖STYLEREF3s169SEQ圖*ARABICs33文本轉(zhuǎn)換為數(shù)組寫入單元格使用動態(tài)數(shù)組去除重復(fù)值在技巧REF_Ref231550675rh169-2中使用數(shù)組函數(shù)將單元格中的文本進行分隔后寫入到工作表Sheet1中的A列單元格，但是如果文本中含有大量的重復(fù)值，在寫入時也會將重復(fù)值寫入到工作表中，此時可以使用動態(tài)數(shù)組去除文本中的重復(fù)值，如下面的代碼所示。#001SubSplitarr()#002DimSplarr()AsString#003DimArr()AsString#004DimTemp()AsString#005DimrAsInteger#006DimiAsInt