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

c_c_lai

¶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)

¤U¸ü¦¸¼Æ: 174

2012-3-29 12:54

c_c_lai

§Ú®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. 
   
3. Option Explicit
   
4. 
   
5. Dim actEnabled As Boolean
   
6. Dim index As Single
   
7. 
   
8. Private Sub Workbook_Open()
   
9.     If (Sheets("¤u§@ªí1").Range("AA1").Value = "") Then Sheets("¤u§@ªí1").Range("AA1").Value = "08:45:00"   ' °²³]AA1Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
   
10.     If (Sheets("¤u§@ªí1").Range("AA2").Value = "") Then Sheets("¤u§@ªí1").Range("AA2").Value = "13:45:59"   ' AA2Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
    
11.     If (Sheets("¤u§@ªí1").Range("AA3").Value = "") Then Sheets("¤u§@ªí1").Range("AA3").Value = 0            ' ¬ö¿ý³Ì«á¸ê®Æ¶×¤J¤§¦C¸¹ (Rows)¡C
    
12.     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
    
13. 
    
14.     If (TimeValue(Now) > Sheets("¤u§@ªí1").Range("AA2").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹LAA2ªº®É¬q¡A«h©I¥s.......
    
15.         Call stopProcedure
    
16.     Else                                                                  ' ¤Ï¤§¡A«h©I¥s.......
    
17.         Call startProcedure
    
18.     End If
    
19. End Sub
    
20. 
    
21. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
22.     On Error Resume Next
    
23.     Call actStop
    
24. End Sub
    
25. 
    
26. 
    
27. Private Sub startProcedure()       ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    
28.     Call actStart
    
29. End Sub
    
30. 
    
31. Private Sub stopProcedure()        ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    
32.    Call actStop
    
33. End Sub
    
34. 
    
35. Sub Starter()
    
36.     If (actEnabled = True And TimeValue(Now) >= Sheets("¤u§@ªí1").Range("AA1").Value And TimeValue(Now) <= Sheets("¤u§@ªí1").Range("AA2").Value) Then
    
37.         index = Sheets("¤u§@ªí1").Range("AA3").Value
    
38. 
    
39.         If (Index = 0) Then Call newTitle  '°²³]newTitleµ{§Ç(¥Ñ¨Ï¥ÎªÌ¦Û¦æ©w¸q)¬O±N²Ä¤@¦Cªº¸ê®Æ©ïÀY¦WºÙ¼g¤J¨ì¤u§@ªí2¡C ¦p¡G¤é´Á¡B®É¶¡¡BR1C5ªº¹ïÀ³Äæ¦ì¸ê®Æµ¥¡C
    
40. 
    
41.         Sheets("¤u§@ªí1").Range("AA3").Value = index + 1       ' ¬ö¿ý¦C¸¹¥[¤@¡C
    
42.         Sheets("¤u§@ªí2").Cells(index + 2, 1).Value = Date
    
43.         Sheets("¤u§@ªí2").Cells(index + 2, 2).Value = TimeValue(Now)
    
44.         ' Sheets("¤u§@ªí2").Cells(index + 2, 3).Value = Sheets("¤u§@ªí1").Cells(1, 5).Value
    
45.         '
    
46.         ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p R1C5 ¹ïÀ³ªº¥i¯à¬O¦¬½L»ùµ¥µ¥¡C
    
47.         '
    
48.     End If
    
49. End Sub
    
50. 
    
51. 
    
52. Sub onStarter()
    
53.     Call Starter
    
54.     If actEnabled Then Call actStart
    
55. End Sub
    
56. 
    
57. Sub actStart()
    
58.     actEnabled = True
    
59.     Application.OnTime (Now + Sheets("¤u§@ªí1").Range("AA4").Value), "ThisWorkBook.onStarter"   ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤Q¬í¼g¤J¤@¦¸)
    
60. End Sub
    
61. 
    
62. Sub actStop()
    
63.     actEnabled = False
    
64. 
    
65.     On Error Resume Next
    
66.     Application.OnTime Now, "ThisWorkBook.onStarter", , False
    
67. End Sub

½Æ»s¥N½X

ajagow

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

c_c_lai

¦^´_ 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. 
   
3. Option Explicit
   
4. 
   
5. Dim actEnabled As Boolean
   
6. Dim index As Single
   
7. 
   
8. Private Sub Workbook_Open()
   
9.     If (Sheets("¤u§@ªí1").Range("AA1").Value = "") Then Sheets("¤u§@ªí1").Range("AA1").Value = "08:45:00"   ' °²³]AA1Äæ¦ì¬°ªÅ¥Õ¡A«h¼g¤J¶}½L°_©l®É¶¡
   
10.     If (Sheets("¤u§@ªí1").Range("AA2").Value = "") Then Sheets("¤u§@ªí1").Range("AA2").Value = "13:45:59"   ' AA2Äæ¦ì¥ç¦P¡C(¦¹¨âÄæ¬ö¿ý°_©l²×¤î®É¶¡)
    
11.     If (Sheets("¤u§@ªí1").Range("AA3").Value = "") Then Sheets("¤u§@ªí1").Range("AA3").Value = 0            ' ¬ö¿ý³Ì«á¸ê®Æ¶×¤J¤§¦C¸¹ (Rows)¡C
    
12.     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
    
13. 
    
14.     If (TimeValue(Now) > Sheets("¤u§@ªí1").Range("AA2").Value) Then       ' ¦pªG¥Ø«e®É¶¡·~¤w¶W¹LAA2ªº®É¬q¡A«h©I¥s.......
    
15.         Call stopProcedure
    
16.     Else                                                                  ' ¤Ï¤§¡A«h©I¥s.......
    
17.         Call startProcedure
    
18.     End If
    
19. End Sub
    
20. 
    
21. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
22.     On Error Resume Next
    
23.     Call actStop
    
24. End Sub
    
25. 
    
26. 
    
27. Sub startProcedure()       ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    
28.     Call actStart
    
29. End Sub
    
30. 
    
31. Sub stopProcedure()        ' «O¯d§@¬°±±¨î¶µ¤§À³¥Îµ{§Ç¡A¦p«ö¶s¤§¥¨¶°À³¥Îµ¥¡C
    
32.    Call actStop
    
33. End Sub
    
34. 
    
35. Sub nnewTitle()
    
36.    ' ®M¤W§A±ý¶×¤J¸ê®ÆªºªíÀY¦WºÙ
    
37. End Sub
    
38. 
    
39. Sub Starter()
    
40.     If (actEnabled = True And TimeValue(Now) >= Sheets("¤u§@ªí1").Range("AA1").Value And TimeValue(Now) <= Sheets("¤u§@ªí1").Range("AA2").Value) Then
    
41.         index = Sheets("¤u§@ªí1").Range("AA3").Value
    
42. 
    
43.         If (index = 0) Then Call newTitle  '°²³]newTitleµ{§Ç(¥Ñ¨Ï¥ÎªÌ¦Û¦æ©w¸q)¬O±N²Ä¤@¦Cªº¸ê®Æ©ïÀY¦WºÙ¼g¤J¨ì¤u§@ªí2¡C ¦p¡G¤é´Á¡B®É¶¡¡BR1C5ªº¹ïÀ³Äæ¦ì¸ê®Æµ¥¡C
    
44. 
    
45.         Sheets("¤u§@ªí1").Range("AA3").Value = index + 1       ' ¬ö¿ý¦C¸¹¥[¤@¡C
    
46.         Sheets("¤u§@ªí2").Cells(index + 2, 1).Value = Date
    
47.         Sheets("¤u§@ªí2").Cells(index + 2, 2).Value = TimeValue(Now)
    
48.         ' Sheets("¤u§@ªí2").Cells(index + 2, 3).Value = Sheets("¤u§@ªí1").Cells(1, 5).Value
    
49.         '
    
50.         ' ½Æ»s±q¨é°ÓDDE¶×¤J¤§¬Û¹ïÀ³¦ì¸m¸ê®Æ¡A¦p R1C5 ¹ïÀ³ªº¥i¯à¬O¦¬½L»ùµ¥µ¥¡C
    
51.         '
    
52.     End If
    
53. End Sub
    
54. 
    
55. 
    
56. Sub onStarter()
    
57.     Call Starter
    
58.     If actEnabled Then Call actStart
    
59. End Sub
    
60. 
    
61. Sub actStart()
    
62.     actEnabled = True
    
63.     Application.OnTime (Now + Sheets("¤u§@ªí1").Range("AA4").Value), "ThisWorkBook.onStarter"   ' ¼g¤J¸ê®Æªº±Æµ{ (¥Ø«e¬O¨C¹j¤Q¬í¼g¤J¤@¦¸)
    
64. End Sub
    
65. 
    
66. Sub actStop()
    
67.     actEnabled = False
    
68. 
    
69.     On Error Resume Next
    
70.     Application.OnTime Now, "ThisWorkBook.onStarter", , False
    
71. End Sub

½Æ»s¥N½X

Hsieh

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

c_c_lai

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

ajagow

¦^´_ 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¿ý¦¨¸ê®Æ¶Ü
   

ÁÂÁÂ

c_c_lai

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

ajagow

¤U¸ü (19.75 KB)
µLªk°õ¦æ¸Ó¥¨¶°

2012-4-14 15:29

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

c_c_lai

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