ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

' 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 ¤­¤ÀÄÁ¾ã¤À§ì¨ú
¥æ©ö¤H

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

TOP

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

Book1.rar (25.49 KB)

¥æ©ö¤H

TOP

¦^´_ 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.     If (IIf(nums >= 60, Minute(Time)*60+Second(Time), Second(Time)) Mod nums) = 0 Then  ' ¶¡¹j nums ¤À¡B¬í
  8.          If Not IsError(Sheets(1).[A2]) Then Call Starter
  9.          If actEnabled Then Call actStart
  10.     Else
  11.         Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.onStarter"    ' ¦Ò¼{¨C¹j .. ¬í°O¿ý¤@¦¸
  12.     End If
  13. End Sub

  14. Sub actStart()
  15.     actEnabled = True
  16.    
  17.     Application.OnTime (Now + TimeValue("00:00:01")),  "ThisWorkBook.onStarter"     ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤­¤ÀÄÁ¼g¤J¤@¦¸)
  18. End Sub
½Æ»s¥N½X

TOP

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

TOP

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

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

TOP

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

TOP

¦^´_  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

TOP

¦^´_ 8# c_c_lai


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

TOP

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

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

  17. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  18.     On Error Resume Next
  19.     Call actStop
  20. End Sub

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

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

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

  30. Sub Starter()
  31.     Dim cIndex As Single
  32.    
  33.     If (actEnabled = True And TimeValue(Now) >= Sheets("sheet1").Range("BA1").Value And TimeValue(Now) <= Sheets("sheet1").Range("BA2").Value) Then
  34.         cIndex = Sheets(2).[A65536].End(xlUp).Row

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

  36.         Sheets("sheet1").Range("BB2").Value = cIndex       ' ¬ö¿ý¦C¸¹¡C

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

  41. Sub onStarter()
  42.     Dim nums As Integer
  43.     Dim tmStr As String
  44.    
  45.     tmStr = Format(Sheets("sheet1").Range("BB1").Value, "hh:mm:ss")
  46.     nums = (Left(tmStr, Len(tmStr) - 6) * 3600) + (Mid(tmStr, Len(tmStr) - 4, 2) * 60) + (Right(tmStr, 2) * 1)

  47.     If (IIf(nums >= 60, Minute(TIME) * 60 + Second(TIME), Second(TIME)) Mod nums) = 0 Then ' ¶¡¹j nums ¤À¡B¬í
  48.          If Not IsError(Sheets(1).[C2]) Then Call Starter                         ' ¦Ò¼{¨C¹j ..¤­¤ÀÄÁ°O¿ý¤@¦¸
  49.          If actEnabled Then Call actStart
  50.     Else
  51.         Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.onStarter"
  52.     End If
  53. End Sub

  54. Sub actStart()
  55.     If actEnabled Then
  56.         ' ²Ä¤G¦¸(§t)¥H«á§¡¥H³]©w¤§ "¶¡¹j®É¬q" ¨Ó³B²z°õ¦æ§Çªº§@·~¡C
  57.         Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.onStarter"
  58.     Else
  59.         actEnabled = True
  60.         
  61.         ' ±N²Ä¤@¦¸±Ò°Ê®É¶¡§ó§ï¬°³]©w "¶}½L®É¶¡"«e¡A¦pªG¶}±Ò Excel ®É¡A¤w¸g¹L¤F³]©w "¶}½L®É¶¡"¡A«h¤@¶i¤J¨t²Î§Yª½±µ¥h°õ¦æ¬ö¿ý§@·~¡C
  62.          If (TimeValue(Now) <= Sheets("sheet1").Range("BA1").Value) Then
  63.              Application.OnTime (TimeValue("08:45:00")), "ThisWorkbook.onStarter"
  64.          Else
  65.             ' ¨t²Î­è³s¤W DDE ¦Ü¸ê®Æ¶×¤JExcel¤u§@ªí³æ¡A¶·¦³¤@­Ó½w½Ä®É¬q¡A
  66.             ' ³o®É¦pªG°¨¤W¥h§ì¨úDDE¸ê®Æ¡A·|¦³«¬ºA¤£²Åªº¿ù»~°T®§²£¥Í¡A¨Ã¤¤Â_°õ¦æ§Çªº§@·~¡C
  67.             Application.OnTime (Now + TimeValue("00:00:05")), "ThisWorkbook.onStarter"
  68.          End If
  69.     End If
  70. End Sub

  71. Sub actStop()
  72.     actEnabled = False

  73.     On Error Resume Next
  74.     Application.OnTime Now, "ThisWorkBook.onStarter", , False
  75. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD