Cch t?o l?ch hng thng trong Excel

D?ch tiu ? D?ch tiu ?
ID c?a bi: 150774 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

Tm t?t

Bi vi?t ny ch?a macro Microsoft Visual Basic for Applications (Th? t?c ph?) nh?c b?n nh?p thng v nm, ?ng th?i t?o l?ch hng thng b?ng trang tnh.

Thng tin thm

Microsoft chi cung cp m?u lp trinh minh hoa ch? khng am bao di bt ky hinh thc nao du co quy inh ro rang hay ngu y. iu nay bao gm nhng khng gii han trong cac hinh thc bao hanh i vi tinh thng mai cung nh tinh thich hp cho mt muc ich cu th. Bai vit nay gia inh rng ban a quen vi ngn ng lp trinh ang c giai thich, va cng cu c s dung tao va g li quy trinh. Microsoft h tr cac ky s c th? giai thich chc nng cua quy trinh c bit, nhng ho se khng sa i nhng m?u nay cung cp chc nng c b sung hoc xy dng cac quy trinh nhm ap ng yu cu cu th cua ban.

tao l?ch, hay lam theo nhng bc sau.

Microsoft Excel 2003

  1. Tao s lam vic mi.
  2. Trn menu Cng cu, tro chut vao Macro, sau o bm Visual Basic Editor.
  3. Trn menu Chen, bm M-un.
  4. Sao chp m? Visual Basic for Applications bn d?i vo trang m-un.
  5. Trn menu T?p, b?m "ng v Quay l?i Microsoft Excel".
  6. B?m vo tab Sheet1.
  7. Trn menu Cng cu, tro chut vao Macro, sau o bm vo cc Macro.
  8. B?m vo CalendarMaker, v sau b?m vo Ch?y ? t?o l?ch.

Microsoft Excel 2007 v Excel 2010

  1. Tao s lam vic mi.
  2. Trn ruy bng dnh cho Nha phat trin, bm Visual Basic.
  3. Trn menu Chen, bm M-un.
  4. Sao chp m? Visual Basic for Applications bn d?i vo trang m-un.
  5. Trn menu T?p, b?m "ng v Quay l?i Microsoft Excel".
  6. B?m vo tab Sheet1.
  7. Trn ruy bng dnh cho Nh pht tri?n, b?m vo Macro
  8. B?m vo CalendarMaker, v sau b?m vo Ch?y ? t?o l?ch.

CH Y: N?u Ruy bng dnh cho Nh pht tri?n khng hi?n th?, h?y i t?i Tu? ch?n Excel v kch ho?t. Trong Excel 2007, b?n s? t?m th?y ruy bng trong menu Ph? bi?n c?n trong Excel 2010, b?n s? th?y trong menu Ruy bng Tu? Ch?nh.

Quy tr?nh Visual Basic M?u

  Sub CalendarMaker()

       ' Khng b?o v? trang n?u c l?ch tr?c  ? ngn l?i.
       ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
          Scenarios:=False
       ' Ngn mn h?nh nh?p nhy trong khi v? l?ch.
       Application.ScreenUpdating = False
       ' Thi?t l?p b?y l?i.
       On Error GoTo MyErrorTrap
       ' Xo khu v?c a1:g14 bao g?m b?t k? l?ch no tr?c .
       Range("a1:g14").Clear
       ' S? d?ng InputBox ? c thng v nm mong mu?n r?i ?t bi?n
       ' MyInput.
       MyInput = InputBox("Nh?p Thng v nm cho L?ch")
       ' Cho php ng?i dng ch?m d?t macro b?ng Hu? trong InputBox.
       If MyInput = "" Then Exit Sub
       ' Nh?n gi tr? ngy ?u thng ? nh?p.
       StartDay = DateValue(MyInput)
       ' Ki?m tra xem ngy c h?p l? hay khng ch? khng c?n ki?m tra ngy ?u tin c?a thng
       ' -- n?u ngy h?p l?, ?t l?i StartDay v? ngy ?u tin c?a thng.
       N?u Day(StartDay) <> 1 th?
           StartDay = DateValue(Month(StartDay) & "/1/" & _
               Year(StartDay))
       K?t thc N?u
       ' Chu?n b?  cho Thng v Nm nh ?c nu ?y ?.
       Range("a1").NumberFormat = "mmmm yyyy"
       ' Cn gi?a nh?n Thng v Nm trn a1:g1 thch h?p
       ' kch th?c, chi?u cao v ? ?m.
       V?i Range("a1:g1")
           .HorizontalAlignment = xlCenterAcrossSelection
           .VerticalAlignment = xlCenter
           .Font.Size = 18
           .Font.Bold = True
           .RowHeight = 35
       K?t thc B?ng
       ' Chu?n b? a2:g2 cho nh?n ngy c?a tu?n v?i cn gi?a, kch th?c,
       ' chi?u cao v ? ?m.
       V?i Range("a2:g2")
           .ColumnWidth = 11
           .VerticalAlignment = xlCenter
           .HorizontalAlignment = xlCenter
           .VerticalAlignment = xlCenter
           .Orientation = xlHorizontal
           .Font.Size = 12
           .Font.Bold = True
           .RowHeight = 20
       K?t thc B?ng
       ' ?t ngy trong tu?n trong a2:g2.
       Range("a2") = "Sunday"
       Range("b2") = "Monday"
       Range("c2") = "Tuesday"
       Range("d2") = "Wednesday"
       Range("e2") = "Thursday"
       Range("f2") = "Friday"
       Range("g2") = "Saturday"
       ' Chu?n b? a3:g7 cho ngy v?i cn tri/trn cng, kch th?c, chi?u cao
       ' v ? ?m.
       V?i Range("a3:g8")
           .HorizontalAlignment = xlRight
           .VerticalAlignment = xlTop
           .Font.Size = 18
           .Font.Bold = True
           .RowHeight = 21
       K?t thc B?ng
       ' ?t thng v nm ? nh?p ?y ? vo "a1".
       Range("a1").Value = Application.Text(MyInput, "mmmm yyyy")
       ' ?t bi?n v nh?n ngy no trong tu?n m thng s? b?t ?u.
       DayofWeek = WeekDay(StartDay)
       ' ?t bi?n ? xc ?nh nm v thng ring bi?t
       ' bi?n.
       CurYear = Year(StartDay)
       CurMonth = Month(StartDay)
       ' ?t bi?n v tnh ngy ?u tin c?a thng ti?p theo.
       FinalDay = DateSerial(CurYear, CurMonth + 1, 1)
       ' ?t "1" vo v? tr  c?a ngy ?u tin ?c ch?n
       ' thng d?a trn DayofWeek.
       Ch?n tr?ng h?p DayofWeek
           Trng hp 1
               Range("a3").Value = 1
           Trng hp 2
               Range("b3").Value = 1
           Trng hp 3
               Range("c3").Value = 1
           Trng hp 4
               Range("d3").Value = 1
           Trng hp 5
               Range("e3").Value = 1
           Trng hp 6
               Range("f3").Value = 1
           Trng hp 7
               Range("g3").Value = 1
       K?t thc Ch?n
       ' V?ng l?p thng qua d?i  a3:g8 tng m?i  sau "1"
       '.
       ?i v?i M?i  trong Range("a3:g8")
           RowCell = cell.Row
           ColCell = cell.Column
           ' Lm g? n?u "1" n?m trong c?t ?u tin.
           N?u cell.Column = 1 v cell.Row = 3 th?
           ' Lm g? n?u  hi?n t?i khng n?m trong c?t ?u tin.
           ElseIf cell.Column <> 1 th?
               N?u cell.Offset(0, -1).Value >= 1 th?
                   cell.Value = cell.Offset(0, -1).Value + 1
                   ' D?ng l?i khi ngy cu?i cng c?a thng ?
                   ' ?c nh?p.
                   N?u cell.Value > (FinalDay - StartDay) th?
                       cell.Value = ""
                       ' Thot kh?i v?ng l?p khi l?ch c ng s?
                       ' ngy ?c hi?n th?.
                       Thot cho
                   K?t thc N?u
               K?t thc N?u
           ' Lm g? n?u  hi?n t?i khng n?m trong Hng 3 m n?m trong C?t 1.
           ElseIf cell.Row > 3 v cell.Column = 1 th?
               cell.Value = cell.Offset(-1, 6).Value + 1
               ' D?ng l?i khi ngy cu?i cng c?a thng ? ?c nh?p.
               N?u cell.Value > (FinalDay - StartDay) th?
                   cell.Value = ""
                   ' Thot kh?i v?ng l?p khi l?ch c ng s? ngy
                   ' ?c hi?n th?.
                   Thot cho
               K?t thc N?u
           K?t thc N?u
       Ti?p theo

       ' T?o  M?c nh?p, ?nh d?ng  ? gi?a, ng?t d?ng v ?ng vi?n
       ' kho?ng ngy.
       Cho x = 0 ?n 5
           Range("A4").Offset(x * 2, 0).EntireRow.Insert
           V?i Range("A4:G4").Offset(x * 2, 0)
               .RowHeight = 65
               .HorizontalAlignment = xlCenter
               .VerticalAlignment = xlTop
               .WrapText = True
               .Font.Size = 10
               .Font.Bold = False
               ' M? kho cc  ny ? c th? nh?p vn b?n sau sau khi
               ' trang ?c b?o v?.
               .Locked = False
           K?t thc B?ng
           ' ?t ?ng vi?n xung quanh kh?i ngy.
           V?i Range("A3").Offset(x * 2, 0).Resize(2, _
           7).Borders(xlLeft)
               .Weight = xlThick
               .ColorIndex = xlAutomatic
           K?t thc B?ng

           V?i Range("A3").Offset(x * 2, 0).Resize(2, _
           7).Borders(xlRight)
               .Weight = xlThick
               .ColorIndex = xlAutomatic
           K?t thc B?ng
           V?i ("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _
              Weight:=xlThick, ColorIndex:=xlAutomatic
       Ti?p theo
       N?u Range("A13").Value = "" th? Range("A13").Offset(0, 0) _
          .Resize(2, 8).EntireRow.Delete
       ' T?t ?ng l?i.
       ActiveWindow.DisplayGridlines = False
       ' B?o v? trang ? ngn ghi  ngy.
       ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
          Scenarios:=True

       ' ?nh l?i kch th?c c?a s? ? hi?n th? t?t c? l?ch (c th? ph?i ?c i?u ch?nh
       ' ? c?u h?nh video).
       ActiveWindow.WindowState = xlMaximized
       ActiveWindow.ScrollRow = 1

       ' Cho php mn h?nh v? l?i v?i l?ch hi?n th?.
       Application.ScreenUpdating = True
       ' Ngn i t?i b?y l?i tr? khi t?m th?y l?i b?ng cch thot  Ph?
       ' ? y.
       Thot  Ph?
   ' L?i gip msgbox pht hi?n s? c?, cung c?p  nh?p m?i, 
   ' v ti?p t?c t?i ni ? gy ra l?i.
   MyErrorTrap:
       MsgBox "C th? b?n ? khng nh?p Thng v Nm ng". _
           & Chr(13) & "Vi?t Thng khng ng" _
           & " (ho?c s? d?ng 3 ch? vi?t t?t)" _
           & Chr(13) & "v 4 ch? s? cho Nm"
       MyInput = InputBox("Nh?p Thng v Nm cho L?ch")
       N?u MyInput = "" th? Exit Sub
       Ti?p t?c
   Ch?m d?t  ph?
				
B?n c th? thm m? khc ? tu? ch?nh l?ch nh?m p ?ng yu c?u c?a m?nh. Chn thm hng cho m?c nh?p trn mn h?nh cho m?i ngy ho?c thay ?i kch th?c mn h?nh ? xem t?t c? l?ch d?a trn kch th?c v ? phn gi?i mn h?nh.

Thu?c tnh

ID c?a bi: 150774 - L?n xem xt sau cng: 02 Thang Mi Hai 2013 - Xem xt l?i: 4.0
p d?ng
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
T? kha:
kbautomation kbdtacode kbhowto kbprogramming KB150774

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com