1、VBA自定義函數(shù)大全龍族聯(lián)盟論壇shcnmartin收集整理目錄 TOC o 1-3 h z u HYPERLINK l _Toc290228862 1.函數(shù)作用：返回 Column 英文字 PAGEREF _Toc290228862 h 6 HYPERLINK l _Toc290228863 2.函數(shù)作用：查詢某一值第num次出現(xiàn)的值 PAGEREF _Toc290228863 h 6 HYPERLINK l _Toc290228864 3.函數(shù)作用：返回當(dāng)個(gè)人工資薪金所得為2000元(起征點(diǎn)為850元)時(shí)的應(yīng)納個(gè)人所得稅稅額 PAGEREF _Toc290228864 h 6 HYPERL

2、INK l _Toc290228865 4.函數(shù)作用：從形如123545ABCDE的字符串中取出數(shù)字 PAGEREF _Toc290228865 h 8 HYPERLINK l _Toc290228866 5.函數(shù)作用：從形如ABCD12455EDF的字符串中取出數(shù)字 PAGEREF _Toc290228866 h 8 HYPERLINK l _Toc290228867 6.函數(shù)作用：按SplitType取得RangeName串值中的起始位置 PAGEREF _Toc290228867 h 8 HYPERLINK l _Toc290228868 7.函數(shù)作用:將金額數(shù)字轉(zhuǎn)成中文大寫 PAGER

3、EF _Toc290228868 h 9 HYPERLINK l _Toc290228869 8.函數(shù)作用:計(jì)算某種稅金 PAGEREF _Toc290228869 h 14 HYPERLINK l _Toc290228870 9.函數(shù)作用:人民幣大、小寫轉(zhuǎn)換 PAGEREF _Toc290228870 h 15 HYPERLINK l _Toc290228871 10.函數(shù)作用:查漢字區(qū)位碼 PAGEREF _Toc290228871 h 17 HYPERLINK l _Toc290228872 11.函數(shù)作用:把公元年轉(zhuǎn)為農(nóng)歷 PAGEREF _Toc290228872 h 17 HYPE

4、RLINK l _Toc290228873 12.函數(shù)作用:返回指定列數(shù)的列標(biāo) PAGEREF _Toc290228873 h 40 HYPERLINK l _Toc290228874 13.函數(shù)作用:用指定字符替換某字符 PAGEREF _Toc290228874 h 40 HYPERLINK l _Toc290228875 14.函數(shù)作用:從右邊開始查找指定字符在字符串中的位置 PAGEREF _Toc290228875 h 41 HYPERLINK l _Toc290228876 15.函數(shù)作用:從右邊開始查找指定字符在字符串中的位置 PAGEREF _Toc290228876 h 41

5、 HYPERLINK l _Toc290228877 16.函數(shù)作用:計(jì)算工齡 PAGEREF _Toc290228877 h 42 HYPERLINK l _Toc290228878 17.函數(shù)作用:計(jì)算日期差，除去星期六、星期日 PAGEREF _Toc290228878 h 43 HYPERLINK l _Toc290228879 18.函數(shù)作用:將英文字反轉(zhuǎn)的自定函數(shù). PAGEREF _Toc290228879 h 43 HYPERLINK l _Toc290228880 19.函數(shù)作用:計(jì)算個(gè)人所得稅 PAGEREF _Toc290228880 h 43 HYPERLINK l _

6、Toc290228881 20.函數(shù)作用:一個(gè)能計(jì)算是否有重復(fù)單元的函數(shù) PAGEREF _Toc290228881 h 44 HYPERLINK l _Toc290228882 21.數(shù)字金額轉(zhuǎn)中文大寫 PAGEREF _Toc290228882 h 45 HYPERLINK l _Toc290228883 22.函數(shù)作用:將數(shù)字轉(zhuǎn)成英文 PAGEREF _Toc290228883 h 46 HYPERLINK l _Toc290228884 23.函數(shù)作用:人民幣大小寫轉(zhuǎn)換 PAGEREF _Toc290228884 h 50 HYPERLINK l _Toc290228885 24.函數(shù)

7、作用:獲取區(qū)域顏色值 PAGEREF _Toc290228885 h 50 HYPERLINK l _Toc290228886 25.函數(shù)作用:獲取活開工作表名 PAGEREF _Toc290228886 h 50 HYPERLINK l _Toc290228887 26.函數(shù)作用:獲取最后一行行數(shù) PAGEREF _Toc290228887 h 51 HYPERLINK l _Toc290228888 27.函數(shù)作用:判斷是否連接在線 PAGEREF _Toc290228888 h 51 HYPERLINK l _Toc290228889 28.函數(shù)作用:幣種轉(zhuǎn)換 PAGEREF _Toc2

8、90228889 h 51 HYPERLINK l _Toc290228890 29.函數(shù)作用:檢驗(yàn)工作表是否有可打印內(nèi)容 PAGEREF _Toc290228890 h 52 HYPERLINK l _Toc290228891 30.函數(shù)作用:查找一字符串(withinstr)在另一字符串中(findstr1)中某一次(startnum)出現(xiàn)時(shí)的位置，返回零表示沒找到。 PAGEREF _Toc290228891 h 54 HYPERLINK l _Toc290228892 31.函數(shù)作用:增加文件路徑最后的“符號(hào) PAGEREF _Toc290228892 h 55 HYPERLINK l

9、 _Toc290228893 32.函數(shù)作用:計(jì)算所得稅 PAGEREF _Toc290228893 h 55 HYPERLINK l _Toc290228894 33.函數(shù)作用:從工作表第一行的標(biāo)題文字以數(shù)字形式返回所在列號(hào) PAGEREF _Toc290228894 h 55 HYPERLINK l _Toc290228895 34.函數(shù)作用:在多個(gè)工作表中查找一個(gè)范圍內(nèi)符合某個(gè)指定條件的工程對(duì)應(yīng)指定范圍加總求和 PAGEREF _Toc290228895 h 56 HYPERLINK l _Toc290228896 35.函數(shù)作用:返回 Column 英文字 PAGEREF _Toc29

10、0228896 h 56 HYPERLINK l _Toc290228897 36.函數(shù)作用:查找指定列名的列數(shù) PAGEREF _Toc290228897 h 57 HYPERLINK l _Toc290228898 37.函數(shù)作用:文字格式的時(shí)間(分:秒)轉(zhuǎn)化為數(shù)字格式(秒) PAGEREF _Toc290228898 h 58 HYPERLINK l _Toc290228899 38.函數(shù)作用:將hh:mm:ss格式的時(shí)分秒數(shù)轉(zhuǎn)換成秒數(shù) PAGEREF _Toc290228899 h 58 HYPERLINK l _Toc290228900 39.函數(shù)作用:金額中文大寫轉(zhuǎn)數(shù)字 PAGER

11、EF _Toc290228900 h 59 HYPERLINK l _Toc290228901 40.函數(shù)作用:把角度轉(zhuǎn)為度秒分、弧度等顯示 PAGEREF _Toc290228901 h 60 HYPERLINK l _Toc290228902 41.函數(shù)作用:身份證號(hào)碼偵測(cè) PAGEREF _Toc290228902 h 60 HYPERLINK l _Toc290228903 42.函數(shù)作用:顯示公式 PAGEREF _Toc290228903 h 62 HYPERLINK l _Toc290228904 43.函數(shù)作用:方便財(cái)務(wù)人員理帳查找 PAGEREF _Toc290228904

12、h 62 HYPERLINK l _Toc290228905 44.函數(shù)作用:數(shù)值轉(zhuǎn)換為字符地址 PAGEREF _Toc290228905 h 65 HYPERLINK l _Toc290228906 45.函數(shù)作用:字符地址轉(zhuǎn)換為數(shù)值 PAGEREF _Toc290228906 h 65 HYPERLINK l _Toc290228907 46.函數(shù)作用:等待時(shí)間以秒計(jì)算 PAGEREF _Toc290228907 h 66 HYPERLINK l _Toc290228908 47.函數(shù)作用:得到字符串實(shí)際的長(zhǎng)度以單字節(jié)記 PAGEREF _Toc290228908 h 66 HYPERL

13、INK l _Toc290228909 48.函數(shù)作用:18位身份證最后一位有效性驗(yàn)證 PAGEREF _Toc290228909 h 67 HYPERLINK l _Toc290228910 49.函數(shù)作用:計(jì)算符合maturity condition的拆解金額 PAGEREF _Toc290228910 h 68 HYPERLINK l _Toc290228911 50.函數(shù)作用:對(duì)多個(gè)用同一分隔符分隔的待查找元素，逐一在表區(qū)域首列內(nèi)搜索，將返回選定單元格的值相加， PAGEREF _Toc290228911 h 69 HYPERLINK l _Toc290228912 51.函數(shù)作用:根

14、據(jù)個(gè)人所得稅工資反算工資數(shù) PAGEREF _Toc290228912 h 70 HYPERLINK l _Toc290228913 52.函數(shù)作用:判斷表是否存在 PAGEREF _Toc290228913 h 70 HYPERLINK l _Toc290228914 53.函數(shù)作用:角度轉(zhuǎn)弧 PAGEREF _Toc290228914 h 71 HYPERLINK l _Toc290228915 54.函數(shù)作用:比擬相同的字符串 PAGEREF _Toc290228915 h 71 HYPERLINK l _Toc290228916 55.函數(shù)作用:對(duì)選定的數(shù)組進(jìn)行排序 PAGEREF _

15、Toc290228916 h 72 HYPERLINK l _Toc290228917 56.函數(shù)作用:取得指定月份天數(shù) PAGEREF _Toc290228917 h 73 HYPERLINK l _Toc290228918 57.函數(shù)作用:排序工作表活頁薄 PAGEREF _Toc290228918 h 73 HYPERLINK l _Toc290228919 58.函數(shù)作用:統(tǒng)計(jì)數(shù)組中非重復(fù)數(shù)據(jù)個(gè)數(shù) PAGEREF _Toc290228919 h 74 HYPERLINK l _Toc290228920 59.函數(shù)作用:摘取子字符串 PAGEREF _Toc290228920 h 74

16、HYPERLINK l _Toc290228921 60.函數(shù)作用:計(jì)算20000余個(gè)漢字的筆畫 PAGEREF _Toc290228921 h 75 HYPERLINK l _Toc290228922 61.函數(shù)作用:刪除當(dāng)前工作表中的全部超連接 PAGEREF _Toc290228922 h 76 HYPERLINK l _Toc290228923 62.函數(shù)作用:取得相近數(shù)據(jù) PAGEREF _Toc290228923 h 76 HYPERLINK l _Toc290228924 63.函數(shù)作用:提取定串中漢字 PAGEREF _Toc290228924 h 77 HYPERLINK l

17、 _Toc290228925 64.函數(shù)作用:搜索重復(fù)數(shù)據(jù)(選定范圍) PAGEREF _Toc290228925 h 77 HYPERLINK l _Toc290228926 65.函數(shù)作用:字符型轉(zhuǎn)數(shù)字型 PAGEREF _Toc290228926 h 78 HYPERLINK l _Toc290228927 66.函數(shù)作用:小寫人民幣轉(zhuǎn)大寫人民幣 PAGEREF _Toc290228927 h 78 HYPERLINK l _Toc290228928 67.函數(shù)作用:取得指定月份人星期天個(gè)數(shù) PAGEREF _Toc290228928 h 79 HYPERLINK l _Toc29022

18、8929 68.函數(shù)作用:偵測(cè)檔案是否包含宏 PAGEREF _Toc290228929 h 80 HYPERLINK l _Toc290228930 69.函數(shù)作用:獲取循環(huán)參照單元格 PAGEREF _Toc290228930 h 81 HYPERLINK l _Toc290228931 70.函數(shù)作用:創(chuàng)立桌面快捷方式 PAGEREF _Toc290228931 h 81 HYPERLINK l _Toc290228932 71.函數(shù)作用:自動(dòng)建立多級(jí)目錄 PAGEREF _Toc290228932 h 82 HYPERLINK l _Toc290228933 72.函數(shù)作用:統(tǒng)計(jì)經(jīng)篩選

19、后符合條件的記錄條數(shù) PAGEREF _Toc290228933 h 82 HYPERLINK l _Toc290228934 73.函數(shù)作用:復(fù)制單元格列高與欄寬 PAGEREF _Toc290228934 h 83 HYPERLINK l _Toc290228935 74.函數(shù)作用:取消隱藏工作表包括vba Project工程保護(hù)的) PAGEREF _Toc290228935 h 83 HYPERLINK l _Toc290228936 75.函數(shù)作用:刪除單元格自定義名稱 PAGEREF _Toc290228936 h 84 HYPERLINK l _Toc290228937 76.函

20、數(shù)作用:從文件路徑中取得文件名 PAGEREF _Toc290228937 h 84 HYPERLINK l _Toc290228938 77.函數(shù)作用:取得一個(gè)文件的擴(kuò)展名 PAGEREF _Toc290228938 h 84 HYPERLINK l _Toc290228939 78.函數(shù)作用:取得一個(gè)文件的路徑 PAGEREF _Toc290228939 h 85 HYPERLINK l _Toc290228940 79.函數(shù)作用:十進(jìn)制轉(zhuǎn)二進(jìn)制 PAGEREF _Toc290228940 h 85 HYPERLINK l _Toc290228941 80.函數(shù)作用:檢查一個(gè)數(shù)組是否為空

21、PAGEREF _Toc290228941 h 85 HYPERLINK l _Toc290228942 81.函數(shù)作用:字母欄名轉(zhuǎn)數(shù)字欄名 PAGEREF _Toc290228942 h 86 HYPERLINK l _Toc290228943 82.函數(shù)作用:數(shù)字欄名轉(zhuǎn)文字欄名 PAGEREF _Toc290228943 h 86 HYPERLINK l _Toc290228944 83.函數(shù)作用:判斷一件活頁夾中是否還有子目錄 PAGEREF _Toc290228944 h 86 HYPERLINK l _Toc290228945 84.函數(shù)作用:判斷一個(gè)文件是否在使用中 PAGEREF

22、 _Toc290228945 h 87 HYPERLINK l _Toc290228946 85.函數(shù)作用:列出檔案詳細(xì)摘要信息 PAGEREF _Toc290228946 h 87 HYPERLINK l _Toc290228947 86.函數(shù)作用:獲取菜單ID編號(hào)及名稱列表 PAGEREF _Toc290228947 h 88 HYPERLINK l _Toc290228948 87.函數(shù)作用:狀態(tài)列動(dòng)態(tài)顯示文字 PAGEREF _Toc290228948 h 88 HYPERLINK l _Toc290228949 88.函數(shù)作用:取得一個(gè)文件的路徑2 PAGEREF _Toc29022

23、8949 h 89 HYPERLINK l _Toc290228950 89.函數(shù)作用:取得一個(gè)文件的路徑3 PAGEREF _Toc290228950 h 89 HYPERLINK l _Toc290228951 90.函數(shù)作用:取得Activecell的欄名 PAGEREF _Toc290228951 h 89 HYPERLINK l _Toc290228952 91.函數(shù)作用:取得單元格中指定字符前的字符 PAGEREF _Toc290228952 h 89 HYPERLINK l _Toc290228953 92.函數(shù)作用:前單元格指定字符前的字符顏色改成紅色 PAGEREF _Toc

24、290228953 h 90 HYPERLINK l _Toc290228954 93.函數(shù)作用:根據(jù)數(shù)字返回對(duì)應(yīng)的字母列號(hào) PAGEREF _Toc290228954 h 90 HYPERLINK l _Toc290228955 94.函數(shù)作用:取工作表名字 PAGEREF _Toc290228955 h 90 HYPERLINK l _Toc290228956 95.函數(shù)作用:取消所有隱藏的宏表 PAGEREF _Toc290228956 h 91 HYPERLINK l _Toc290228957 96.函數(shù)作用:導(dǎo)出VBA Project代碼 PAGEREF _Toc290228957

25、 h 91 HYPERLINK l _Toc290228958 97.函數(shù)作用:導(dǎo)入VBA Project代碼 PAGEREF _Toc290228958 h 91 HYPERLINK l _Toc290228959 98.函數(shù)作用:取得漢字拼音的第一個(gè)字母 PAGEREF _Toc290228959 h 91 HYPERLINK l _Toc290228960 99.函數(shù)作用:獲取兩欄中相同的數(shù)據(jù) PAGEREF _Toc290228960 h 94 HYPERLINK l _Toc290228961 100.函數(shù)作用:選取當(dāng)前工作表中公式出錯(cuò)的單元格關(guān)返回出錯(cuò)個(gè)數(shù) PAGEREF _Toc

26、290228961 h 95 HYPERLINK l _Toc290228962 101.函數(shù)作用:將工作表中最后一列作為頁腳打印在每一面頁尾 PAGEREF _Toc290228962 h 95 HYPERLINK l _Toc290228963 102.函數(shù)作用:獲取vbproject引用工程 PAGEREF _Toc290228963 h 96 HYPERLINK l _Toc290228964 103.函數(shù)作用:移除Excel工作表中的外部數(shù)據(jù)連接 PAGEREF _Toc290228964 h 96 HYPERLINK l _Toc290228965 104.函數(shù)作用:將選擇定單元格

27、作成鏡像圖片 PAGEREF _Toc290228965 h 97 HYPERLINK l _Toc290228966 105.函數(shù)作用:反選擇單元格中的數(shù) PAGEREF _Toc290228966 h 99 HYPERLINK l _Toc290228967 106.函數(shù)作用:在Excel中參加一個(gè)量度尺(以厘米為單位) PAGEREF _Toc290228967 h 100 HYPERLINK l _Toc290228968 107.函數(shù)作用:在Excel中參加一個(gè)量度尺(以寸為單位) PAGEREF _Toc290228968 h 102 HYPERLINK l _Toc2902289

28、69 108.函數(shù)作用:取得一個(gè)短文件名的長(zhǎng)文件名 PAGEREF _Toc290228969 h 105 HYPERLINK l _Toc290228970 109.函數(shù)作用:取得臨時(shí)文件名 PAGEREF _Toc290228970 h 105 HYPERLINK l _Toc290228971 110.函數(shù)作用:等用Shell調(diào)用的程序執(zhí)行完成后再執(zhí)行其它程序 PAGEREF _Toc290228971 h 106 HYPERLINK l _Toc290228972 111.函數(shù)作用:將Mouse顯示成動(dòng)畫 PAGEREF _Toc290228972 h 107 HYPERLINK l

29、_Toc290228973 112.函數(shù)作用:限制Mouse移動(dòng)范圍 PAGEREF _Toc290228973 h 107 HYPERLINK l _Toc290228974 113.函數(shù)作用:取得當(dāng)前激活窗品句柄及標(biāo)題 PAGEREF _Toc290228974 h 108 HYPERLINK l _Toc290228975 114.函數(shù)作用:取得屏幕分辨率 PAGEREF _Toc290228975 h 108 HYPERLINK l _Toc290228976 115.函數(shù)作用:自動(dòng)建立多級(jí)目錄 PAGEREF _Toc290228976 h 108 HYPERLINK l _Toc2

30、90228977 116.函數(shù)作用:將文件長(zhǎng)度置零 PAGEREF _Toc290228977 h 109 HYPERLINK l _Toc290228978 117.函數(shù)作用:讀取WIN9X / Me共享文件夾密碼 PAGEREF _Toc290228978 h 109 HYPERLINK l _Toc290228979 118.函數(shù)作用:取得預(yù)設(shè)的打印機(jī)及設(shè)置預(yù)設(shè)的打印機(jī) PAGEREF _Toc290228979 h 112 HYPERLINK l _Toc290228980 119.函數(shù)作用:獲得當(dāng)前操作系統(tǒng)的打印機(jī)個(gè)數(shù)及檢測(cè)打印是否存在 PAGEREF _Toc290228980 h

31、 113 HYPERLINK l _Toc290228981 120.函數(shù)作用:枚舉打印機(jī)名稱清單 PAGEREF _Toc290228981 h 113 HYPERLINK l _Toc290228982 121.函數(shù)作用:讀取網(wǎng)絡(luò)效勞器當(dāng)前時(shí)間 PAGEREF _Toc290228982 h 115 HYPERLINK l _Toc290228983 122.函數(shù)作用:下載文件到指定目錄 PAGEREF _Toc290228983 h 117 HYPERLINK l _Toc290228984 123.函數(shù)作用:自動(dòng)映射網(wǎng)絡(luò)驅(qū)動(dòng)器 PAGEREF _Toc290228984 h 117 H

32、YPERLINK l _Toc290228985 124.函數(shù)作用:自動(dòng)斷開網(wǎng)絡(luò)驅(qū)動(dòng)器 PAGEREF _Toc290228985 h 118 HYPERLINK l _Toc290228986 125.函數(shù)作用:連接選定單元格中的內(nèi)容 PAGEREF _Toc290228986 h 118 HYPERLINK l _Toc290228987 126.函數(shù)作用:獲取一個(gè)單元格中有指定字體顏色部份數(shù)據(jù) PAGEREF _Toc290228987 h 119 HYPERLINK l _Toc290228988 127.函數(shù)作用:對(duì)指定文件加XLS加密 PAGEREF _Toc290228988 h

33、 119 HYPERLINK l _Toc290228989 128.函數(shù)作用:選擇指定范圍內(nèi)使用了填充顏色的單元格 PAGEREF _Toc290228989 h 120 HYPERLINK l _Toc290228990 129.函數(shù)作用:在特定的區(qū)域內(nèi)查找文本,返回值是包含查找文本的單元格 PAGEREF _Toc290228990 h 120 HYPERLINK l _Toc290228991 130.函數(shù)作用:返回特定區(qū)域中最大值的地址 PAGEREF _Toc290228991 h 121 HYPERLINK l _Toc290228992 131.函數(shù)作用:刪除表格中使用范圍內(nèi)的

34、所有空白單元格 PAGEREF _Toc290228992 h 121 HYPERLINK l _Toc290228993 132.函數(shù)作用:返回?cái)?shù)組中有多少個(gè)指定的字符串 PAGEREF _Toc290228993 h 122 HYPERLINK l _Toc290228994 133.函數(shù)作用:返回當(dāng)前工作表中引用了指定的單元的地址 PAGEREF _Toc290228994 h 123 HYPERLINK l _Toc290228995 134.函數(shù)作用:獲取Excel中字型列表 PAGEREF _Toc290228995 h 123 HYPERLINK l _Toc290228996

35、135.函數(shù)作用:獲取一個(gè)字符串中有多少個(gè)數(shù)字字符 PAGEREF _Toc290228996 h 124 HYPERLINK l _Toc290228997 136.函數(shù)作用:在Excel中對(duì)多列進(jìn)行填充 PAGEREF _Toc290228997 h 124 HYPERLINK l _Toc290228998 137.函數(shù)作用:對(duì)選定的范圍進(jìn)行數(shù)據(jù)填充忽略單元格格式 PAGEREF _Toc290228998 h 124 HYPERLINK l _Toc290228999 138.函數(shù)作用:VBA Project加密及解密 PAGEREF _Toc290228999 h 125 HYPER

36、LINK l _Toc290229000 139.函數(shù)作用:列出收藏夾中的網(wǎng)址 PAGEREF _Toc290229000 h 126 HYPERLINK l _Toc290229001 140.函數(shù)作用:計(jì)算兩個(gè)日期之間相隔的年份,比方年齡,工齡等.可計(jì)算從1000年01月01日起的日期 PAGEREF _Toc290229001 h 127 HYPERLINK l _Toc290229002 141.函數(shù)作用:從字符串提取純數(shù)字 PAGEREF _Toc290229002 h 127 HYPERLINK l _Toc290229003 142.函數(shù)作用:將一個(gè)數(shù)組按升序排列 PAGEREF

37、 _Toc290229003 h 128 HYPERLINK l _Toc290229004 143.函數(shù)作用:將一個(gè)數(shù)組按降序排列 PAGEREF _Toc290229004 h 129 HYPERLINK l _Toc290229005 144.函數(shù)作用:刪除空白列 PAGEREF _Toc290229005 h 129 HYPERLINK l _Toc290229006 145.函數(shù)作用:判斷工作表是否為空白 PAGEREF _Toc290229006 h 130 HYPERLINK l _Toc290229007 146.函數(shù)作用:將數(shù)據(jù)按類分到不同工作薄 PAGEREF _Toc29

38、0229007 h 130 HYPERLINK l _Toc290229008 147.函數(shù)作用:單元格內(nèi)數(shù)據(jù)排序 PAGEREF _Toc290229008 h 131 HYPERLINK l _Toc290229009 148.函數(shù)作用:對(duì)多欄排序 PAGEREF _Toc290229009 h 132 HYPERLINK l _Toc290229010 149.函數(shù)作用:返回計(jì)算公式的值 ,值的計(jì)算公式 PAGEREF _Toc290229010 h 132 HYPERLINK l _Toc290229011 150.函數(shù)作用:把第一列=某個(gè)值對(duì)應(yīng)的第二列的內(nèi)容連在一起，并用、隔開 PA

39、GEREF _Toc290229011 h 133 HYPERLINK l _Toc290229012 151.函數(shù)作用:取得系統(tǒng)使用模式 PAGEREF _Toc290229012 h 134 HYPERLINK l _Toc290229013 152.函數(shù)作用:計(jì)算機(jī)注銷/關(guān)機(jī)/重啟 PAGEREF _Toc290229013 h 134 HYPERLINK l _Toc290229014 153.函數(shù)作用:更改計(jì)算機(jī)名稱 PAGEREF _Toc290229014 h 135 HYPERLINK l _Toc290229015 154.函數(shù)作用:從n位開始取出字符串中的漢字、英文字母、數(shù)

40、字 PAGEREF _Toc290229015 h 135 HYPERLINK l _Toc290229016 155.函數(shù)作用:在指定列中尋找含有指定字符串的單元格，并將符合條件的單元格標(biāo)為紅色，并將對(duì)應(yīng)的下一列單元格賦值為1 PAGEREF _Toc290229016 h 136 HYPERLINK l _Toc290229017 156.函數(shù)作用:去除字符串中的空格 PAGEREF _Toc290229017 h 136 HYPERLINK l _Toc290229018 157.函數(shù)作用:查找合并單元格位置 PAGEREF _Toc290229018 h 137 HYPERLINK l

41、 _Toc290229019 158.函數(shù)作用:陰陽歷轉(zhuǎn)換和陰陽歷生日 PAGEREF _Toc290229019 h 137 HYPERLINK l _Toc290229020 159.函數(shù)作用:利用數(shù)組和Substitute來替換某字符 PAGEREF _Toc290229020 h 141 HYPERLINK l _Toc290229021 160.函數(shù)作用:一鍵創(chuàng)立斜線表頭 PAGEREF _Toc290229021 h 141 HYPERLINK l _Toc290229022 161.函數(shù)作用:自動(dòng)獲取指定月的工作日 PAGEREF _Toc290229022 h 143#1.函數(shù)

42、作用：返回 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ù)作用：查詢某一值第num次出現(xiàn)的值 參數(shù)說明：Value1：查詢引用的數(shù)值; Range1：查詢

43、區(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 Then c = c + 1 If c = num Then v1 = D(1, C

44、ol) 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è)人所得稅稅額 語 法：Grsds(bsc, mysala) 參數(shù)說明：bsc: 必選項(xiàng)，為起征點(diǎn)，包括稅法規(guī)定的工資基數(shù)800元加上允許稅前扣除的合理費(fèi)用; mysala: 必選項(xiàng)，為人個(gè)工資薪金所得。 示 例：Grsds(850, 20000) =#Function Grsds(bsc As Dou

45、ble, 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 = Application.WorksheetFunction.Round(mysala - bs

46、c) * 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.WorksheetFunction.Round(mysala - bsc) * 0.25 - 1

47、375, 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, Len(mystring) - i + 1)End Function#6.函數(shù)作用：

48、按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 Then RangeName = VBA.Left(RangeName, VBA

49、.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(VBA.InStr(1, RangeName, $) 0, VBA.Right(R

50、angeName, 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, RangeName, $) - 1) Else SplitRangeName = Ran

51、geName 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)成中文大寫#Function Money(Number As Currency) Dim i, j, k, m, leng As Integer 計(jì)數(shù)器 Dim Zero As Integer 連續(xù)零標(biāo)識(shí) Dim Tnumber

52、As String 儲(chǔ)存數(shù)字字符串,計(jì)算數(shù)組長(zhǎng)度 Dim Num() As String 定義數(shù)組 Dim Num1(3) As String 存儲(chǔ)萬元以下數(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 如果大于一億，那么不處理 If (Number 99999999) Or (Number -

53、99999999) Then MsgBox (Sorry,數(shù)據(jù)超過一億，暫不處理。) MsgBox (順便問一下，你真有那么多錢嗎？) 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 - Int(Number) * 100 - Int(Number - Int(Numb

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

55、佰, 仟, 萬, 億) *處理小于一元金額* *小數(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 (Number - Int(Number) 0) Then i = i - 1 Num2(

56、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 *分解大于一萬的整數(shù)局部* If (Int(Number) 9999) Then If (Cha2(0) ) Then i = i + 1 End If For j = 3 To 0 Step -1 Num1(j) = Num(i -

57、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)換萬元以上數(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(j - 1) = M2(m) Then Flag1 = True End If N

58、ext 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 Cha(j) = M2(i - 1) j = j + 1 i = i - 1 Z

59、ero = 0 End If Next k Cha(j - 1) = 萬 Zero = 0 End If *轉(zhuǎ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 If (j 0) Then If (Cha1(j - 1) = M2(m) Then

60、 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 If (Num1(k) = 0) Then i = i - 1 Else Cha1