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

[¤À¨É] ½L¤¤ DDE ¦sÀÉ»P VBA ªº¹ê»ÚÀ³¥Î½d¨Ò

[¤À¨É] ½L¤¤ DDE ¦sÀÉ»P VBA ªº¹ê»ÚÀ³¥Î½d¨Ò

¶K¤W½L¤¤ DDE ¦sÀÉ»P VBA ªº¹ê»ÚÀ³¥Î½d¨Ò¡A¨Ñ¤j®a°Ñ¦ÒÀ³¥Î (¬è¯à´¶´ç²³¥Í)
³o¤]¬O¤@¯ë¤H¦b¹ê°È¹B¥Î¤W±`¸I¨ìªº°ÝÃDª¼ÂI¡A»P¨ä­W«ä§x´b¤£ª¾¦p¦ó°_µ§
¤£¦p¤À¨É©Òª¾¨Ï¤HÁŵM¹y®©¡C
§Æ±æ¤j®a¥H«á³£¯à¦¨¬°°ª¤H¡A¨Ã¬è«ü¾É«ü¥¿¡I
½L¤¤ DDE ¦sÀɪº¹ê»ÚÀ³¥Î½d¨Ò.rar (1.11 KB)

§Ú®tÂI§Ñ¤F»P§Ú¾Ö¦³¤p¾Ç¥Íµ¥¯Åªº¦P¾Ç­Ì¬OµLªk¤U¸üªþ¥óªº¡A
©Ò¥H§Ú¤S±N¥¦ª½±µ¶K¤F¥X¨Ó¡A¤è«K¤j®a¾\Äý¡C
  1. ' ½L¤¤ DDE ¦sÀɪº¹ê»ÚÀ³¥Î½d¨Ò

  2. Option Explicit

  3. Dim actEnabled As Boolean
  4. Dim index As Single

  5. Private Sub Workbook_Open()
  6.     If (Sheets("¤u§@ªí1").Range("AA1").Value = "") Then Sheets("¤u§@ªí1").Range("AA1").Value = "08:45:00"   ' °²³]AA1Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
  7.     If (Sheets("¤u§@ªí1").Range("AA2").Value = "") Then Sheets("¤u§@ªí1").Range("AA2").Value = "13:45:59"   ' AA2Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
  8.     If (Sheets("¤u§@ªí1").Range("AA3").Value = "") Then Sheets("¤u§@ªí1").Range("AA3").Value = 0            ' ¬ö¿ý³Ì«á¸ê®Æ¶×¤J¤§¦C¸¹ (Rows)¡C
  9.     If (Sheets("¤u§@ªí1").Range("AA4").Value = "") Then Sheets("¤u§@ªí1").Range("AA4").Value = "00:00:10"   ' ¬ö¿ý¸ê®Æ¶×¤J¬Û¹j®É¶¡¡A¦p¨C¹j¤Q¬í¼g¤J¤@¦¸¡C

  10.     If (TimeValue(Now) > Sheets("¤u§@ªí1").Range("AA2").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹LAA2ªº®É¬q¡A«h©I¥s.......
  11.         Call stopProcedure
  12.     Else                                                                  ' ¤Ï¤§¡A«h©I¥s.......
  13.         Call startProcedure
  14.     End If
  15. End Sub

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


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

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

  26. Sub Starter()
  27.     If (actEnabled = True And TimeValue(Now) >= Sheets("¤u§@ªí1").Range("AA1").Value And TimeValue(Now) <= Sheets("¤u§@ªí1").Range("AA2").Value) Then
  28.         index = Sheets("¤u§@ªí1").Range("AA3").Value

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

  30.         Sheets("¤u§@ªí1").Range("AA3").Value = index + 1       ' ¬ö¿ý¦C¸¹¥[¤@¡C
  31.         Sheets("¤u§@ªí2").Cells(index + 2, 1).Value = Date
  32.         Sheets("¤u§@ªí2").Cells(index + 2, 2).Value = TimeValue(Now)
  33.         ' Sheets("¤u§@ªí2").Cells(index + 2, 3).Value = Sheets("¤u§@ªí1").Cells(1, 5).Value
  34.         '
  35.         ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p R1C5 ¹ïÀ³ªº¥i¯à¬O¦¬½L»ùµ¥µ¥¡C
  36.         '
  37.     End If
  38. End Sub


  39. Sub onStarter()
  40.     Call Starter
  41.     If actEnabled Then Call actStart
  42. End Sub

  43. Sub actStart()
  44.     actEnabled = True
  45.     Application.OnTime (Now + Sheets("¤u§@ªí1").Range("AA4").Value), "ThisWorkBook.onStarter"   ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤Q¬í¼g¤J¤@¦¸)
  46. End Sub

  47. Sub actStop()
  48.     actEnabled = False

  49.     On Error Resume Next
  50.     Application.OnTime Now, "ThisWorkBook.onStarter", , False
  51. End Sub
½Æ»s¥N½X

TOP

³ø§i:®M¦b2010 ¦³°ÝÃD
½s¿è¿ù»~¡A¸Ó¦¨­û¤w¦s¦b©ó¦¹ª«¥ó¼Ò²Õ©ÒÄ~©Óªº¼Ò²Õ¤¤
Dim index As Single
==========
¦]¬°ÁÙ¬O¤p¾Ç¥Í¡A©Ò¥H°ÝÃD¯S§O¦h

TOP

¦^´_ 3# ajagow
³o­Ó¼Ò²Õ¦®¦b´£¨Ñ§A¦p¦ó¦b¹ê°È¤W¼¶¼g¤@­ÓÄÝ©ó§A­Ó¤Hªºµ{¦¡½X½d¨Ò¡A
¥¦ªº½T¬O¤@²Õ¯uªºµ{¦¡¼Ò²Õ¡A§A¥u¬O§â¹ïÀ³ªºÄæ¦ì¥[¥H­×¹¢¡A¦A¥[¤W§A­Ó¤Hªº«ä¦Ò¼Ò¦¡¥[¥H®M¤J²Õ¦X¡A
´N¦¨¤F§A©Ò»Ý­nªº§¹¾ã¤§µ{¦¡½X¤F¡C
§Ú¦A§â¥¦¶K¤@¦¸¡Aµ{¦¡½X½Ð½Æ»s¨ì ThisWorkbook ¤º¡Aª½±µ½sĶ¤]µL°ÝÃDªº¡C
  1. ' ½L¤¤ DDE ¦sÀɪº¹ê»ÚÀ³¥Î½d¨Ò

  2. Option Explicit

  3. Dim actEnabled As Boolean
  4. Dim index As Single

  5. Private Sub Workbook_Open()
  6.     If (Sheets("¤u§@ªí1").Range("AA1").Value = "") Then Sheets("¤u§@ªí1").Range("AA1").Value = "08:45:00"   ' °²³]AA1Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
  7.     If (Sheets("¤u§@ªí1").Range("AA2").Value = "") Then Sheets("¤u§@ªí1").Range("AA2").Value = "13:45:59"   ' AA2Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
  8.     If (Sheets("¤u§@ªí1").Range("AA3").Value = "") Then Sheets("¤u§@ªí1").Range("AA3").Value = 0            ' ¬ö¿ý³Ì«á¸ê®Æ¶×¤J¤§¦C¸¹ (Rows)¡C
  9.     If (Sheets("¤u§@ªí1").Range("AA4").Value = "") Then Sheets("¤u§@ªí1").Range("AA4").Value = "00:00:10"   ' ¬ö¿ý¸ê®Æ¶×¤J¬Û¹j®É¶¡¡A¦p¨C¹j¤Q¬í¼g¤J¤@¦¸¡C

  10.     If (TimeValue(Now) > Sheets("¤u§@ªí1").Range("AA2").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹LAA2ªº®É¬q¡A«h©I¥s.......
  11.         Call stopProcedure
  12.     Else                                                                  ' ¤Ï¤§¡A«h©I¥s.......
  13.         Call startProcedure
  14.     End If
  15. End Sub

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


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

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

  26. Sub nnewTitle()
  27.    ' ®M¤W§A±ý¶×¤J¸ê®ÆªºªíÀY¦WºÙ
  28. End Sub

  29. Sub Starter()
  30.     If (actEnabled = True And TimeValue(Now) >= Sheets("¤u§@ªí1").Range("AA1").Value And TimeValue(Now) <= Sheets("¤u§@ªí1").Range("AA2").Value) Then
  31.         index = Sheets("¤u§@ªí1").Range("AA3").Value

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

  33.         Sheets("¤u§@ªí1").Range("AA3").Value = index + 1       ' ¬ö¿ý¦C¸¹¥[¤@¡C
  34.         Sheets("¤u§@ªí2").Cells(index + 2, 1).Value = Date
  35.         Sheets("¤u§@ªí2").Cells(index + 2, 2).Value = TimeValue(Now)
  36.         ' Sheets("¤u§@ªí2").Cells(index + 2, 3).Value = Sheets("¤u§@ªí1").Cells(1, 5).Value
  37.         '
  38.         ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p R1C5 ¹ïÀ³ªº¥i¯à¬O¦¬½L»ùµ¥µ¥¡C
  39.         '
  40.     End If
  41. End Sub


  42. Sub onStarter()
  43.     Call Starter
  44.     If actEnabled Then Call actStart
  45. End Sub

  46. Sub actStart()
  47.     actEnabled = True
  48.     Application.OnTime (Now + Sheets("¤u§@ªí1").Range("AA4").Value), "ThisWorkBook.onStarter"   ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤Q¬í¼g¤J¤@¦¸)
  49. End Sub

  50. Sub actStop()
  51.     actEnabled = False

  52.     On Error Resume Next
  53.     Application.OnTime Now, "ThisWorkBook.onStarter", , False
  54. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# c_c_lai

¼¶¼gµ{¦¡¡AÅܼƩR¦WÀ³¸ÓºÉ¶qÁקKVBAªº«O¯d¦r
3#©Ò´£ªº°ÝÃD¡A¦b©ó§A±NÅܼƦWºÙ©R¦W¬°index¡A³o­Ó«O¯d¦r¤£³QVBA¨t²Î©Ò±µ¨ü
¦]¬°¦¹¦WºÙ¡A¦bVBAªº»y¨¥¤¤¤w³Q«Å§i¦¨¤@­Ó­«­nªº«ü¥O¡A©Ò¥H´NÃþ¦ü­«½Æ«Å§i¡A¦Ó¾É­P¿ù»~¡C
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh
ÁÂÁ±zªº«ü¥¿¡I
¤µ«á§Ú·|¯S§O¥h¯d·NVBAªº«O¯d¦r¡A §Ú¤w±N index §ó§ï¦¨ cIndex (Check Index ¤§·N¡A¥i¨Ì¨C­Ó¤H½s½X²ßºD¦Û¦æ¥h©w¸q)
¯u¤£¦n·N«ä¯d¤U¤F¤£¦nªº¿ù»~¥Ü½d¡C

TOP

¦^´_ 5# Hsieh

ÁÂÁ ¶W¯Åª©¥D ¤ÎC_C LAI ¸Ñ»¡
index   ==> CIndex
¤w±N§ï¥¿«á°õ¦æ
¤S¦³¤G­Ó°ÝÃD   Call newTitle  &  ThisWorkBook.onStarter
¨S¦³©w¸q    newTitle
                ³o¬O­n¦bSheet2 «Ø¥ß¤@­Ó¤é´Á(A1)¡B®É¶¡(B1)¡A¦¬½L»ù(C1) ?
µLªk¶}±Ò  ThisWorkBook.onStarter

³o­Óµ{¦¡ªº¥Î·N¬O§â½L¤¤DDEªº¼Æ¦r°O¿ý¦¨¸ê®Æ¶Ü
   

ÁÂÁÂ
¯qÁ¨à

TOP

¦^´_ 7# ajagow
¹ï¤£°_¡I
¦b½d¨Ò¤¤ Sub newTitle()  (¤§«e¥»·Q¥Ñ¨Ï¥ÎªÌ¦Û¦æ¥[¤W¡A¦ý¬°¼W¶i§AªºÁA¸Ñ¡AÁ{®É¥[¤Jªº) »~¥´¦¨ Sub nnewTitle() ½Ð§A¦Û¦æ§ó¥¿¡A
½Ð¯d·N ThisWorkBook.onStarter¡A¦b ThisWorkBook.onStarter¤¤¬O¦³¤@ÂI "." ªº (ThisWorkBook + "." +  onStarter)¡A
¦¹·N§Y¬O "·í³]©w®É¬q¨ì®É§Y¤©°õ¦æ onStarter¦¹µ{¦¡¬q"

Sub onStarter()
    Call Starter
    If actEnabled Then Call actStart
End Sub

onStarter µ{¦¡¬q·|¥h°õ¦æ Starter (¦¹µ{¦¡¬q­t³d±N±qDDE¶×¤J¤§½L¤¤¸ê°T¡A¹ê»Ú¼g¤J¨ì§A«ü©w½s¼gªº¤u§@ªí³æ¤º)¡A
°õ¦æ§¹²¦¤S·|¨ì actStart ªº±Æµ{¡C ¦p¦¹¤£Â_´`Àô§@·~¡Aª½¨ì±ø¥óº¡¨¬¬°¤î¡C

TOP

µLªk°õ¦æ¸Ó¥¨¶°

thisworkbookonstarter.JPG
2012-4-14 15:29
¦^´_ 8# c_c_lai
µLªk°õ¦æ¸Ó¥¨¶°¡A¤£ª¾°ÝÃD¥X¦b­þ¸Ì¡C
§Ú¸Õ¹L«Ü¦h¦¸¡AÁÙ¬O¤£¯à§¹¦¨³o»ò¦nªºµ{¦¡¡C
½Ð¨DÀ°¦£
¯qÁ¨à

TOP

¦^´_ 9# ajagow
³Â·Ð¤W¶ÇÀɮסI

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD