Ãö©ó½L¤¤¶}±ÒDDE¨C¤­¤ÀÄÁ§ì¤@¦¸

8BQGIGIGAGA

' DDE ¸ê®Æ¬ö¿ý°ÝÃD
Option Explicit
Dim actEnabled As Boolean
Dim cIndex As Single

Private Sub Workbook_Open()
    ' ¥H¤U¥|¦C¸ê®Æ¤§³]©w¡A¥i°t¦X¹ê§@¡B©Î´ú¸Õ¤§¥Øªº¡Aª½±µ¦b "sheet1" «ü©w¤§³]©wÄæ¡A±oÀH®É¤©¥H²§°Ê¡C
    If (Sheets("sheet1").Range("BA1").Value = "") Then Sheets("sheet1").Range("BA1").Value = "08:45:00"   ' °²³]C6Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
    If (Sheets("sheet1").Range("BA2").Value = "") Then Sheets("sheet1").Range("BA2").Value = "13:45:59"   ' D6Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
    If (Sheets("sheet1").Range("BB1").Value = "") Then Sheets("sheet1").Range("BB1").Value = "00:01:00"   ' ¬ö¿ý¸ê®Æ¶×¤J¬Û¹j®É¶¡¡A¦p¨C¹j¤@¤ÀÄÁ¼g¤J¤@¦¸¡C
    If (Sheets("sheet1").Range("BB2").Value = "") Then Sheets("sheet1").Range("BB2").Value = 0            ' ¬ö¿ý¤w¶×¤J¸ê®Æ¦C¼Æ¡C

    If (TimeValue(Now) > Sheets("sheet1").Range("BA2").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹L D6 ªºÀç·~®É¬q¡A«h©I¥s.......
        Call stopProcedure
    Else                                                                 ' ¤Ï¤§¦b D6 ³]©w®É¶¡¥H«e¡A«h©I¥s.......
        Call startProcedure
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Call actStop
End Sub

Sub startProcedure()       ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    Call actStart
End Sub

Sub stopProcedure()        ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
   Call actStop
End Sub

Sub newTitle()
    Sheets(2).[A1].Resize(, 4) = Sheets(1).[A1:D1].Value  ' ®M¤W§A±ý¶×¤J¸ê®ÆªºªíÀY¦WºÙ
End Sub

Sub Starter()
    If (actEnabled = True And TimeValue(Now) >= Sheets("sheet1").Range("BA1").Value And TimeValue(Now) <= Sheets("sheet1").Range("BA2").Value) Then
        cIndex = Sheets("sheet1").Range("BB2").Value

        If (cIndex = 0) Then Call newTitle  ' newTitle µ{§Ç (¥Ñ¨Ï¥ÎªÌ¦Û¦æ©w¸q) ¬O±N²Ä¤@¦Cªº¸ê®Æ©ïÀY¦WºÙ¼g¤J¨ìsheet2¡F¦p¤é´Á¡B®É¶¡ªº¹ïÀ³Äæ¦ì¸ê®Æµ¥¡C

        Sheets("sheet1").Range("BB2").Value = cIndex + 1       ' ¬ö¿ý¦C¸¹¥[¤@¡C

        ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p A1¡BB1¡BC1¡BD1 ¹ïÀ³ªº¥i¯à¬O¤º½L¡B¥~½L¡B¦¨¥æ¡Bº¦¶^µ¥µ¥¡A¥H¦¹Ãþ±À¡C
        Sheets(2).[A65536].End(xlUp).Offset(1).Resize(, 4) = Sheets(1).[A2:D2].Value

        cIndex = Sheets("sheet1").Range("BB2").Value      ' ¤Á°O Counter (­p¼Æ¾¹) ­n¥[¤@¡A§_«h¥Ã»·¬°¹s (·íµM¤w¤]¥i¥H¤£¤©¬ö¿ý¸ê®Æ¦C­z¡A¨Ì­Ó¤H²ß©Ê)¡C
    End If
End Sub

Sub onStarter()
    If Not IsError(Sheets(1).[A2]) Then Call Starter
    If actEnabled Then Call actStart
End Sub

Sub actStart()
    actEnabled = True
   
    Application.OnTime (Now + Sheets("sheet1").Range("BB1").Value), "ThisWorkBook.onStarter"     ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤­¤ÀÄÁ¼g¤J¤@¦¸)
End Sub

Sub actStop()
    actEnabled = False

    On Error Resume Next
    Application.OnTime Now, "ThisWorkBook.onStarter", , False
End Sub

¥Î³o®M¤U¥h­×§ï ¦ý¬O½L¤¤¶}±Ò®É§ì¨ú®É¶¡³£¤£¬O ¤­¤ÀÄÁKªº®É¶¡ ¨Ò¦p: 11:59:31
½Ð°Ý¸Ó¦p¦ó­×§ï Åý¥L¦b ¤­¤ÀÄÁ¾ã¤À§ì¨ú

c_c_lai

¦^´_ 1# 8BQGIGIGAGA
½Ð¤W¶Ç§Aªº Excel ÀɮסI

8BQGIGIGAGA

Àɮצb¦¹ ½Ð¤j¤jÀ°¦£:)

c_c_lai

¦^´_ 3# 8BQGIGIGAGA
¸Õ¸Õ¬Ý¡I

1. Sub onStarter()
   
2.     Dim nums As Integer
   
3.     Dim tmStr As String
   
4.    
   
5.     tmStr = Format(Sheets("sheet1").Range("BB1").Value, "hh:mm:ss")
   
6.     nums = (Left(tmStr, Len(tmStr) - 6) * 3600) + (Mid(tmStr, Len(tmStr) - 4, 2) * 60) + (Right(tmStr, 2) * 1)
   
7. 
   
8.     If (IIf(nums >= 60, Minute(Time)*60+Second(Time), Second(Time)) Mod nums) = 0 Then  ' ¶¡¹j nums ¤À¡B¬í
   
9.          If Not IsError(Sheets(1).[A2]) Then Call Starter
   
10.          If actEnabled Then Call actStart
    
11.     Else
    
12.         Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.onStarter"    ' ¦Ò¼{¨C¹j .. ¬í°O¿ý¤@¦¸
    
13.     End If
    
14. End Sub
    
15. 
    
16. Sub actStart()
    
17.     actEnabled = True
    
18.    
    
19.     Application.OnTime (Now + TimeValue("00:00:01")),  "ThisWorkBook.onStarter"     ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤­¤ÀÄÁ¼g¤J¤@¦¸)
    
20. End Sub

½Æ»s¥N½X

8BQGIGIGAGA

¤j¤j§Ú¥[¶i¥h¥H«áÅܦ¨¤£·|¦s¸ê®Æ¤F@@

c_c_lai

¤j¤j§Ú¥[¶i¥h¥H«áÅܦ¨¤£·|¦s¸ê®Æ¤F@@
8BQGIGIGAGA µoªí©ó 2013-1-28 11:54

§A§âµ{¦¡¥[¨ì­þ¸Ì¡H
¦^ÂЮɽпï«ö "¦^´_" ¿ï¯Ã¡A§_«h·í¨Æ¤H¤£ª¾¹D§A¤w¦^´_¡I

8BQGIGIGAGA

¦^´_ 6# c_c_lai
¤£¬O¨ú¥N­ì¥»ªº¨â­Ó sub¶Ü

c_c_lai

¦^´_  c_c_lai
¤£¬O¨ú¥N­ì¥»ªº¨â­Ó sub¶Ü
8BQGIGIGAGA µoªí©ó 2013-1-29 08:58

¨S¿ù¡I
©Ò¦³µ{¦¡½XÀ³¸m©ñ©ó ThisWorkbook ¤º¡A
Private Sub Workbook_Open() ¤~·|¦³©Ò¦^À³¡A
¦pªG§A¨Ï¥Î Private Sub Auto_Open() «K­n©ñ¸m©ó ¼Ò²Õ ¤º¡C
¨º»ò½Ð°Ý§A§âµ{¦¡¥[¨ì­þ¸Ì¥h¤F¡H

8BQGIGIGAGA

¦^´_ 8# c_c_lai


    ¥Ñ©ó¤£ª¾¹D«ç»ò¸ÑÄÀ ½Ð¬Ý Book1.rar (26.16 KB)

ÀÉ®×
¤U¸ü¦¸¼Æ: 23

2013-1-29 22:02

c_c_lai

¦^´_ 9# 8BQGIGIGAGA
°t¦X§Aªº DDE ¸ê®Æµyµy­×§ï¤F¤@¨Ç¡A¸Õ¸Õ¬Ý§a¡I

1. ' DDE ¸ê®Æ¬ö¿ý°ÝÃD
   
2. Option Explicit
   
3. Dim actEnabled As Boolean
   
4. Dim i As Single
   
5. 
   
6. Private Sub Workbook_Open()
   
7.     ' ¥H¤U¥|¦C¸ê®Æ¤§³]©w¡A¥i°t¦X¹ê§@¡B©Î´ú¸Õ¤§¥Øªº¡Aª½±µ¦b "sheet1" «ü©w¤§³]©wÄæ¡A±oÀH®É¤©¥H²§°Ê¡C
   
8.     If (Sheets("sheet1").Range("BA1").Value = "") Then Sheets("sheet1").Range("BA1").Value = "08:45:00"   ' °²³]C6Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
   
9.     If (Sheets("sheet1").Range("BA2").Value = "") Then Sheets("sheet1").Range("BA2").Value = "13:45:59"   ' D6Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
   
10.     If (Sheets("sheet1").Range("BB1").Value = "") Then Sheets("sheet1").Range("BB1").Value = "00:05:00"   ' ¬ö¿ý¸ê®Æ¶×¤J¬Û¹j®É¶¡¡A¦p¨C¹j¤@¤ÀÄÁ¼g¤J¤@¦¸¡C
    
11.     If (Sheets("sheet1").Range("BB2").Value = "") Then Sheets("sheet1").Range("BB2").Value = 0            ' ¬ö¿ý¤w¶×¤J¸ê®Æ¦C¼Æ¡C
    
12. 
    
13.     If (TimeValue(Now) > Sheets("sheet1").Range("BA2").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹L D6 ªºÀç·~®É¬q¡A«h©I¥s.......
    
14.         Call stopProcedure
    
15.     Else                                                                 ' ¤Ï¤§¦b D6 ³]©w®É¶¡¥H«e¡A«h©I¥s.......
    
16.         Call startProcedure
    
17.     End If
    
18. End Sub
    
19. 
    
20. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
21.     On Error Resume Next
    
22.     Call actStop
    
23. End Sub
    
24. 
    
25. Sub startProcedure()        ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    
26.     Call actStart
    
27. End Sub
    
28. 
    
29. Sub stopProcedure()        ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    
30.    Call actStop
    
31. End Sub
    
32. 
    
33. Sub newTitle()
    
34.     Sheets(2).[A1].Resize(, 7) = Sheets(1).[A1:G1].Value  ' ®M¤W§A±ý¶×¤J¸ê®ÆªºªíÀY¦WºÙ
    
35. End Sub
    
36. 
    
37. Sub Starter()
    
38.     Dim cIndex As Single
    
39.    
    
40.     If (actEnabled = True And TimeValue(Now) >= Sheets("sheet1").Range("BA1").Value And TimeValue(Now) <= Sheets("sheet1").Range("BA2").Value) Then
    
41.         cIndex = Sheets(2).[A65536].End(xlUp).Row
    
42. 
    
43.         If (cIndex = 1) Then Call newTitle  ' newTitle µ{§Ç (¥Ñ¨Ï¥ÎªÌ¦Û¦æ©w¸q) ¬O±N²Ä¤@¦Cªº¸ê®Æ©ïÀY¦WºÙ¼g¤J¨ìsheet2¡F¦p¤é´Á¡B®É¶¡ªº¹ïÀ³Äæ¦ì¸ê®Æµ¥¡C
    
44. 
    
45.         Sheets("sheet1").Range("BB2").Value = cIndex       ' ¬ö¿ý¦C¸¹¡C
    
46. 
    
47.         ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p A1¡BB1¡BC1¡BD1 ¹ïÀ³ªº¥i¯à¬O¤º½L¡B¥~½L¡B¦¨¥æ¡Bº¦¶^µ¥µ¥¡A¥H¦¹Ãþ±À¡C
    
48.         Sheets(2).[A65536].End(xlUp).Offset(1).Resize(, 7) = Sheets(1).[A2:G2].Value
    
49.     End If
    
50. End Sub
    
51. 
    
52. Sub onStarter()
    
53.     Dim nums As Integer
    
54.     Dim tmStr As String
    
55.    
    
56.     tmStr = Format(Sheets("sheet1").Range("BB1").Value, "hh:mm:ss")
    
57.     nums = (Left(tmStr, Len(tmStr) - 6) * 3600) + (Mid(tmStr, Len(tmStr) - 4, 2) * 60) + (Right(tmStr, 2) * 1)
    
58. 
    
59.     If (IIf(nums >= 60, Minute(TIME) * 60 + Second(TIME), Second(TIME)) Mod nums) = 0 Then ' ¶¡¹j nums ¤À¡B¬í
    
60.          If Not IsError(Sheets(1).[C2]) Then Call Starter                         ' ¦Ò¼{¨C¹j ..¤­¤ÀÄÁ°O¿ý¤@¦¸
    
61.          If actEnabled Then Call actStart
    
62.     Else
    
63.         Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.onStarter"
    
64.     End If
    
65. End Sub
    
66. 
    
67. Sub actStart()
    
68.     If actEnabled Then
    
69.         ' ²Ä¤G¦¸(§t)¥H«á§¡¥H³]©w¤§ "¶¡¹j®É¬q" ¨Ó³B²z°õ¦æ§Çªº§@·~¡C
    
70.         Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.onStarter"
    
71.     Else
    
72.         actEnabled = True
    
73.         
    
74.         ' ±N²Ä¤@¦¸±Ò°Ê®É¶¡§ó§ï¬°³]©w "¶}½L®É¶¡"«e¡A¦pªG¶}±Ò Excel ®É¡A¤w¸g¹L¤F³]©w "¶}½L®É¶¡"¡A«h¤@¶i¤J¨t²Î§Yª½±µ¥h°õ¦æ¬ö¿ý§@·~¡C
    
75.          If (TimeValue(Now) <= Sheets("sheet1").Range("BA1").Value) Then
    
76.              Application.OnTime (TimeValue("08:45:00")), "ThisWorkbook.onStarter"
    
77.          Else
    
78.             ' ¨t²Î­è³s¤W DDE ¦Ü¸ê®Æ¶×¤JExcel¤u§@ªí³æ¡A¶·¦³¤@­Ó½w½Ä®É¬q¡A
    
79.             ' ³o®É¦pªG°¨¤W¥h§ì¨úDDE¸ê®Æ¡A·|¦³«¬ºA¤£²Åªº¿ù»~°T®§²£¥Í¡A¨Ã¤¤Â_°õ¦æ§Çªº§@·~¡C
    
80.             Application.OnTime (Now + TimeValue("00:00:05")), "ThisWorkbook.onStarter"
    
81.          End If
    
82.     End If
    
83. End Sub
    
84. 
    
85. Sub actStop()
    
86.     actEnabled = False
    
87. 
    
88.     On Error Resume Next
    
89.     Application.OnTime Now, "ThisWorkBook.onStarter", , False
    
90. End Sub

½Æ»s¥N½X