在excel中实现阳历转化阴历

1、步骤一，在Excel工作表界面下按 组合键打开VBA窗 口，进入VBA编辑环境，在其窗口的菜单栏上依次单击 插入”宀 模 块”，可在当前VBA工程中插入模块，并直接进入此模块的代码编辑 区域。可在此编辑区域中输入自定义函数的程序代码，如图：Public Fu nction Non gLi(Optio nal XX DATE As Date)Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30),Mo nN ame(12)Dim curTime, curYear, curM on th,

2、 curDayDim Gon gliStr, Non gliStr, No ngliDayStrDim i, m, n, k, isE nd, bit, TheDate获取当前系统时间curTime = XX DATE天干名称TianGan(0)=甲”TianGan(1)=乙”TianGan(2)=丙”TianGan(3) = 丁TianGan(4)=戊”TianGan(5)=己TianGan(6)=庚TianGan=辛”TianGan(8)=壬”TianGan(9)=癸地支名称DiZhi(0)=子DiZhi(1)=丑DiZhi(2)=寅DiZhi(3)=卯”DiZhi(4)=辰DiZhi(5

3、)=巳DiZhi(6)=午DiZhi(7)=未DiZhi(8)=申”DiZhi(9)=酉DiZhi(10)=戌IDiZhi(11)=亥属相名称DayName(30DayName(24DayName(22DayName(21)- 廿一”N a mII+NDayName(11DayName(10DayName(9)NDayName NShuXiang(11ShuXiang(10ShuXiang(9)ShuXiang(8)ShuXiang(7)ShuXiang(6)ShuXiang(5)ShuXiang(4)ShuXiang(3)ShuXiang(2)ShuXiang(1)ShuXiang(O)1

4、I匸t3J4厂4匸t3匸t3)=廿四”4Ll) = 十九”+厂)=十四”+ 丨丨) = 初十”I厂eVis卜n甘 !卅IIeVeV农历月份名MonN ame(0) = *MonName(1)=正MonName(2)= 二MonName(3)= 三”MonN ame (4)=四MonName(5)=五”MonName(6)= 六”MonName(7)= 七MonName(8)= 八MonName(9)= 九”MonName(10)= 十IIMonName(11) = 十MonName(12)= 腊II公历每月前面的天数Mon thAdd(0) = 0Mo nthAdd=31Mon thAdd(2

5、) = 59Mon thAdd(3) = 90Mo nthAdd(4) = 120Mon thAdd(5) = 151Mo nthAdd(6) = 181Mon thAdd(7) = 212Mon thAdd(8) = 243Mon thAdd(9) = 273Mon thAdd(10) = 304Mon thAdd(11) = 334农历数据Non gliData(0) = 2635Non gliData(1) = 333387Non gliData(2) = 1701Non gliData(3) = 1748Non gliData(4) = 267701Non gliData(5) = 6

6、94Non gliData(6) = 2391Non gliData(7) = 133423Non gliData(8) = 1175Non gliData(9) = 396438Non gliData(10) = 3402Non gliData(11) = 3749iNon gliData(12) = 331177Non gliData(13) = 1453iNon gliData(14) = 694No ngliData(15) = 201326No ngliData(16) = 2350No ngliData(17) = 465197No ngliData(18) = 3221No ng

7、liData(19) = 3402No ngliData(20) = 400202No ngliData(21) = 2901No ngliData(22) = 1386No ngliData(23) = 267611Non gliData(24) = 605No ngliData(25) = 2349No ngliData(26) = 137515No ngliData(27) = 2709No ngliData(28) = 464533No ngliData(29) = 1738No ngliData(30) = 2901No ngliData(31) = 330421No ngliDat

8、a(32) = 1242No ngliData(33) = 2651No ngliData(34) = 199255No ngliData(35) = 1323No ngliData(36) = 529706No ngliData(37) = 3733No ngliData(38) = 1706No ngliData(39) = 398762No ngliData(40) = 2741No ngliData(41) = 1206No ngliData(42) = 267438No ngliData(43) = 2647No ngliData(44) = 1318No ngliData(45)

9、= 204070No ngliData(46) = 3477No ngliData(47) = 461653No ngliData(48) = 1386No ngliData(49) = 2413No ngliData(50) = 330077Non gliData(51) = 1197No ngliData(52) = 2637No ngliData(53) = 268877No ngliData(54) = 3365No ngliData(55) = 531109No ngliData(56) = 2900No ngliData(57) = 2922No ngliData(58) = 39

10、8042No ngliData(59) = 2395Non gliData(60) = 1179No ngliData(61) = 267415No ngliData(62) = 2635No ngliData(63) = 661067No ngliData(64) = 1701No ngliData(65) = 1748No ngliData(66) = 398772No ngliData(67) = 2742No ngliData(68) = 2391No ngliData(69) = 330031Non gliData(70) = 1175No ngliData(71) = 1611No

11、 ngliData(72) = 200010No ngliData(73) = 3749No ngliData(74) = 527717No ngliData(75) = 1452No ngliData(76) = 2742No ngliData(77) = 332397No ngliData(78) = 2350No ngliData(79) = 3222No ngliData(80) = 268949No ngliData(81) = 3402No ngliData(82) = 3493No ngliData(83) = 133973No ngliData(84) = 1386No ngl

12、iData(85) = 464219Non gliData(86) = 605No ngliData(87) = 2349No ngliData(88) = 334123No ngliData(89) = 2709No ngliData(90) = 2890No ngliData(91) = 267946|No ngliData(92) = 2773No ngliData(93) = 592565 No ngliData(94) = 1210No ngliData(95) = 2651No ngliData(96) = 395863No ngliData(97) = 1323No ngliDa

13、ta(98) = 2707No ngliData(99) = 265877生成当前公历年、月、日= Go ngliStrcurYear = Year(curTime)curM onth = Mon th(curTime) curDay = Day(curTime)Gon gliStr = curYear & 年If (curMo nth 10) The nGongliStr = GongliStr & 0 & curMonth & ” 月”ElseGongliStr = GongliStr & curMonth & 月End IfIf (curDay 2) ThenTheDate = TheD

14、ate + 1End If计算农历天干、地支、月、日isEnd =m = 00DoIf (NongliData(m) 4095) Thenk = 11Elsek = 12End Ifn = kDoIf (n 0) ThenExit DoEnd If获取NongliData(m)的第n个二进制位的值 bit = Non gliData(m)For i = 1 To n Step 1bit = In t(bit / 2)Nextbit = bit Mod 2If (TheDate (Int(NongliData(m) / 65536) + 1) ThencurMonth = curMonth -

15、1End IfEnd If生成农历天干、地支、属相= Non gliStrNongliStr =农历& TianGan(curYear - 4) Mod 60) Mod 10) & DiZhi(curYear - 4) Mod60) Mod 12) & ” 年”Non gliStr = Non gliStr & (” & ShuXia ng(curYear - 4) Mod 60) Mod 12) & )生成农历月、日= Non gliDayStrIf (curMo nth 1) The nNongliDayStr =闰& MonName(-1 * curMonth)ElseNo ngliDa

16、yStr = MonN ame(curMo nth)End IfNo ngliDayStr = Non gliDayStr & ”月”NongliDayStr = NongliDayStr & DayName(curDay)NongLi = Non gliStr & Non gliDayStrEnd Function步骤2,单击窗口右上角的 关闭”按钮关闭VBA编辑窗口，返回到 工作表窗口中。此时即可在当前工作簿中使用刚刚创建的自定义函 数，并且可以在函数列表的 用户定义”类别中找到此自定义函数。如 图步骤3，要使自定义函数能够应用在其他工作簿中， 需要将这个包含自定义函数的工作簿另存为加载宏”依次单击“Office按钮”宀 另存为”-其他格式”打开 另存为”对话框，在 保存类