Excel高級應(yīng)用

1、 Excel 高級應(yīng)用 學 號 :120616124姓 名 : 孫 艷一,用宏錄制器進行宏編碼宏編碼: Sub 跨列居中() 跨列居中 MacroRange("A1").Select With Selection.Font .Name = "Tahoma" .Size = 22 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .Theme

2、Color = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("A1").Select ActiveCell.FormulaR1C1 = "120616班級學員信息登記表" With ActiveCell.Characters(Start:=1, Length:=6).Font .Name = "Tahoma" .FontStyle = "常規(guī)" .Size = 22 .Strikethrough =

3、 False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With With ActiveCell.Characters(Start:=7, Length:=9).Font .Name = "宋體" .FontStyle = &quo

4、t;常規(guī)" .Size = 22 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("A2").Select ActiveCell.FormulaR1C1 = &

5、quot;學號" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋體" .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .Tin

6、tAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("B2").Select ActiveCell.FormulaR1C1 = "姓名" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋體" .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .

7、OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("C2").Select ActiveCell.FormulaR1C1 = "性別" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋體&qu

8、ot; .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("D2").Select Activ

9、eCell.FormulaR1C1 = "民族" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋體" .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xl

10、ThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("E2").Select ActiveCell.FormulaR1C1 = "出生年月" With ActiveCell.Characters(Start:=1, Length:=4).Font .Name = "宋體" .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = Fal

11、se .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("A3").Select ActiveCell.FormulaR1C1 = "101" Range("B3").Select ActiveCell.FormulaR

12、1C1 = "孫艷" With ActiveCell.Characters(Start:=1, Length:=2).Font .Name = "宋體" .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLigh

13、t1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("C3").Select ActiveCell.FormulaR1C1 = "女" With ActiveCell.Characters(Start:=1, Length:=1).Font .Name = "宋體" .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = F

14、alse .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("D3").Select ActiveCell.FormulaR1C1 = "漢" With ActiveCell.Characters(Start:=1, Length:=1).Font .Name = "

15、宋體" .FontStyle = "常規(guī)" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("E3").Select

16、ActiveCell.FormulaR1C1 = "3/5/1992" Range("A1:F1").Select Range("F1").Activate With Selection .HorizontalAlignment = xlCenterAcrossSelection .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .Readin

17、gOrder = xlContext .MergeCells = False End With Range("F3").SelectEnd Sub二,請寫出代碼完成身份證號碼中生日信息提取宏編碼:Sub Macro1()' Macro1 Macro Range("C2").Select ActiveCell.FormulaR1C1 = "=MID(RC-1,11,2)&""-""&MID(RC-1,13,2)" Range("C3").Select A

18、ctiveCell.FormulaR1C1 = "" Range("C2").Select Selection.AutoFill Destination:=Range("C2:C3"), Type:=xlFillDefault Range("C2:C3").SelectEnd Sub三,選做題 2,編寫倒計時牌(UI)<html> <head> <title>網(wǎng)頁特效|L<BODY> <!-將以下代碼加入HTML的<Body&

19、gt;</Body>之間-><SCRIPT language=JavaScript1.2> function setcountdown(theyear,themonth,theday) yr=theyear;mo=themonth;da=theday  setcountdown(2010,6,7) var occasion="2010高考時間" var message_on_occasion="盼望已久的時刻終于來到了!"&#

20、160;var countdownwidth='380px' var countdownheight='40px' var countdownbgcolor='tan' var opentags='<font face="宋體"><small>' var closetags='</small></font>' var montha

21、rray=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") var crosscount='' function start_countdown() if (

22、document.layers) document.countdownnsmain.visibility="show" else if (document.all|document.getElementById) crosscount=document.getElementById&&!document.all?document.getElementById("countdownie") : countdownie countdown() if

23、60;(document.all|document.getElementById) document.write('<span id="countdownie" style="width:'+countdownwidth+' background-color:'+countdownbgcolor+'"></span>')window.onload=start_countdownfunction countdown() var

24、 today=new Date() var todayy=today.getYear() if (todayy < 1000) todayy+=1900 var todaym=today.getMonth() var todayd=today.getDate() var todayh=today.getHours() var todaymin=today.getMinutes() var todays

25、ec=today.getSeconds() var todaystring=montharraytodaym+" "+todayd+", "+todayy+" "+todayh+":"+todaymin+":"+todaysec futurestring=montharraymo-1+" "+da+", "+yr dd=Date.parse(futurestring)

26、-Date.parse(todaystring) dday=Math.floor(dd/(60*60*1000*24)*1) dhour=Math.floor(dd%(60*60*1000*24)/(60*60*1000)*1) dmin=Math.floor(dd%(60*60*1000*24)%(60*60*1000)/(60*1000)*1) dsec=Math.floor(dd%(60*60*1000*24)%(60*60*1000)%(60*1000)/1000*1) if(dday<=0&&dhour<=

27、0&&dmin<=0&&dsec<=1&&todayd=da) if (document.layers) document.countdownnsmain.document.countdownnssub.document.write(opentags+message_on_occasion+closetags) document.countdownnsmain.document.countdownnssub.document.close()  else if

28、0;(document.all|document.getElementById) crosscount.innerHTML=opentags+message_on_occasion+closetags return  else if (dday<=-1) if (document.layers) document.countdownnsmain.document.countdownnssub.document.write(opentags+"時間已經(jīng)過了!"+closetags)

29、 document.countdownnsmain.document.countdownnssub.document.close()  else if (document.all|document.getElementById) crosscount.innerHTML=opentags+"Occasion already passed! "+closetagsreturn  else if (document.layers) docu

30、ment.countdownnsmain.document.countdownnssub.document.write(opentags+dday+ " days, "+dhour+" hours, "+dmin+" minutes, and "+dsec+" seconds left until "+occasion+closetags)document.countdownnsmain.docu

31、ment.countdownnssub.document.close()  else if (document.all|document.getElementById) crosscount.innerHTML=opentags+"還有 "+dday+ " 天, "+dhour+" 小時, "+dmin+" 分, "+dsec+" 秒 就是 &qu

32、ot;+occasion+closetags  setTimeout("countdown()",1000)  </SCRIPT> <ILAYER id=countdownnsmain visibility="hide" bgColor="&countdownbgcolor;" height="&countdownheight;" width="&countdown

33、width;"><LAYER id=countdownnssub height="&countdownheight;" width="&countdownwidth;" top="0" left="0"></LAYER></ILAYER> </body>If Command1.Caption = "倒計時" ThenT

34、imer1.Enabled = TrueCommand1.Caption = "停止"Command2.Enabled = FalseElseTimer1.Enabled = FalseCommand1.Caption = "倒計時"Command2.Caption = "重設(shè)倒計時"Command2.Enabled = TrueEnd IfEnd SubPrivate

35、0;Sub Command2_Click()If Command2.Caption = "重設(shè)倒計時" ThenCall againCommand1.Enabled = TrueEnd IfEnd SubPrivate Sub Command3_Click()a = MsgBox("你確定要退出嗎")EndEnd SubPrivate Sub Form_Load()ClsText1.Te

36、xt = ""a = MsgBox("以下是一個倒計時程序,請按以下要求輸入你要倒計的時間")h = Val(InputBox("請輸入倒計時的,小時數(shù),若無填0", "小時/輸入", 0)m = Val(InputBox("請輸入倒計時的,分鐘數(shù),若無填0", "小時/輸入", 0)s = Val(InputBox("請輸入倒

37、計時的,秒數(shù),若無填0", "小時/輸入", 0)Label1.Caption = "你輸入的時間是:" & Format(h, "00") & ":" & Format(m, "00") & ":" & Format(s, "00")&#

38、160;& "如果沒有錯誤請點擊倒計時按紐"Text1.Visible = FalseEnd SubPrivate Sub Timer1_Timer()If s > 0 Thens = s - 1End IfIf s < 1 And m > 0 Thenm = m - 1s = 60End IfIf m < 1 And s = 0 And h > 0 Thenh = h - 1m = 60End IfIf h < 0 Thenh = 0End IfIf m <&