excel161個(gè)VBA自定義函數(shù)大全

1、VBA自定義函數(shù)大全目錄TOC o 1-2 h u HYPERLINK l _Toc23490 1.函數(shù)作用：返回 Column 英文字 PAGEREF _Toc23490 5 HYPERLINK l _Toc25070 2.函數(shù)作用：查詢某一值第num次出現(xiàn)的值 PAGEREF _Toc25070 5 HYPERLINK l _Toc14911 3.函數(shù)作用：返回當(dāng)個(gè)人工資薪金所得為2000元(起征點(diǎn)為850元)時(shí)的應(yīng)納個(gè)人所得稅稅額 PAGEREF _Toc14911 6 HYPERLINK l _Toc22651 4.函數(shù)作用：從形如123545ABCDE的字符串中取出數(shù)字 PAGERE

2、F _Toc22651 7 HYPERLINK l _Toc4399 5.函數(shù)作用：從形如ABCD12455EDF的字符串中取出數(shù)字 PAGEREF _Toc4399 7 HYPERLINK l _Toc21003 6.函數(shù)作用：按SplitType取得RangeName串值中的起始位置 PAGEREF _Toc21003 8 HYPERLINK l _Toc7930 7.函數(shù)作用:將金額數(shù)字轉(zhuǎn)成中文大寫(xiě) PAGEREF _Toc7930 9 HYPERLINK l _Toc22610 8.函數(shù)作用:計(jì)算某種稅金 PAGEREF _Toc22610 14 HYPERLINK l _Toc814

3、3 9.函數(shù)作用:人民幣大、小寫(xiě)轉(zhuǎn)換 PAGEREF _Toc8143 14 HYPERLINK l _Toc13055 10.函數(shù)作用:查漢字區(qū)位碼 PAGEREF _Toc13055 16 HYPERLINK l _Toc12268 11.函數(shù)作用:把公元年轉(zhuǎn)為農(nóng)歷 PAGEREF _Toc12268 17 HYPERLINK l _Toc31580 12.函數(shù)作用:返回指定列數(shù)的列標(biāo) PAGEREF _Toc31580 38 HYPERLINK l _Toc24917 13.函數(shù)作用:用指定字符替換某字符 PAGEREF _Toc24917 39 HYPERLINK l _Toc2706

4、5 14.函數(shù)作用:從右邊開(kāi)始查找指定字符在字符串中的位置 PAGEREF _Toc27065 39 HYPERLINK l _Toc10258 15.函數(shù)作用:從右邊開(kāi)始查找指定字符在字符串中的位置 PAGEREF _Toc10258 40 HYPERLINK l _Toc4908 16.函數(shù)作用:計(jì)算工齡 PAGEREF _Toc4908 40 HYPERLINK l _Toc6494 17.函數(shù)作用:計(jì)算日期差，除去星期六、星期日 PAGEREF _Toc6494 41 HYPERLINK l _Toc17018 18.函數(shù)作用:將英文字反轉(zhuǎn)的自定函數(shù). PAGEREF _Toc1701

5、8 42 HYPERLINK l _Toc7549 19.函數(shù)作用:計(jì)算個(gè)人所得稅 PAGEREF _Toc7549 42 HYPERLINK l _Toc11556 20.函數(shù)作用:一個(gè)能計(jì)算是否有重復(fù)單元的函數(shù) PAGEREF _Toc11556 43 HYPERLINK l _Toc28941 21.數(shù)字金額轉(zhuǎn)中文大寫(xiě) PAGEREF _Toc28941 44 HYPERLINK l _Toc3558 22.函數(shù)作用:將數(shù)字轉(zhuǎn)成英文 PAGEREF _Toc3558 45 HYPERLINK l _Toc31245 23.函數(shù)作用:人民幣大小寫(xiě)轉(zhuǎn)換 PAGEREF _Toc31245 4

6、8 HYPERLINK l _Toc14384 24.函數(shù)作用:獲取區(qū)域顏色值 PAGEREF _Toc14384 49 HYPERLINK l _Toc22002 25.函數(shù)作用:獲取活開(kāi)工作表名 PAGEREF _Toc22002 49 HYPERLINK l _Toc5212 26.函數(shù)作用:獲取最后一行行數(shù) PAGEREF _Toc5212 49 HYPERLINK l _Toc29053 27.函數(shù)作用:判斷是否連接在線 PAGEREF _Toc29053 50 HYPERLINK l _Toc12980 28.函數(shù)作用:幣種轉(zhuǎn)換 PAGEREF _Toc12980 50 HYPER

7、LINK l _Toc16433 29.函數(shù)作用:檢驗(yàn)工作表是否有可打印內(nèi)容 PAGEREF _Toc16433 51 HYPERLINK l _Toc21379 30.函數(shù)作用:查找一字符串(withinstr)在另一字符串中(findstr1)中某一次(startnum)出現(xiàn)時(shí)的位置，返回零表示沒(méi)找到。 PAGEREF _Toc21379 53 HYPERLINK l _Toc6303 31.函數(shù)作用:增加文件路徑最后的“符號(hào) PAGEREF _Toc6303 54 HYPERLINK l _Toc29890 32.函數(shù)作用:計(jì)算所得稅 PAGEREF _Toc29890 54 HYPER

8、LINK l _Toc9184 33.函數(shù)作用:從工作表第一行的標(biāo)題文字以數(shù)字形式返回所在列號(hào) PAGEREF _Toc9184 54 HYPERLINK l _Toc14885 34.函數(shù)作用:在多個(gè)工作表中查找一個(gè)范圍內(nèi)符合某個(gè)指定條件的工程對(duì)應(yīng)指定范圍加總求和 PAGEREF _Toc14885 55 HYPERLINK l _Toc489 35.函數(shù)作用:返回 Column 英文字 PAGEREF _Toc489 56 HYPERLINK l _Toc27281 36.函數(shù)作用:查找指定列名的列數(shù) PAGEREF _Toc27281 56 HYPERLINK l _Toc4528 37

9、.函數(shù)作用:文字格式的時(shí)間(分:秒)轉(zhuǎn)化為數(shù)字格式(秒) PAGEREF _Toc4528 57 HYPERLINK l _Toc22081 38.函數(shù)作用:將hh:mm:ss格式的時(shí)分秒數(shù)轉(zhuǎn)換成秒數(shù) PAGEREF _Toc22081 57 HYPERLINK l _Toc817 39.函數(shù)作用:金額中文大寫(xiě)轉(zhuǎn)數(shù)字 PAGEREF _Toc817 58 HYPERLINK l _Toc21290 40.函數(shù)作用:把角度轉(zhuǎn)為度秒分、弧度等顯示 PAGEREF _Toc21290 59 HYPERLINK l _Toc19152 41.函數(shù)作用:身份證號(hào)碼偵測(cè) PAGEREF _Toc19152

10、 60 HYPERLINK l _Toc10587 42.函數(shù)作用:顯示公式 PAGEREF _Toc10587 61 HYPERLINK l _Toc2229 43.函數(shù)作用:方便財(cái)務(wù)人員理帳查找 PAGEREF _Toc2229 62 HYPERLINK l _Toc22373 44.函數(shù)作用:數(shù)值轉(zhuǎn)換為字符地址 PAGEREF _Toc22373 64 HYPERLINK l _Toc19470 45.函數(shù)作用:字符地址轉(zhuǎn)換為數(shù)值 PAGEREF _Toc19470 65 HYPERLINK l _Toc5911 46.函數(shù)作用:等待時(shí)間以秒計(jì)算 PAGEREF _Toc5911 65

11、HYPERLINK l _Toc3348 47.函數(shù)作用:得到字符串實(shí)際的長(zhǎng)度以單字節(jié)記 PAGEREF _Toc3348 66 HYPERLINK l _Toc5184 48.函數(shù)作用:18位身份證最后一位有效性驗(yàn)證 PAGEREF _Toc5184 66 HYPERLINK l _Toc8710 49.函數(shù)作用:計(jì)算符合maturity condition的拆解金額 PAGEREF _Toc8710 67 HYPERLINK l _Toc28742 50.函數(shù)作用:對(duì)多個(gè)用同一分隔符分隔的待查找元素，逐一在表區(qū)域首列內(nèi)搜索，將返回選定單元格的值相加， PAGEREF _Toc28742 6

12、8 HYPERLINK l _Toc14974 51.函數(shù)作用:根據(jù)個(gè)人所得稅工資反算工資數(shù) PAGEREF _Toc14974 69 HYPERLINK l _Toc10722 52.函數(shù)作用:判斷表是否存在 PAGEREF _Toc10722 70 HYPERLINK l _Toc27328 53.函數(shù)作用:角度轉(zhuǎn)弧 PAGEREF _Toc27328 70 HYPERLINK l _Toc20343 54.函數(shù)作用:比擬相同的字符串 PAGEREF _Toc20343 71 HYPERLINK l _Toc28175 55.函數(shù)作用:對(duì)選定的數(shù)組進(jìn)行排序 PAGEREF _Toc2817

13、5 71 HYPERLINK l _Toc9216 56.函數(shù)作用:取得指定月份天數(shù) PAGEREF _Toc9216 73 HYPERLINK l _Toc9523 57.函數(shù)作用:排序工作表活頁(yè)薄 PAGEREF _Toc9523 73 HYPERLINK l _Toc28662 58.函數(shù)作用:統(tǒng)計(jì)數(shù)組中非重復(fù)數(shù)據(jù)個(gè)數(shù) PAGEREF _Toc28662 74 HYPERLINK l _Toc15949 59.函數(shù)作用:摘取子字符串 PAGEREF _Toc15949 74 HYPERLINK l _Toc14935 60.函數(shù)作用:計(jì)算20000余個(gè)漢字的筆畫(huà) PAGEREF _Toc

14、14935 75 HYPERLINK l _Toc30778 61.函數(shù)作用:刪除當(dāng)前工作表中的全部超連接 PAGEREF _Toc30778 76 HYPERLINK l _Toc13056 62.函數(shù)作用:取得相近數(shù)據(jù) PAGEREF _Toc13056 76 HYPERLINK l _Toc17934 63.函數(shù)作用:提取定串中漢字 PAGEREF _Toc17934 77 HYPERLINK l _Toc8375 64.函數(shù)作用:搜索重復(fù)數(shù)據(jù)(選定范圍) PAGEREF _Toc8375 77 HYPERLINK l _Toc31602 65.函數(shù)作用:字符型轉(zhuǎn)數(shù)字型 PAGEREF

15、_Toc31602 78 HYPERLINK l _Toc30867 66.函數(shù)作用:小寫(xiě)人民幣轉(zhuǎn)大寫(xiě)人民幣 PAGEREF _Toc30867 78 HYPERLINK l _Toc12520 67.函數(shù)作用:取得指定月份人星期天個(gè)數(shù) PAGEREF _Toc12520 79 HYPERLINK l _Toc12715 68.函數(shù)作用:偵測(cè)檔案是否包含宏 PAGEREF _Toc12715 80 HYPERLINK l _Toc18249 69.函數(shù)作用:獲取循環(huán)參照單元格 PAGEREF _Toc18249 81 HYPERLINK l _Toc16176 70.函數(shù)作用:創(chuàng)立桌面快捷方式

16、 PAGEREF _Toc16176 81 HYPERLINK l _Toc27626 71.函數(shù)作用:自動(dòng)建立多級(jí)目錄 PAGEREF _Toc27626 82 HYPERLINK l _Toc11356 72.函數(shù)作用:統(tǒng)計(jì)經(jīng)篩選后符合條件的記錄條數(shù) PAGEREF _Toc11356 83 HYPERLINK l _Toc19287 73.函數(shù)作用:復(fù)制單元格列高與欄寬 PAGEREF _Toc19287 83 HYPERLINK l _Toc28896 74.函數(shù)作用:取消隱藏工作表包括vba Project工程保護(hù)的) PAGEREF _Toc28896 84 HYPERLINK l

17、 _Toc20215 75.函數(shù)作用:刪除單元格自定義名稱(chēng) PAGEREF _Toc20215 84 HYPERLINK l _Toc2483 76.函數(shù)作用:從文件路徑中取得文件名 PAGEREF _Toc2483 84 HYPERLINK l _Toc24306 77.函數(shù)作用:取得一個(gè)文件的擴(kuò)展名 PAGEREF _Toc24306 85 HYPERLINK l _Toc373 78.函數(shù)作用:取得一個(gè)文件的路徑 PAGEREF _Toc373 85 HYPERLINK l _Toc20445 79.函數(shù)作用:十進(jìn)制轉(zhuǎn)二進(jìn)制 PAGEREF _Toc20445 86 HYPERLINK

18、l _Toc11454 80.函數(shù)作用:檢查一個(gè)數(shù)組是否為空 PAGEREF _Toc11454 86 HYPERLINK l _Toc16380 81.函數(shù)作用:字母欄名轉(zhuǎn)數(shù)字欄名 PAGEREF _Toc16380 87 HYPERLINK l _Toc16914 82.函數(shù)作用:數(shù)字欄名轉(zhuǎn)文字欄名 PAGEREF _Toc16914 87 HYPERLINK l _Toc7182 83.函數(shù)作用:判斷一件活頁(yè)夾中是否還有子目錄 PAGEREF _Toc7182 87 HYPERLINK l _Toc29269 84.函數(shù)作用:判斷一個(gè)文件是否在使用中 PAGEREF _Toc29269

19、88 HYPERLINK l _Toc29696 85.函數(shù)作用:列出檔案詳細(xì)摘要信息 PAGEREF _Toc29696 88 HYPERLINK l _Toc3565 86.函數(shù)作用:獲取菜單ID編號(hào)及名稱(chēng)列表 PAGEREF _Toc3565 89 HYPERLINK l _Toc7689 87.函數(shù)作用:狀態(tài)列動(dòng)態(tài)顯示文字 PAGEREF _Toc7689 90 HYPERLINK l _Toc25597 88.函數(shù)作用:取得一個(gè)文件的路徑2 PAGEREF _Toc25597 90 HYPERLINK l _Toc16253 89.函數(shù)作用:取得一個(gè)文件的路徑3 PAGEREF _T

20、oc16253 90 HYPERLINK l _Toc13396 90.函數(shù)作用:取得Activecell的欄名 PAGEREF _Toc13396 91 HYPERLINK l _Toc20855 91.函數(shù)作用:取得單元格中指定字符前的字符 PAGEREF _Toc20855 91 HYPERLINK l _Toc4620 92.函數(shù)作用:前單元格指定字符前的字符顏色改成紅色 PAGEREF _Toc4620 91 HYPERLINK l _Toc10114 93.函數(shù)作用:根據(jù)數(shù)字返回對(duì)應(yīng)的字母列號(hào) PAGEREF _Toc10114 92 HYPERLINK l _Toc17390 9

21、4.函數(shù)作用:取工作表名字 PAGEREF _Toc17390 92 HYPERLINK l _Toc28911 95.函數(shù)作用:取消所有隱藏的宏表 PAGEREF _Toc28911 92 HYPERLINK l _Toc22798 96.函數(shù)作用:導(dǎo)出VBA Project代碼 PAGEREF _Toc22798 93 HYPERLINK l _Toc12667 97.函數(shù)作用:導(dǎo)入VBA Project代碼 PAGEREF _Toc12667 93 HYPERLINK l _Toc6980 98.函數(shù)作用:取得漢字拼音的第一個(gè)字母 PAGEREF _Toc6980 93 HYPERLIN

22、K l _Toc21610 99.函數(shù)作用:獲取兩欄中相同的數(shù)據(jù) PAGEREF _Toc21610 96 HYPERLINK l _Toc5736 100.函數(shù)作用:選取當(dāng)前工作表中公式出錯(cuò)的單元格關(guān)返回出錯(cuò)個(gè)數(shù) PAGEREF _Toc5736 97 HYPERLINK l _Toc21835 101.函數(shù)作用:將工作表中最后一列作為頁(yè)腳打印在每一面頁(yè)尾 PAGEREF _Toc21835 97 HYPERLINK l _Toc25215 102.函數(shù)作用:獲取vbproject引用工程 PAGEREF _Toc25215 98 HYPERLINK l _Toc5112 103.函數(shù)作用:

23、移除Excel工作表中的外部數(shù)據(jù)連接 PAGEREF _Toc5112 98 HYPERLINK l _Toc4155 104.函數(shù)作用:將選擇定單元格作成鏡像圖片 PAGEREF _Toc4155 99 HYPERLINK l _Toc21915 105.函數(shù)作用:反選擇單元格中的數(shù) PAGEREF _Toc21915 101 HYPERLINK l _Toc21107 106.函數(shù)作用:在Excel中參加一個(gè)量度尺(以厘米為單位) PAGEREF _Toc21107 102 HYPERLINK l _Toc17074 107.函數(shù)作用:在Excel中參加一個(gè)量度尺(以寸為單位) PAGER

24、EF _Toc17074 104 HYPERLINK l _Toc5259 108.函數(shù)作用:取得一個(gè)短文件名的長(zhǎng)文件名 PAGEREF _Toc5259 107 HYPERLINK l _Toc29297 109.函數(shù)作用:取得臨時(shí)文件名 PAGEREF _Toc29297 107 HYPERLINK l _Toc27869 110.函數(shù)作用:等用Shell調(diào)用的程序執(zhí)行完成后再執(zhí)行其它程序 PAGEREF _Toc27869 108 HYPERLINK l _Toc22000 111.函數(shù)作用:將Mouse顯示成動(dòng)畫(huà) PAGEREF _Toc22000 109 HYPERLINK l _T

25、oc5025 112.函數(shù)作用:限制Mouse移動(dòng)范圍 PAGEREF _Toc5025 109 HYPERLINK l _Toc10756 113.函數(shù)作用:取得當(dāng)前激活窗品句柄及標(biāo)題 PAGEREF _Toc10756 110 HYPERLINK l _Toc2537 114.函數(shù)作用:取得屏幕分辨率 PAGEREF _Toc2537 110 HYPERLINK l _Toc3260 115.函數(shù)作用:自動(dòng)建立多級(jí)目錄 PAGEREF _Toc3260 111 HYPERLINK l _Toc14765 116.函數(shù)作用:將文件長(zhǎng)度置零 PAGEREF _Toc14765 111 HYPE

26、RLINK l _Toc31695 117.函數(shù)作用:讀取WIN9X / Me共享文件夾密碼 PAGEREF _Toc31695 112 HYPERLINK l _Toc24312 118.函數(shù)作用:取得預(yù)設(shè)的打印機(jī)及設(shè)置預(yù)設(shè)的打印機(jī) PAGEREF _Toc24312 115 HYPERLINK l _Toc27526 119.函數(shù)作用:獲得當(dāng)前操作系統(tǒng)的打印機(jī)個(gè)數(shù)及檢測(cè)打印是否存在 PAGEREF _Toc27526 115 HYPERLINK l _Toc19376 120.函數(shù)作用:枚舉打印機(jī)名稱(chēng)清單 PAGEREF _Toc19376 116 HYPERLINK l _Toc2506

27、0 121.函數(shù)作用:讀取網(wǎng)絡(luò)效勞器當(dāng)前時(shí)間 PAGEREF _Toc25060 117 HYPERLINK l _Toc25247 122.函數(shù)作用:下載文件到指定目錄 PAGEREF _Toc25247 119 HYPERLINK l _Toc8217 123.函數(shù)作用:自動(dòng)映射網(wǎng)絡(luò)驅(qū)動(dòng)器 PAGEREF _Toc8217 120 HYPERLINK l _Toc19353 124.函數(shù)作用:自動(dòng)斷開(kāi)網(wǎng)絡(luò)驅(qū)動(dòng)器 PAGEREF _Toc19353 120 HYPERLINK l _Toc29827 125.函數(shù)作用:連接選定單元格中的內(nèi)容 PAGEREF _Toc29827 121 HYP

28、ERLINK l _Toc12787 126.函數(shù)作用:獲取一個(gè)單元格中有指定字體顏色部份數(shù)據(jù) PAGEREF _Toc12787 121 HYPERLINK l _Toc25037 127.函數(shù)作用:對(duì)指定文件加X(jué)LS加密 PAGEREF _Toc25037 122 HYPERLINK l _Toc10158 128.函數(shù)作用:選擇指定范圍內(nèi)使用了填充顏色的單元格 PAGEREF _Toc10158 122 HYPERLINK l _Toc27627 129.函數(shù)作用:在特定的區(qū)域內(nèi)查找文本,返回值是包含查找文本的單元格 PAGEREF _Toc27627 123 HYPERLINK l _

29、Toc23300 130.函數(shù)作用:返回特定區(qū)域中最大值的地址 PAGEREF _Toc23300 124 HYPERLINK l _Toc8702 131.函數(shù)作用:刪除表格中使用范圍內(nèi)的所有空白單元格 PAGEREF _Toc8702 124 HYPERLINK l _Toc31022 132.函數(shù)作用:返回?cái)?shù)組中有多少個(gè)指定的字符串 PAGEREF _Toc31022 125 HYPERLINK l _Toc29808 133.函數(shù)作用:返回當(dāng)前工作表中引用了指定的單元的地址 PAGEREF _Toc29808 126 HYPERLINK l _Toc12455 134.函數(shù)作用:獲取E

30、xcel中字型列表 PAGEREF _Toc12455 126 HYPERLINK l _Toc8382 135.函數(shù)作用:獲取一個(gè)字符串中有多少個(gè)數(shù)字字符 PAGEREF _Toc8382 127 HYPERLINK l _Toc5685 136.函數(shù)作用:在Excel中對(duì)多列進(jìn)行填充 PAGEREF _Toc5685 127 HYPERLINK l _Toc2254 137.函數(shù)作用:對(duì)選定的范圍進(jìn)行數(shù)據(jù)填充忽略單元格格式 PAGEREF _Toc2254 127 HYPERLINK l _Toc2937 138.函數(shù)作用:VBA Project加密及解密 PAGEREF _Toc2937

31、 128 HYPERLINK l _Toc14791 139.函數(shù)作用:列出收藏夾中的網(wǎng)址 PAGEREF _Toc14791 129 HYPERLINK l _Toc31455 140.函數(shù)作用:計(jì)算兩個(gè)日期之間相隔的年份,比方年齡,工齡等.可計(jì)算從1000年01月01日起的日期 PAGEREF _Toc31455 130 HYPERLINK l _Toc18109 141.函數(shù)作用:從字符串提取純數(shù)字 PAGEREF _Toc18109 131 HYPERLINK l _Toc7200 142.函數(shù)作用:將一個(gè)數(shù)組按升序排列 PAGEREF _Toc7200 132 HYPERLINK l

32、 _Toc32179 143.函數(shù)作用:將一個(gè)數(shù)組按降序排列 PAGEREF _Toc32179 132 HYPERLINK l _Toc19888 144.函數(shù)作用:刪除空白列 PAGEREF _Toc19888 133 HYPERLINK l _Toc29125 145.函數(shù)作用:判斷工作表是否為空白 PAGEREF _Toc29125 133 HYPERLINK l _Toc13012 146.函數(shù)作用:將數(shù)據(jù)按類(lèi)分到不同工作薄 PAGEREF _Toc13012 134 HYPERLINK l _Toc27504 147.函數(shù)作用:單元格內(nèi)數(shù)據(jù)排序 PAGEREF _Toc27504

33、134 HYPERLINK l _Toc820 148.函數(shù)作用:對(duì)多欄排序 PAGEREF _Toc820 135 HYPERLINK l _Toc6228 149.函數(shù)作用:返回計(jì)算公式的值 ,值的計(jì)算公式 PAGEREF _Toc6228 136 HYPERLINK l _Toc8223 150.函數(shù)作用:把第一列=某個(gè)值對(duì)應(yīng)的第二列的內(nèi)容連在一起，并用、隔開(kāi) PAGEREF _Toc8223 137 HYPERLINK l _Toc24046 151.函數(shù)作用:取得系統(tǒng)使用模式 PAGEREF _Toc24046 137 HYPERLINK l _Toc25826 152.函數(shù)作用:計(jì)

34、算機(jī)注銷(xiāo)/關(guān)機(jī)/重啟 PAGEREF _Toc25826 138 HYPERLINK l _Toc3854 153.函數(shù)作用:更改計(jì)算機(jī)名稱(chēng) PAGEREF _Toc3854 138 HYPERLINK l _Toc4832 154.函數(shù)作用:從n位開(kāi)始取出字符串中的漢字、英文字母、數(shù)字 PAGEREF _Toc4832 139 HYPERLINK l _Toc538 155.函數(shù)作用:在指定列中尋找含有指定字符串的單元格，并將符合條件的單元格標(biāo)為紅色，并將對(duì)應(yīng)的下一列單元格賦值為1。 PAGEREF _Toc538 140 HYPERLINK l _Toc32582 156.函數(shù)作用:去除字

35、符串中的空格 PAGEREF _Toc32582 140 HYPERLINK l _Toc13924 157.函數(shù)作用:查找合并單元格位置 PAGEREF _Toc13924 141 HYPERLINK l _Toc6038 158.函數(shù)作用:陰陽(yáng)歷轉(zhuǎn)換和陰陽(yáng)歷生日 PAGEREF _Toc6038 141 HYPERLINK l _Toc26896 159.函數(shù)作用:利用數(shù)組和Substitute來(lái)替換某字符 PAGEREF _Toc26896 145 HYPERLINK l _Toc15200 160.函數(shù)作用:一鍵創(chuàng)立斜線表頭 PAGEREF _Toc15200 145 HYPERLIN

36、K l _Toc15976 作用:自動(dòng)獲取指定月的工作日 PAGEREF _Toc15976 146正文ByVal 表示該參數(shù)是按值方式傳遞的。 你在函數(shù)中修改此參數(shù)的值不會(huì)造成調(diào)用這個(gè)函數(shù)的函數(shù)的變量值的改變。ByRef 表示該參數(shù)按引用方式傳遞。 在函數(shù)中修改此參數(shù)的值會(huì)造成實(shí)參的值發(fā)生改變。例如：dim I as integer, J as integerI = 1 : J = 2call fun(I,J)Msgbox I= & Cstr(i) & J= & cstr(J)Function Fun(byref a, byval b) a = 5 b = 6 fun = 0函數(shù)返回值End

37、 Function將會(huì)輸出I=5 J=2，即I的值被函數(shù)Fun改變了。 #1.函數(shù)作用：返回 Column 英文字#Function ColLetter(ColNumber As Integer) As String On Error GoTo Errorhandler ColLetter = Left(Cells(1, ColNumber).Address(0, 0), 1 - (ColNumber 26) Exit FunctionErrorhandler: MsgBox Error encountered, please re-enter End Function#2.函數(shù)作用：查詢某一

38、值第num次出現(xiàn)的值 參數(shù)說(shuō)明：Value1：查詢引用的數(shù)值; Range1：查詢區(qū)域; num：指定查詢第幾次出現(xiàn); Col：返回值, 相對(duì)引用區(qū)域, 相對(duì)引用列的右數(shù)第Col列#Function MyFind(Value1, ByVal Range1 As Range, ByVal num As Integer, ByVal Col As Integer) If Value1 = Then Exit Function If Range1.Columns.Count 1 Then Exit Function For Each D In Range1 If D.Value = Value1 T

39、hen c = c + 1 If c = num Then v1 = D(1, Col) Exit For End If ElseIf IsEmpty(D) Then Exit For End If Next If v1 = Then v1 = not MyFind = v1End Function#3.函數(shù)作用：返回當(dāng)個(gè)人工資薪金所得為2000元(起征點(diǎn)為850元)時(shí)的應(yīng)納個(gè)人所得稅稅額 語(yǔ) 法：Grsds(bsc, mysala) 參數(shù)說(shuō)明：bsc: 必選項(xiàng)，為起征點(diǎn)，包括稅法規(guī)定的工資基數(shù)800元加上允許稅前扣除的合理費(fèi)用; mysala: 必選項(xiàng)，為人個(gè)工資薪金所得。 示 例：Grsd

40、s(850, 20000) =#Function Grsds(bsc As Double, mysala As Double) As Double bsc為起征點(diǎn)加上允許稅前扣除的合理費(fèi)用,mysala為工資薪金所得 On Error GoTo Grsds_err Select Case mysala Case Is = bsc Grsds = 0 Case Is = bsc + 500 Grsds = Application.WorksheetFunction.Round(mysala - bsc) * 0.05, 2) Case Is = bsc + 2000 Grsds = Applic

41、ation.WorksheetFunction.Round(mysala - bsc) * 0.1 - 25, 2) Case Is = bsc + 5000 Grsds = Application.WorksheetFunction.Round(mysala - bsc) * 0.15 - 125, 2) Case Is = bsc + 20000 Grsds = Application.WorksheetFunction.Round(mysala - bsc) * 0.2 - 375, 2) Case Is = bsc + 40000 Grsds = Application.Workshe

42、etFunction.Round(mysala - bsc) * 0.25 - 1375, 2) Case Is = bsc + 60000 Grsds = Application.WorksheetFunction.Round(mysala - bsc) * 0.3 - 3375, 2) Case Is = bsc + 80000 Grsds = Application.WorksheetFunction.Round(mysala - bsc) * 0.35 - 6375, 2) Case Is 0 i = i + 1 Loop mydata = Val(Mid(mystring, i, L

43、en(mystring) - i + 1)End Function#6.函數(shù)作用：按SplitType取得RangeName串值中的起始位置#1:單元格，2:行號(hào)，3:列號(hào)，4:范圍Public Const SINGLE_CELL = 1Public Const ROW_NUM = 2Public Const COL_NUM = 3Public Const RANGE_ALL = 4Public Function SplitRangeName(RangeName As String, SplitType As Integer) As String If VBA.Len(RangeName) 0

44、 Then RangeName = VBA.Left(RangeName, VBA.InStr(1, RangeName, :) - 1) Select Case SplitType Case SINGLE_CELL If VBA.InStr(1, RangeName, :) 0 Then SplitRangeName = $ & VBA.Left(RangeName, VBA.InStr(1, RangeName, :) - 1) Else SplitRangeName = $ & RangeName End If Case ROW_NUM SplitRangeName = VBA.IIf(

45、VBA.InStr(1, RangeName, $) 0, VBA.Right(RangeName, VBA.Len(RangeName) - VBA.InStr(1, RangeName, $), RangeName) If Not IsNumeric(SplitRangeName) Then SplitRangeName = MsgBox , vbInformation, End If Case COL_NUM If VBA.InStr(1, RangeName, $) 0 Then SplitRangeName = VBA.Left(RangeName, VBA.InStr(1, Ran

46、geName, $) - 1) Else SplitRangeName = RangeName End If If IsNumeric(SplitRangeName) Then SplitRangeName = MsgBox , vbInformation, End If Case RANGE_ALL SplitRangeName = $ & RangeName End Select End IfEnd Function#7.函數(shù)作用:將金額數(shù)字轉(zhuǎn)成中文大寫(xiě)#Function Money(Number As Currency) Dim i, j, k, m, leng As Integer 計(jì)

47、數(shù)器 Dim Zero As Integer 連續(xù)零標(biāo)識(shí) Dim Tnumber As String 儲(chǔ)存數(shù)字字符串,計(jì)算數(shù)組長(zhǎng)度 Dim Num() As String 定義數(shù)組 Dim Num1(3) As String 存儲(chǔ)萬(wàn)元以下數(shù)字 Dim Num2(1) As String 儲(chǔ)存拆分后的數(shù)字 Dim Cha(8), Cha1(9), Cha2(4) As String 儲(chǔ)存轉(zhuǎn)化后的漢字 Dim Zcha As String 連接后的字符串 Dim Flag, Flag1 As Boolean 正負(fù)標(biāo)志 Flag = True Flag1 = False Zero = 0 如果大于一

48、億，那么不處理 If (Number 99999999) Or (Number -99999999) Then MsgBox (Sorry,數(shù)據(jù)超過(guò)一億，暫不處理。) MsgBox (順便問(wèn)一下，你真有那么多錢(qián)嗎？) Money = Sorry! Else If (Number = 0) Then Money = 零元整 Else *將負(fù)數(shù)數(shù)字轉(zhuǎn)化正數(shù)并更改標(biāo)識(shí)* If (Number 0) Then Tnumber = CStr(Int(Number * 100) / 100) Else Tnumber = CStr(Number) End If *處理四舍五入* If (Number -

49、Int(Number) * 100 - Int(Number - Int(Number) * 100) = 0.5) Then Tnumber = CStr(CCur(Tnumber) + 0.01) End If Number = CCur(Tnumber) *重新分配數(shù)組空間* ReDim Num(Len(Tnumber) - 1) As String *將字符串分開(kāi)存儲(chǔ)至數(shù)組中* For i = 0 To Len(Tnumber) - 1 Num(i) = Mid(Tnumber, i + 1, 1) Next i *定義所需字符* Dim M1, M2 M1 = Array(零, 壹,

50、 貳, 叁, 肆, 伍, 陸, 柒, 捌, 玖) M2 = Array(, 拾, 佰, 仟, 萬(wàn), 億) *處理小于一元金額* *小數(shù)點(diǎn)后一位，那么* If (Number - Int(Number) 0) And (Number * 100 - Int(Number) * 100) Mod 10) = 0) Then i = i - 1 Num2(0) = Num(i) Num(i) = i = i - 1 Num(i) = i = i - 1 Cha2(0) = M1(CByte(Num2(0) Cha2(1) = 角 Cha2(2) = 整 Else *小數(shù)點(diǎn)后兩位那么* If (Num

51、ber - Int(Number) 0) Then i = i - 1 Num2(1) = Num(i) Num2(0) = Num(i - 1) Num(i) = i = i - 1 Num(i) = i = i - 1 Num(i) = i = i - 1 Cha2(0) = M1(CByte(Num2(0) Cha2(1) = 角 Cha2(2) = M1(CByte(Num2(1) Cha2(3) = 分 End If End If *分解大于一萬(wàn)的整數(shù)局部* If (Int(Number) 9999) Then If (Cha2(0) ) Then i = i + 1 End If

52、For j = 3 To 0 Step -1 Num1(j) = Num(i - 1) Num(i - 1) = i = i - 1 Next j Else If (Cha2(0) ) Then i = i + 1 End If For j = 0 To i - 1 Num1(j) = Num(j) Num(j) = Next j End If *轉(zhuǎn)換萬(wàn)元以上數(shù)字* If (Num(0) ) Then leng = i j = 0 For k = 0 To leng - 1 If (Num(k) = 0) Then Zero = Zero + 1 For m = 1 To 5 If (Cha(

53、j - 1) = M2(m) Then Flag1 = True End If Next m If (Zero = 1) And (Flag1 = False) Then Cha(j) = M1(CByte(Num(k) End If If (Zero = 1) Then j = j + 1 End If Else If (Num(k) ) Then If (Zero 0) Then Cha(j - 1) = 零 End If Cha(j) = M1(CByte(Num(k) End If j = j + 1 End If If (Num(k) = 0) Then i = i - 1 Else

54、 Cha(j) = M2(i - 1) j = j + 1 i = i - 1 Zero = 0 End If Next k Cha(j - 1) = 萬(wàn) Zero = 0 End If *轉(zhuǎn)換萬(wàn)元以下數(shù)字* If (Num1(0) ) Then j = 0 Flag1 = False leng = 3 While (Num1(leng) = ) leng = leng - 1 Wend i = leng + 1 For k = 0 To leng If (Num1(k) ) Then If (Num1(k) = 0) Then Zero = Zero + 1 For m = 1 To 5 I

55、f (j 0) Then If (Cha1(j - 1) = M2(m) Then Flag1 = True End If End If Next m If (Zero = 1) And (Flag1 = False) Then Cha1(j) = M1(CByte(Num1(k) End If If (Zero = 1) Then j = j + 1 End If Else If (Num1(k) ) Then If (Zero 0) Then Cha1(j - 1) = 零 End If Cha1(j) = M1(CByte(Num1(k) End If j = j + 1 End If

56、If (Num1(k) = 0) Then i = i - 1 Else Cha1(j) = M2(i - 1) j = j + 1 i = i - 1 Zero = 0 End If End If Next k Cha1(j - 1) = 元 If (Cha2(0) = ) Then Cha1(j) = 整 End If End If *連接字符串* j = 0 While (Cha(j) ) Zcha = Zcha & Cha(j) j = j + 1 Wend j = 0 While (Cha1(j) ) Zcha = Zcha & Cha1(j) j = j + 1 Wend j =

57、0 While (Cha2(j) ) Zcha = Zcha & Cha2(j) j = j + 1 Wend *最終顯示* If (Flag) Then Money = Zcha Else Money = 負(fù) & Zcha End If End If End IfEnd Function#8.函數(shù)作用:計(jì)算某種稅金#Public Function 稅(fa) Dim x If (fa - 800) 0 And (fa - 800) = 500 And (fa - 800) = 2000 And (fa - 800) = 5000 And (fa - 800) = 20000 And (fa

58、- 800) = 40000 And (fa - 800) = 60000 And (fa - 800) = 80000 And (fa - 800) = 100000 Then x = (fa - 800) * 0.45 - 15375 稅 = x Else End IfEnd Function#9.函數(shù)作用:人民幣大、小寫(xiě)轉(zhuǎn)換#Function 小寫(xiě)(k) Application.ScreenUpdating = False m1 = Application.WorksheetFunction.Round(k * 100, 0) n1 = Int(m1 / 100) n2 = Int(m1

59、 / 10) - n1 * 10 n3 = m1 - n1 * 100 - n2 * 10 e = Application.WorksheetFunction.Text(n1, DBNum1) f = Application.WorksheetFunction.Text(n2, DBNum1) g = Application.WorksheetFunction.Text(n3, DBNum1) If n3 = 0 Then 小寫(xiě) = 人民幣大寫(xiě)： & e & 元 & 整 End If If (n3 0) And (n2 0) Then 小寫(xiě) = 人民幣大寫(xiě)： & e & 元 & f & 角 &

60、 g & 分 If n1 = 0 Then 小寫(xiě) = 人民幣大寫(xiě)： & f & 角 & g & 分 End If End If If (n3 = 0) And n2 0 Then 小寫(xiě) = 人民幣大寫(xiě)： & e & 元 & f & 角 & 整 If n1 = 0 Then 小寫(xiě) = 人民幣大寫(xiě)： & f & 角 & 整 End If End If If (n3 0) And (n2 = 0) Then 小寫(xiě) = 人民幣大寫(xiě)： & e & 元 & g & 分 If n1 = 0 Then 小寫(xiě) = 人民幣大寫(xiě)： & g & 分 End If End If If k = 0 Or k = Then