VBA自定义函数大全.docx

《VBA自定义函数大全.docx》由会员分享，可在线阅读，更多相关《VBA自定义函数大全.docx（111页珍藏版）》请在三一文库上搜索。

1、VBA自定义函数大全龙族联盟论坛Shcnmartin收佻整理1.函数作用,返回Co1.umn英文字#样#样#FunctionCo1.1.etter(Co1.NumberAsInteger)AsStringOnErrorGoToErrorhand1.erCo1.1.etter-1.eft(Ce1.1.S(IJCo1.Number).Address(0,0),1-(Co1.Number26)ExitFunctionErrorhand1.er:MsgBox,Errorencountered,p1.easere-enterEndFunction碗#力2函数作用,查询某一值第num次出现的值,参数说明:

2、Va1.ue1.:查询引用的数位；Range1.：在询区域；num：指定杳询第几次出现；Co1.：返回值，相对引用区域，相对引用列的右数第Co1.列#样#样#样#FunctionMyFind(VaIUe1,ByVa1.Range1.AsRange,ByVa1.numAsInteger,ByVa1.Co1.AsInteger)IfVa1.ue1.“ThenExitFunctionIfRange1.Co1.umnsXount1ThenExitFunctionForEachDInRange1.IfD.Va1.ue三Va1.ue1.Thencc1Ifc=numThenv1.=D(1.,Co1.)Exi

3、tForEndIfE1.seIfIsEmpty(D)ThenExitForEndIfNextIfv1.=wwThenv1.三“not”MyFind=v1.EndFunction,3.函数作用：返回当个人工资希金所得为2ee元（起征点为850元）时的应纳个人所得税税额ifi法：Grsds（bsc,mysa1.a）,参数说明；bsc:必选攻，为起征点，包括税法规定的工费展数8。元加上允许税前扣除的合理费用；,mysa1.a:必选项.为人个工资薪金所得.,示例：GrSdS（85。，20000）=FunctionGrSdS(bscAsDoub1.e,mysa1.aAsDoub1.e)AsDoub1.e

4、bsc为起征点加上允许税的扪除的合理费用，mysa1.a为工资薪金所得OnErrorGoToGrsds-errSe1.ectCasemysa1.aCaseIs-bscGrsds-0CaseIs=bsc+50Grsds=App1.ication.WorksheetFunction-RoundC(mysa1.a-bsc)*0.05,2)CaseIsbsc+2000Grsds=App1.ication.WorksheetFunction.Round(mysa1.a-bsc)0.1-25,2)CaseIs三bsc+5000Grsds-App1.ication.WorksheetFunction.Ro

5、und(mysa1.a-bsc)0.15-125,2)CaseIs=bsc+20GrsdsHAPPIiCatiOn.WorksheetFunction.Round(mysa1.a-bsc)*0.2-375,2)CaseIs=bsc+4Grsds=App1.ication.WorksheetFunction.RoundC(mysa1.a-bsc)”0.25-1375,2)CaseIs-bsc+60000Grsds=App1.ication.WorksheetFunction.Rond(aysa1.a-bsc)*0.3-3375,2)CaseIs三bsc+80000Grsds-App1.icati

6、on.WorksheetFunction.Round(mysa1.a-bsc)0.35-6375,2)CaseIs0i=i11.oopmydataVaI(Mid(InyString,i,1.en(mystring)-i1)EndFunction6,函数作用：按Sp1.itType取得RangeName串值中的起始位JK样#样#样#材#样#1：单兀格，2：行号，3：列号.4：范困Pub1.icPub1.icPub1.icPub1.icConstConstConstConstSING1.ECE1.1.-1ROWNUM-2CO1.NUM=3RANGEA1.1.=4Pub1.icFunctionSp1

7、itRangeName(RangeNameAsString,Sp1.itTypeAsInteger)AsStringIfVBA.1.en(RangeName)eThenRangeName-VBA.1.eft(RangeName,VBA.InStr(1.,RangeName,:)-1)Se1.ectCaseSp1.itTypeCaseSING1.E_CE1.1.IfVBA.1.nStr(1.,RangeName,:)0ThenSp1.itRangeName-&VBA.1.eft(RangeNamejVBA.InStr(1.jRangeName,-1)E1.seSp1.itRangeName三S

8、RangeNameEndIfCaseROW_NUMSp1.itRangeName=VBA.IIf(VBA.InStr(1.,RangeName,$),VBA.RIght(RangeNaae,VBA.1.en(RangeNaae)-VBA.1.Str(1.,RangeName,5),RangeName)IfNotIsNumeric(Sp1.itRangeName)ThenSp1.itRangeName=MsgBox,VbInformation,EndIfCaseCO1._NUMIfVBA.InStr(1.,RangeName,$)ThenSp1.itRangeName-VBA.1.eft(Ra

9、ngeName,VBA.IStr(1.,RangeName,$)-1)E1.seSp1.itRangeName=RangeNameEndIfIfIsNumeric(Sp1.itRangeName)ThenSp1.itRangeName=MsgBoxVbInformationjEndIfCaseRANGE_A1.1.Sp1.itRangeName=&RangeNameEndSe1.ectEndIfEndFunction一A*一一AA.A.X.A1.A.1.uaaau*A*aaauaA,7.a数作用:将金额数字转成中文大写#FunctionMOney(NUmberAsCurrency)Dimi,j

10、k,m,IengAsInteger计数播DimZeroAsInteger连社:专标识DimTnumberAsString0存数小，计算数组长度DimNUm()AsString，定义数组DimNum1.(3)AsString访7,元以下数7DimNum2(1.)AsString储存拆分后的数7DimCha(8),Cha1.(9),Cha2(4)AsString储存转化后的汉字DimZchaAsString连接出的字符串DimF1.ag,F1.ag1.AsBoo1.ean正负标志F1.ag-TrueF1.ag1.=Fa1.seZero=如果大于一亿,则不处理If(Number99999999)O

11、r(NumberJ卜，你一有那么多(吗?)Money-Sorry!E1.seIf(Number=0)ThenMoney-兀”E1.se将负数数字转化正数并更改标识If(Number0)ThenTnumber-CStr(Int(Number1.)/100)E1.seTnumber=CStr(Number)EndIf处理四舍五入If(Number-Int(Number)1.-Int(Number-Int(Number)*1.)=.5)ThenTnumber三CStr(CCur(Tnumber)+0.01)EndIfNumber=CCur(Tnumber)*或新分配数殂空间*ReDimNum(1.e

12、n(Tnumber)-1)AsString将字符中分开存储至数组中Fori=0To1.en(Tnumber)-1Num(i)=Mid(Tnumberji+1,1)Nexti，定义所需字符，.DimM1.,M2M1.-Array(,w,叁,评，,陆,柒,捌,玖)M2=Array拾，佰，1F,n7in,E)*处理小于一元金撇*小数点后一位，则*If(Number-Int(Number)And(Number*Iee-Int(Number)*100)Mod1.)-)Theni=i-1NUm2(。)=Num(I)Num(i)三i-i-1Num(i)=i=i-1Cha2()-M1.(CByte(NUm2(

13、e)Cha2(1.)=角Cha2(2)=整”E1.se*小钦点后两位则*If(Number-Int(Number)0)Theni=i-1Num2(1.)=Num(i)Num2()-Num(i-1)Num(i)m-i=i-1Num(i)=-i-i-1Nm(i)-,i=i-1Cha2()=MI(CByte(NUm2(0)Cha2(1.)-“角”Cha2(2)-N1.(CByte(NUm2(1)Cha2(3)=分EndIfEndIf，分解大于一万的整数部分If(Int(Number)9999)ThenIf(Cha2(),)Theni-i+1EndIfForj=3To0Step-1Num1.(j)=N

14、um(i-1)Num(i-1)-i-i-1NextjE1.seIf(Cha2()Theni-i+1EndIfForj=0Toi-1Nua1.(J)Nun(J)Nn(j)“NextjEndIf*，*转换万元以上数字If(Num(0)ThenIeng=ij=Fork三ToIeng-1If(Nun(k)-0)ThenZero=Zero+1Form=1To5If(Cha(j-1)-M2(m)ThenF1.ag1.-TrueEndIfNextmIf(Zero1)And(F1.ag1.三Fa1.se)ThenCha(j)-M1.(CByte(Num(k)EndIfIf(Zero=1)Thenj三j+1En

15、dIfE1.seIf(Num(k)ThenIf(Zero)ThenCha(j-1)=-.-EndIfCha(j)=M1.(CByte(Num(k)EndIfj-j1EndUIf(Nm(k)=0)Theni三i-1E1.seCha(j)=M2(i-1)Zero-EndIfNextkCha(j-1),7,Zero0EndIf*转换万元以下数字*If(Num1.(0)Thenj-F1.ag1.=Fa1.seIeng=3Whi1.e(Num1.(Ieng)-)Ieng-Ieng-1Wendi=Ieng+1Fork三ToIengIf(Num1.(k)JThenIf(Num1.(k)=0)ThenZero

16、Zero+1Form-1To5If(j0)ThenIf(Cha1.(j-1)=M2(m)ThenF1.ag1.=TrueEndIfEndIfNextmIf(Zero=1)And(F1.ag1.=Fa1.se)ThenCha1.(j)-M1.(CByte(Num1.(k)EndIfIf(Zero=1)Thenj=j1EndIfE1.seIf(Num1.(k)-)ThenIf(Zero)ThenCha1.(j-1)苓“EndIfCha1.(j)=M1.(CByte(Num1.(k)EndIfj-j+1EndIfIf(Nun1.(k)=0)ThenE1.seCha1.(j)-M2(i-1)j=j+

17、1Zero三0EndIfEndIfNextkCha1.(j-1)-c,If(Cha2(0)-)Thenchai(j)=*rrEndIfEndIf连接字符小j=0Whi1.e(Cha(j)Zcha-Zcha&Cha(J)j-j1Wendj=Whi1.e(Cha1.(j)ZchaZcha&Cha1.(j)j=j+1Wendj-Whi1.e(Cha2(j)Zcha=Zcha&Cha2(j)j=j+1Wend，,最终显示，If(F1.ag)ThenMoney=ZChaE1.seMoney=负”&ZchaEndIfEndIfEndIfEndFunction8.函数作用：计算某种税金#样#Pub1.icF

18、unction税(fa)DimXIf(fa-800)0And(fa-80)三500And(fa-8)=20And(fa-8O)=SeAnd(fa-800)-2000And(fax=(fa-8)*.25-1375税XE1.seIf(fa-800)4000And(faX-(fa-800)*0.3-3375税=E1.seIf(fa-80)=60And(faX-(fa-800)*.35-6375税=XE1.seIf(fa-8)=8eAnd(fax=(fa-80)*0.4-10375税E1.seIf(fa-800)-100000Thenx=(fa-8)*0.45-15375税*X-8e)40000The

19、n-8)6Then-800)80000Then-800)DBNum1.)f=App1.ication.WorksheetFunction.Text(2,-DBNum1.M)g-APP1.iCatiOn.WorksheetFunction.Text(n3,DBNum1.)If3-0Then小马三”人民币大n；&e&EndIfIf(n30)And(2)Then小写=人民币大二：“&e&几&f&ffj&g&分”Ifn1.=0Then小耳-人民币火：&f&角&g&分EndIfEndIfIf(n3=0)Andn2Then小耳”人币大写：&e&x&f&ffj&”整”Ifn1.-0Then小写=人民币大巧：

20、n&f&ffj&整EndIfEndIfIf(n3e)And(n2-)Then小写=1人民用大打：&e&无&g&1分”Ifn1.=0Then小写-人民币大母I&g&分”EndIfEndIfIfk=Ork=Thenk=EndIfApp1.ication1ScreenUpdatingTrueEndFunctionFunction大写(k)App1.ication1ScreenUpdating-Fa1.sem1.=App1.ication.WorksheetFu11ctio11.Rou11d(k10,0)n1.=Intm1./1.)n2-1.nt(m1./1.)-n1.*103-m1.-n1.*100

21、n2*1.e=App1.ication.WorksheetFunction.Text(n1.,db11um2-)f=App1.ication.WorksheetFunction.Text(112,dbnm2)g三APPIiCation.WorksheetFunction.Text(n3,dbnum2)If3-0Then大写=人民币大打；&e&ftEndIfIf(n30)And(n2)Then大写=1人民而大二：&e&几&f&ffj&g&-Ifn1.=0Then大写-人民币大耳：&f&f(,&g&分”EndIfEndIfIf(n3=)Andn2Then大写三”人葭币人一:&e&;&f&0&If

22、n1.-0Then大写=人民币大巧：&f&ftj&EndIfEndIfIf(n30)And(n2-)Then大写=1人民币大二：&e&几“&f&g&分”Ifn1.=0Then大写-人民币大：&g&”分EndIfEndIfIfk=Ork=Then大耳-EndIfApp1.ication.ScreenUpdating-TrueEndFunctionie.函数作用：Cxy区位码Pub1.icFunction区(fa$)ASStringOnErrorResumeNextDim1.1.$,R1.$,1.$,R$,a,b$,c,d,e$c=1.en(fa)Fori1Tocd-Mid(fa,i,1)a=He

23、x(Asc(d)1.1.=CInt(SH+Mid(a,1,2)-16R1.三CInt(ftH+Mid(a,3,2)-160If1.en(1.1.)-1Then1.=0&1.1.E1.se1.-1.1.EndIfIf1.en(R1.)=1ThenR=&R1.E1.seR-R1.EndIfNextiK=eEndFunction#*#11.函数作用：北1公元年转为农历0V天干地支年1)十二生肖年2)农历月3)农历日4),24节气函数说明：本函数利用阵列处理，以方便日后现今持列IntToSimDay_$(,IntToSimDay-$(,IntToSimDay_$(,IntToSimDay_$(,Int

24、ToSimDay_$(,口商可使用至2010年FunctionChineCa1.ender(iDate,Optiona1.numAsInteger=0)num:。8都可用，不输入num预设值为e分别介绍08的用法假设A1.:2002/12/22ChineCa1.ender(A1.)：壬午年马卜月十九冬至ChineCaIender(A1,。)：壬午年马I,一月卜九冬至ChineCa1.ender(A1.,1):r5-f月十九冬至ChineCa1.ender(A1.,2):月十九冬至ChineCa1.ender(A1.jJ):I一1月十九ChineCa1.ender(A1.M):壬午年ChineC

25、a1.ender(A1.jS):年ChineCa1.ender(A1.,6):月ChineCaIenC1.er(AI,7):十九HChineCa1.ender(A1,8):冬至TTTTTtTTTTTTTTTTtTtTTTTtTYTTTtTTTTTTTtTTTTTTTtTTTTTTTtTTTTTTTTTT1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.rrVTTTrTTVTTTDimIntToSimDay_$(31,4)Pub1.icrgstrMonthName(11)AsStringPub1.icPgstrDayName(G)AsStringPub1.icPgi

26、DaysInMonth(I1.)AsStringDimDimB_2_(220)DimB_3_(410)DimB_4_$(30)DimB_5_$(12)DimB_6_$(12)DimB_7_$(23)DimiYearDimiMonthDimiDayS天干地支年I)十二生肖年2)农历月3),农历日4)24节.气IntToSimDay_$(,IntToSimDay_$(,IntToSimDay_$(,IntToSimDay_$(,IntToSimDay_$(,FunctionChineCa1.ender(iDate,Optiona1.numAsInteger=0)DimiYearAsInteger,

27、iMothAsInteger,iDayAsIntegerIfIsDate(IDate)TheniYear-Year(iDate)iMonth=Month(iDate)iDay=Day(IDate)Ca1.1.Ini1.unarStrGet1.unarDaysiYear,iMonthIntyear=民国”&App1.ication.WorksheetFunction.Text(!Year-1911,DBNum1.;)&,年,Se1.ectCasenumCase0ChineCa1.ender=IntToSimDay_S(iDay-1)&IntToSimDay_S(iay-1,1)&IntToSim

28、Day_$(iDay-1.j2)_&IntToSimDay_$(iDay-1,3)&IntTOSifnDay_$(iDay,4)Case1ChineCa1.ender=IntToSimDay_t(iDay-1,1)&IntToSimOay_$(iDay-1,2)_&IntToSimDay_$(iDay-1.j3)&IntToSimDay_$(iDay,4)Case2ChineCa1.enderIntToSimDay_$(iDay-1,IntToSimDay_$(iDay-1,3)&IntTOSimDay_$(iDay,4)Case3ChineCa1.ender=IntToSimDay_$(iD

29、ay-1,IntToSimDay_$(iDay-1,3)Case4ChineCa1.ender=IntToSimDayS(iDay-1,Case5ChineCa1.ender-IntToSimDay_S(iDay-1,Case6ChineCa1.ender=IntToSimDay_S(iDay-1,Case7ChineCa1.ender-IntToSimDay_S(iDay-i,Case8ChineCa1.ender=IntToSimDay_$(iDay,4)2)&2)&0)1)&年2)3)&I-CaseE1.seChineCa1.enderEndSe1.ectE1.seChineCa1.en

30、der-mmEndIfEndFunctionPrivateSubIni1.unarStrODimirgstrMothName()=wjrgstrMonthName(1.)mrgstrMonthName(2)“JPgstrMonthName(J)=四J*rgstrMothName(4)=,.,JwPgstrMonthName(三)-mImrgstrMothName(6)m1.ImrgstrMonthName(7)三“八rgstrMothName(8)=w/1.dwrgstrMonthName(9)-IwrgstrMonthName(10)-mjmrgstrMonthName(11)三“IJ”B_

31、2_(0)=30B-2_(1)29B_2_(2)3B_2_-B_2_(4)=B_2_=B_2_(6)B_2_(7)-B_2_(8)=B_2_(9)=B-2_(10)B-2_（三）B_2_(12)B_2_(13)；B-2_(14)B_2_(15)B_2_(16)=B_2_(17)三B_2_(18)；B_2_(19)，B_2_(20)：B_2_(21)三36:3029302930292930=29=311,121994(农历月份最后一天),1,23,5,6,789,1.,11*121995-2930=3=29329三36=3,26,7B-2-(22)-39B-2_(23)-29B_2_(24)=3

32、08_2_(25)=29B_2_(26)3B-2_(27)-29B_2_(28)=30B_2_(29)=29B_2_(30)3B-2_(31)-308_2_(32)=298_2_(33)=30B-2_(34)29B_2_(35)30B_2_(36)=308-2-(37)=29B-2-(38)-29B-2_(39)30B_2_(40)=298_2_(41)=3。B-2-(42)-29B-2_(43)-30B_2_(44)=298-2-(45)=3B_2_(46)三3B_2_(47)-29B_2_(48)=30B_2_(49)=3B_2_(50)-29B_2_(51)-30B_2_(52)=29B

33、2_(53)=29B-2_(S4)三3B-2_(55)-29B_2_(56)=39B_2_(57)=3B_2_(58)三3B-2_(S9)-29B_2_(60)=308_2_(61)=3B-2_(62)三29B_2_(63)-30B_2_(64)=30B_2_(65)=29r89,1.111215,6789,1.,11,1,25,6-7891.1157,89101112,119961997199819996.e=(601.)-2-88.63=(81.)-Z-8.62(01)-389,。=(90I)-Z-8S.6?=(S01.)-3-8v.=(r)-z-8,62-(E)-z-a3.0C-(Z0

34、T)-Za,0=(i0T)zg33z.ei=(00i)zs,62(66)-3-80T.0-(86)-Z-96.6Z=(1.6)-Z98.=(96-Z-8.62-(S6)-Z-89,6Z-(fr6)-Z-0S,0=(6)3_8RJ,6=。6厂8V.0-(T6)-38.6Z三(06)-Z-f1.乙。=(68厂8I.=(888T00Zz,62-(z.8)3-8.。(98)-z-a01.0=(S8)-3-86.61=(，8厂88.0E-(8)-3-8i,6Z-(Z8)-Z-f1.9.6?=(18)-8S.=(8)-Z-8V.6Z-(6)-38E.6Z三(8)Z-0乙佻=(U)ZI.=(9)-Z-800

35、032T.62(SZ)-38,0c-()za01.0=()86.=(3i)-Z-88.62(T)-Z-8Z,。一(0)Za9.6Z=(69)ZS.6Z=(89)ZSP.0E-(9)ZS.6Z三(99)-Z-9900?t.9003.9003Zt90。ZI.90ZZ1.S3II.S00301.S0。Z6.S。Z8.S031.see39.S0ZS.S0Zr.S03.S003ZtS00ZT.SOOZZX.01.6.8.1.9.PWZz.U.0T.6.8.1.,9.X.00231.0T.63=0=GZ-女三62=6Z-。三63=0=0E-6Z0=63=0E-63次=63=。-6Z笈=61=。-6Z三次=

36、6-0fZ=6乙-0三6Z=63=。-6Z次=6Z-。三0=63=0E-6Z三(1.)-9(zs)-8(s)-z-8(0s)-Z-a(6tx)-3-8(8t)-3-8(SD-1.g(9tr)-Z-9(St1.)-9(tt)-j-8(v)-z-8(ZVI)-Z-9(ItT)-3-9(0VX)-3-8(6)-Z-8(8I)-Z-9(D-3-8(91.)-Z8(S-Z-8(w)-z-a(厂广飞(ZT)-3-8()-z-8(0ET)-Z-9(61.)-3-9(8Sc(1.Z1.)-Z-8(9z)zQ(SD-Z-8(W1.)-3-8ZI)-Z-Q(zz)zQ(z)z9(03T)-J-8(6)-Z-8(8)-z-f1.(i1.1.)-3-8(911)-3-8(s)-z-8(tr)-z-9(【1)(z)z9()-z-8()-Z-a,12007,2207327,4200752007,62007,7278279207,1