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

½Ð°Ý°ª¤â­n±N¥H¤UDDE ¨C¤ÀÄÁ°O¿ý§ï¬°30¬í¦Û°Ê°O¿ý¤@¦¸­n«ç§ï

½Ð°Ý°ª¤â­n±N¥H¤UDDE ¨C¤ÀÄÁ°O¿ý§ï¬°30¬í¦Û°Ê°O¿ý¤@¦¸­n«ç§ï

§Ú¹ïVBA³£¤£À´¦bºô¤W§ä¦n¤[§ä¨ì¤@­Ó¤ñ¸û±µªñ§Ú­nªº½d¨Ò
¥i¬O¥L¬O¨C¤ÀÄÁ°O¿ý¤@¦¸
¥i¥H½Ð°ª¤âÀ°§Ú§ï¨C30¬í´N°O¿ý¤@¦¸¶Ü
¤]ÁÂÁ³o¦ì´£¨Ñ½d¨Òªººô¤Í


Option Explicit
Dim LastMin As Integer

Private Sub Workbook_Open()
    Sheets("µ¦²¤°O¿ý").Cells(4, 2) = 10
    LastMin = Minute(Time)
    Call Timer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.Timer", , False
End Sub


Public Sub Timer()
    Dim Pos As Integer, i As Integer, RangeStr As String
        
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.Timer" '¨C¬íÅã¥Ü
    Sheets("µ¦²¤°O¿ý").Cells(3, 2) = Time '±N®É¶¡show¦Üµ¦²¤ªºb3Äæ¦ì
    Dim HHMM As Integer
    HHMM = Hour(Time) * 100 + Minute(Time)
    If (HHMM < 845 Or HHMM > 1345) Then Exit Sub 'Àç·~®É¶¡¤~°õ¦æ
    If Minute(Time) <> LastMin Then '¶}©l«á°µ
        With Sheets("µ¦²¤°O¿ý")
            .Cells(4, 2) = .Cells(4, 2) + 1 '±NÅܰʦ渹¥[¤@¦æ
            Pos = .Cells(4, 2)
            .Cells(Pos, 1) = Time
            .Cells(Pos, 2) = .Cells(2, 2)
            .Cells(Pos, 3) = .Cells(2, 3)
            .Cells(Pos, 4) = .Cells(2, 4)
            .Cells(Pos, 5) = .Cells(2, 5)
            .Cells(Pos, 6) = .Cells(2, 6)
            .Cells(Pos, 7) = .Cells(2, 7)
            .Cells(Pos, 8) = .Cells(2, 8)
            .Cells(Pos, 9) = .Cells(2, 9)
            .Cells(Pos, 10) = .Cells(2, 10)
           
         
        End With
   
        LastMin = Minute(Time)
    End If
End Sub

¦^´_ 1# xu123
´N¥H§A¤â¤W®×¨Ò¨Ó°Q½×ªº¸Ü¡A§A¥u­n±N "00:00:01" (¨C¤@¬í)
­×§ï¬° "00:00:30"  (¨C¤T¤Q¬í) «K§¹¦¨¤F¡I

TOP

ÁÂÁÂc_c_lai ¤jªº¦^´_
¤U©P¶}½L¦A¸Õ¸Õ¬Ý

TOP

¦^´_ 3# xu123
¤£¶·µ¥¶}½L¡A§A¥u­n¶}±Ò¸ÓExcel ªí³æ¡A©ó 8:45 «áÆ[¹î¨C¹j30¬í¬O§_·|¼g¤J¤@µ§¬ö¿ý¡A
µª®×«K§Yª¾¾å¡C

TOP

¦^´_  xu123
¤£¶·µ¥¶}½L¡A§A¥u­n¶}±Ò¸ÓExcel ªí³æ¡A©ó 8:45 «áÆ[¹î¨C¹j30¬í¬O§_·|¼g¤J¤@µ§¬ö¿ý¡A
µª®×«K ...
c_c_lai µoªí©ó 2012-5-25 21:14

½Ð°Ý­n§ï­þ¨à?
Application.OnTime Now + TimeValue("00:00:30"), "ThisWorkbook.Timer" '¨C¬íÅã¥Ü
§Ú§ï³oÃä«á  ®É¶¡´N¤£°Ê¤]¤£°O¿ý¤F?

ÁÂÁ  ·P®¦

TOP

c_c_lai ¤j
¸Õ¹L¤F¤£¦æ£z
``§Ú§ï¶}½L®É¶¡¥h´ú¸Õ
ÁÙ¬O¤@¤ÀÄÁ°O¤@¦¸

TOP

¦^´_ 6# xu123
½Ðªþ¤WÀɮץѧڳo¤è¨ÓÆ[¹î¡A¦]¬°¨º¥»¨Ó´N¥i¦æªº¡A
§_«h§Aªº "00:00:01" ¥çµLªk°õ¦æ¡C

TOP

¦^´_ 6# xu123
ªþ¤W¹Ï¤ù¥H¤Îµ{¦¡½X¨Ñ§A¹ê°È°Ñ¦Ò¡G
¤@¡B DDE ¨C¤ÀÄÁ°O¿ý§ï¬°30¬í¦Û°Ê°O¿ý¤@¦¸­n«ç§ï [­×§ï]
  1. Option Explicit
  2. Dim counter As Single      ' ¬°¥[²`¦L¶H¡A¥Î¥¦¨ÓÅã¥Üµ{¦¡¦³§_¦b¹B¦æ³B²z

  3. Private Sub Workbook_Open()
  4.     counter = 0

  5.     Call timerStart        ' ·í¶}±Ò¥» Excel ÀɮסAµ{¦¡«h¦Û°Ê°õ¦æ timerStart µ{¦¡¬q¡C
  6. End Sub

  7. Sub timerStart()
  8.     ' ¥»½d¨Ò³]©w®É¶¡¬O±q¨C¤é¤KÂI¥|¤Q¤­¤À¶}©l°_ºâ¡A¨C¹j 30 ¬í«K·|¦Û°Ê¥h°õ¦æ ThisWorkbook µ{¦¡°Ï°ì¤ºªº ExeSelf¡C
  9.     ' ¦pªGı±oµ¥¤Ó¤[¤F¡A¤]¥i¥H¥h²§°Ê®É¬q¡C¨Ò¦p: "00:00:01"(¨C¹j 1 ¬í)¡B"00:00:10"(¨C¹j 10 ¬í)¡B"00:00:20"(¨C¹j 20 ¬í) µ¥µ¥¡C
  10.     If (TimeValue(Now) > TimeValue("08:45:00")) Then Application.OnTime (Now + TimeValue("00:00:30")), "ThisWorkbook.ExeSelf"
  11. End Sub

  12. Sub ExeSelf()
  13.     counter = counter + 1   ' counter ·|³v¦¸¥[¤@

  14.     [F8].Value = counter    ' 08:45 «á¨C¹j 30 ¬í¡A½ÐÆ[¹î F1 Äæ¦ì¼Æ¦r¬O§_·|²§°Ê¡H
  15.     Call timerStart         ' µ{¦¡¤§©Ò¥H·|¤ÏÂаõ¦æ¡A´N¦b©ó¥»¦æ¦Û°Ê¦A¥h¦^ÀY©I¥s°õ¦æ timerStart µ{¦¡¬q¤@¦¸¤§¬G¡C
  16. End Sub
½Æ»s¥N½X
¤G¡B ¦hªÅ¤O¹D (O/H/L/C) ¹ê»Ú¤§µ{¦¡¹B§@µ{¦¡½X¡A½Ð²Ó¤ßºÝ­Ù¡C
  1. Option Explicit

  2. Dim timerEnabled As Boolean  '
  3. Dim counter As Single        ' ¥Ü½d¥Î­p¼Æ¾¹
  4. Dim Ov As Single, Hv As Single, Lv As Single, Cv As Single As Single  ' ¶}½L»ù¡B³Ì°ª»ù¡B³Ì°ª»ù¡B¦¬½L»ù(¦¨¥æ»ù) µ¥³]©w¤§ÅܼơC
  5. Dim turnKey As Integer   ' §PÂ_¨C¤@½ü°j¤W¦CÅܼƪì©l­È¤§³]©w¡C
  6. Dim nums As Integer      ' ±N¨C¹j "¦h¤Ö®É¶¡" ³B²z®É¬q¡A²¾¨ì¦¹³B¨Ó³B²z¡C

  7. Private Sub Workbook_Open()
  8.     counter = 0

  9.     nums = 30            ' ³]©w¨C¹j 30 ¬í³B²z®É¬q¡A¥ç¥i§ó§ï¦¨ 10 (¨C¹j 10 ¬í)¡B20 (¨C¹j 20 ¬í)µ¥µ¥¡C
  10.                          ' ¦¹³B¤£«Øij¨Ï¥Î 1 ¬í®É¬q¨Ó³B²z¡A¦]¬°ÁÙ­n¥h¨D¥X¶}½L¡B³Ì°ª¡B³Ì§C¡B¦¨¥æ»ù¡C
  11.     timerEnabled = False
  12.     Call timerStart      ' µ{¦¡¤@±Ò©l¡A«K¥h¦Û°Ê°õ¦æ timerStart      
  13. End Sub

  14. Sub timerStart()
  15.     turnKey = 0          ' ¨C¤@½ü°j½L¤¤ªì©l­È¤§³]©w¡C

  16.     If timerEnabled Then
  17.         Application.OnTime (Now), "ThisWorkbook.ExeSelf"
  18.     Else
  19.         ' ¨t²Î­è³s¤W DDE ¦Ü¸ê®Æ¶×¤JExcel¤u§@ªí³æ¡A¶·¦³¤@­Ó½w½Ä®É¬q¡A³o®É¦pªG°¨¤W¥h§ì¨úDDE¸ê®Æ¡A·|¦³«¬ºA¤£²Åªº¿ù»~°T®§²£¥Í¡A¨Ã¤¤Â_°õ¦æ§Çªº§@·~¡C
  20.         Application.OnTime (Now + TimeValue("00:00:05")), "ThisWorkbook.ExeSelf"
  21.     End If
  22. End Sub

  23. Sub ExeSelf()
  24.     timerEnabled = True

  25.     If IsError(Sheets("¤u§@ªí1").Range("E5").Value) Then ' °²³] E5 Äæ¦ì¬°¦hªÅ¤O¹D
  26.         Cv = 0
  27.     Else
  28.         Cv = Sheets("¤u§@ªí1").Range("E5").Value          ' ¦hªÅ¤O¹D¦¨¥æ»ù
  29.     End If
  30.    
  31.     If (turnKey = 0 Or Ov = 0) Then                     ' ·í turnKey ­È¬°0¡Aªí¥Ü¨ä¬°ªì©l­È³]©w¡C¬°¦Ò¶q¨t²Î­è³s¤W DDE¡A
  32.                                                         ' ¦³®É¦b²Ä¤@®Ú¼Æ­È²£¥Í®É Ov = Cv ­È¥i¯à¬° 0 (IsError()ªºª¬ºA)¡C
  33.         Ov = Cv                                         ' ¦hªÅ¤O¹D¶}½L»ùªì©l­È³]©w
  34.         Hv = Cv                                         ' ¦hªÅ¤O¹D³Ì°ª»ùªì©l­È³]©w
  35.         Lv = Cv                                         ' ¦hªÅ¤O¹D³Ì§C»ùªì©l­È³]©w
  36.     End If
  37.    
  38.     turnKey = turnKey + 1
  39.    
  40.     If (Cv > Hv) Then Hv = Cv                           ' §PÂ_¦hªÅ¤O¹D³Ì°ª»ù
  41.     If (Cv < Lv) Then Lv = Cv                           ' §PÂ_¦hªÅ¤O¹D³Ì§C»ù

  42.     counter = counter + 1
  43.     [F1].Value = counter    ' 08:45 «á¨C¹j 30 ¬í¡A½ÐÆ[¹î F1 Äæ¦ì¼Æ¦r¬O§_·|²§°Ê¡H
  44.    
  45.     If (turnKey < nums) Then
  46.         Application.OnTime (Now + TimeValue("00:00:01")), "ThisWorkbook.ExeSelf"
  47.     Else
  48.         If (Cv > 0) Then Call Timer
  49.         Call timerStart
  50.     End If
  51. End Sub

  52. Sub Timer()
  53.     Dim str As String
  54.    
  55.     If (TimeValue(Now) >= TimeValue("08:45:00") And TimeValue(Now) <= TimeValue("13:46:01")) Then    ' ¶}½L¡B¦¬½L®É¬q³]©w
  56.         ' ½L¤¤³B²z¡A±N¸ê®Æ¶×¤J¼g¤J¤u§@ªí³æ¤ºÀx¦s¡C

  57.     End If
  58. End Sub
½Æ»s¥N½X
½Ð¯d·N¦¹¨â¤äµ{¦¡ªº¼gªk¤W¤§®t²§¡A§Ú³£ºÉ¶q¤@¤@¥[¥H»¡©ú¬°¦ó¦p¦¹¡A¬Û«H¹ï§A·|¦³§U¯q¡C
§Ú­Ì¦b¤H¥Í®Èµ{¤W³£¬O±q¤£ª¾¦ÓÀòª¾¤¤¦¨ªøªº¡A¥u­n¦³¤ß³£¥i¥H¦¨¥\ªº¡C

TOP

ÁÂÁÂc_c_lai ¤j¥Î¤ßªº¦^´_§Úªþ¤WÀɮפF ¦Û°Ê°O¿ý¨C¤ÀÄÁ©e¶R½æ§¡­È-½d¨Ò.rar (11.08 KB)

TOP

¦^´_  xu123
ªþ¤W¹Ï¤ù¥H¤Îµ{¦¡½X¨Ñ§A¹ê°È°Ñ¦Ò¡G
¤@¡B DDE ¨C¤ÀÄÁ°O¿ý§ï¬°30¬í¦Û°Ê°O¿ý¤@¦¸­n«ç§ï [­×§ï] ...
c_c_lai µoªí©ó 2012-5-26 09:44

·PÁÂc_c_lai ¤j³o»ò¸Ô²Ó¥Î¤ßªº¸Ñ»¡
¤S¶i¤@¨B¾Ç²ß¤F
¨Ó´ú¸Õ¬ã¨s¤@¤U

·P®¦¦A·P®¦

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD