Excel VBA_自定义函数集.doc

1，求得a年b月第c个星期d的日期‘/viewthread.php?tid=459346pid=2991598page=1extra=page%3D1 ‘求得a年b月第c个星期d的日期Function xqrq(myYear, myMonth, Optional n% = 1, Optional myxq% = 1) As DatemyYear年份，myMonth月份，n第几个，myxq星期几myxq=1(周日)、2(周一)、3(周二)...Dim myDate As Date, xq%myDate = DateSerial(myYear, myMonth, 1)xq = Weekday(myDate)If myxq xq Then? ? xqrq = myDate + 7 - xq + myxq + 7 * (n - 1)Else? ? xqrq = myDate + 7 * (n - 1) - xq + myxqEnd IfEnd Function用法：=xqrq(1991,3,2,4)? ? 1991年3月第2个星期三??返回1991-3-13n默认为第1个，myxq默认为星期日（＝1）如果A1＝1991，B1＝3E1＝xqrq(A1,B1,,)? ? 返回1991-3-3求得‘2010-2-5 ‘/viewthread.php?tid=535481pid=3540112page=1extra=page%3D1 Function ls(ParamArray rngs() As Variant) Dim d, ad1$, aa, bb, i%, j, cc1, cc2, ii% Set d = CreateObject(Scripting.Dictionary) For ii = 0 To UBound(rngs) ad1 = rngs(ii).Address(ReferenceStyle:=xlR1C1) If InStr(ad1, ,) 0 Then aa = Split(ad1, ,) For i = 0 To UBound(aa) If InStr(aa(i), :) 0 Then bb = Split(aa(i), :) cc1 = Split(bb(0), C)(1) cc2 = Split(bb(1), C)(1) For j = Val(cc1) To Val(cc2) d(j) = Next Else j = Split(aa(i), C)(1) d(j) = End If Next Else If InStr(ad1, :) 0 Then bb = Split(ad1, :) cc1 = Split(bb(0), C)(1) cc2 = Split(bb(1), C)(1) For j = Val(cc1) To Val(cc2) d(j) = Next Else j = Split(ad1, C)(1) d(j) = End If End If Next ls = 有 d.Count 列。 End Function 函数用法： ＝ls(A2:A10,B4:d7,C5:G8)Function myweekday(ByVal y As Long, ByVal m As Long, ByVal d As Long) As Long Dim bias As Long If m 2 Then bias = m - 2 Else bias = 10 + m y = y - 1 End If myweekday = (d + (13 * bias - 1) \ 5 + (y Mod 100) + (y Mod 100) \ 4 + (y \ 100) \ 4 - 2 * (y \ 100)) Mod 7 + 1 End Function 本文来自CSDN博客，转载请标明出处：/northwolves/archive/