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

[µo°Ý] §Ú¤]·Q°ÝDDE§Q¥ÎPrivate Sub Worksheet_Calculate()¨ú¸ê®Æ¤è¦¡

[µo°Ý] §Ú¤]·Q°ÝDDE§Q¥ÎPrivate Sub Worksheet_Calculate()¨ú¸ê®Æ¤è¦¡

¦U¦ì¤j¤j§A­Ì¦n
§Úª¾¹D§Ú«Ü¹à
¦ý¬Oªñ¤é­è¦n¦³»Ý¨D·Q­n½Ð±Ð¦U¦ì¤j¤j

¥ý¦bSheet1(Sheet1)¤W¥´¤W
¨ä¥L¤j¤jªº¯«©_µ{¦¡(¦p¤U)

Private Sub Worksheet_Calculate()
Dim NowDateTime, nowTime, startTime, stopTime
Dim tr As String

NowDateTime = Now  '²{¦bªº®É¶¡­È, ¥h±¼¾ã¼Æ³¡¥÷,
nowTime = (NowDateTime - Int(NowDateTime)) '±o¨ì²{¦bªº®É¶¡­È
startTime = Range("A6") '¶}½L®É¶¡, ¨Ò¦p: "09:00:00 AM"
stopTime = Range("A8")  '¦¬½L®É¶¡, ¨Ò¦p: "01:30:00 PM"

If nowTime <= startTime Then  '©|¥¼¶}½L
    Exit Sub
ElseIf nowTime > stopTime Then '¤w¸g¦¬½L
    Exit Sub
Else
    If [C2] <> "-" And [C2] <> "###" Then   '²M½Lªºª¬ºA, ¤£¨ú¨ä¸ê®Æ
        tr = Int((nowTime - startTime) * 288) + 2  '¨C®t 300 ¬í´N´«¤@¦C
        If Range("D" & tr) = "" Then Range("D" & tr) = Range("C2")  '¶}©l»ù
        If Range("E" & tr) = "" Or Range("C2") > Range("E" & tr) _
            Then Range("E" & tr) = Range("C2")                      '³Ì°ª»ù
        If Range("F" & tr) = "" Or Range("C2") < Range("F" & tr) _
            Then Range("F" & tr) = Range("C2")                      '³Ì§C»ù
        Range("G" & tr) = Range("C2")                               'µ²§ô»ù
    End If
End If
End Sub

¦ý³Ì«áÁÙ¬OµLªk¦s¨ú
¤£ª¾¹D­þÃä¿ù»~¤F  ÁÙ¬O­n¥´¦bThisWorkbook¨ºÃä©O???
§Ú¬O¨Ï¥Î´I¨¹e01 DDE¦s¨ú¸ê®Æ©ñ¦bC2

¦A½Ð¦U¦ì¤j¤j¤F §Ú¦³ª¦¤å¦ý¬OÁÙ¬O¤£·| ·PÁÂ

©Ò¿×ªº "µLªk¦s¨ú" ¤£ª¾¹D¬O¤°»ò±¡§Î?
1. ½L¤¤¸Ó¸õ°Êªº¼Æ¦r¤£·|¸õ°Ê?
2. ÁÙ¬O¥X²{¿ù»~°T®§,¨Ò¦p: "µLªk¦s¨ú»·ºÝ¸ê®Æ" ©Î¬O "¬Y¬Yµ{¦¡µLªk¶}±Ò?"
3. ¥ç©Î¬O µ²ªGÀx¦s®æ¤º®eµLªk¸òµÛ¨Ó·½Àx¦s®æ¤º®eÅÜ°Ê?

¦]°T®§¤£¨¬©Ò¥H¥u¯à¥ý¥Î²qªº :
1. ¥ýÀˬd¤@¤U¬O§_¤§«e¦³½Õ¾ã¹L³]©wÅܦ¨¬O "¤â°Ê­pºâ" ©Î¬O "Àx¦s«e¦A­pºâ" ¤§Ãþªº³]©w.

2. ¸Ó¬Ý½Lµ{¦¡À³¤ñExcelÀÉ¥ý°õ¦æ,ÁÙÀ³½T»{¤wµn¤J¨Ã¥¿±`³s½u¥H±µ¦¬¦æ±¡¸ê®Æ¤¤,
¤S©ÎªÌ¬OÀx¦s®æ¤ºªº¤½¦¡¬O§_¤£¥¿½T-
¥i¥H¦Ò¼{¨Ì¬Ý½L³nÅé©Ò´£¨Ñªº¥\¯à¬Ý¬Oª½±µÂন(©Î¶×¥X¨ì)ExcelÀÉ ©Î¬O ¥iª½±µ©ì¦²¨ìÀx¦s®æ¤W.
¦A¨Ì¨ä³W«ß±À¾É¥X¥¿½Tªº¤½¦¡. (¨Ò¦p¤£¦PªÑ²¼¥N¸¹ »P ¤£¦P»ù§O¦WºÙ(¶R¶i»ù, ¦¨¥æ»ù...)¶¡¤½¦¡ªº®t²§)
3. ¥ý½T»{¨Ó·½Àx¦s®æÅܰʱ¡§Î¥¿±`,¦A½T»{µ²ªGÀx¦s®æ¤½¦¡¬O§_¥¿±`.

TOP

·PÁ¤j¤jÀ°¦£¦^ÂÐ
1.¼Æ¦r¬O·|¸õ°Êªº
2.µL¿ù»~°T®§
3.§Ú³]©w¬°D E F G ¸òµÛC2ÅÜ¤Æ ¦ý¬OD E F G±q¨Ó¨S¦³¥X²{¹L¼Æ­È¹L??

Àˬd«á
1.¥¿±`
2.¦n¹³¬O³oÃä¥X°ÝÃD §Ú§ï¥Î¤¸¤jDDE«á´N¥X²{µ{¦¡¿ù»~ ¤£¹L¥¿¦bºN¼ô¿ù»~½X¤¤
  ¦ü¥G¬O´I¨¹e01¨Ï¥Î¬°®æ¦¡°ÝÃD
3.¥Ø«e´N¦bºÃ´b ®Ú¾Ú§Úªº¸ÑŪ ¬O§_ D E F G´NÀ³¸Ó¶}©l¦s¨ú¸ê®Æ«á´«¦æ«ùÄò¬ö¿ý
   ¦ý§¹¥þ¨S¦³°ÊÀR ·PÁ¤j¤j¤F

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-7-27 02:36 ½s¿è
Àˬd«á
2.¦n¹³¬O³oÃä¥X°ÝÃD §Ú§ï¥Î¤¸¤jDDE«á´N¥X²{µ{¦¡¿ù»~ ¤£¹L¥¿¦bºN¼ô¿ù»~½X¤¤
  ¦ü¥G¬O´I¨¹e01¨Ï¥Î¬°®æ¦¡°ÝÃD
3.¥Ø«e´N¦bºÃ´b ®Ú¾Ú§Úªº¸ÑŪ ¬O§_ D E F G´NÀ³¸Ó¶}©l¦s¨ú¸ê®Æ«á´«¦æ«ùÄò¬ö¿ý
   ¦ý§¹¥þ¨S¦³°ÊÀR
albertbug µoªí©ó 2011-7-26 21:54


2. ¤£¦Pªº¬Ý½L³nÅé·|¦]¬°¨ä µ{¦¡(©Î¸ê®Æ®w)¦WºÙ(¦ì©ó "|" ¥ª¤è) »P ¹ï»ù§O¦WºÙ©w¸q ªº¤£¦P¦Ó¦³¤£¦Pªº®M¥Î¤½¦¡,
¤@¯ë¨Ó»¡¤£­×§ï¤½¦¡¤º®e¬O¤£¯àª½±µ§ì¨ì¸ê®Æªº,
½Ð°Ñ¾\³o½g¤ºªº±Ô­z¨Ó­×§ï¤½¦¡.
3. ½Ð¤W¶Ç¸ÓÀÉ®×¥H«K¤j®a§PÂ_°ÝÃDÂI¦b­þ.

TOP

ªþ¥ó¬°´ú¸ÕÀÉ®×
A6¬°¶}©l®É¶¡
A8¬°µ²§ô®É¶¡
C2ªºData­ì¥»¬O·Q©ñ¤J´Á³f¸õ°Ê
¦ý¥Ñ©ó·Q´ú¸Õ¾ã¤Ñ©Ò¥H¬O©ñ¤W¤¸¤j¶×²v¸õ°Ê

¦A³Â·Ð¦U¦ì¤j¤j¤F
·PÁÂ

Ãö©ó²Ä¤GÂI­×§ï¤½¦¡³¡¤À¤w¦b¾Ç²ß·PÁ¦U¦ì

VBA2.rar (8.13 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-11-12 06:44 ½s¿è

¦^´_ 5# albertbug
[ 2011/11/12 §ó¥¿ ]
¸Õ¸Õ¬Ý
  1. Private Sub Worksheet_Calculate()
  2.     Dim startTime, stopTime
  3.     Dim Tr As String, Time_Step As Date
  4.    
  5.     Time_Step = #12:05:00 AM#     '³]©w¶¡¹j®É¶¡: 5¤ÀÄÁ->300¬í
  6.     'Time_Step = #12:00:01 AM#     '³]©w¶¡¹j®É¶¡: 1¬í
  7.    
  8.     startTime = Range("A6") '¶}½L®É¶¡, ¨Ò¦p: "09:00:00 AM"
  9.     stopTime = Range("A8")  '¦¬½L®É¶¡, ¨Ò¦p: "01:30:00 PM"
  10.     If startTime > Time Then  '©|¥¼¶}½L
  11.         Exit Sub
  12.     ElseIf stopTime < Time Then '¤w¸g¦¬½L
  13.         Exit Sub
  14.     Else     
  15.       If [C2] <> "-" And [C2] <> "###" Then   '²M½Lªºª¬ºA, ¤£¨ú¨ä¸ê®Æ
  16.             'Tr = Int((nowTime - startTime) * 1440) + 30  '¨C®t 300 ¬í´N´«¤@¦C
  17.             Tr = Int((Time - startTime) / Time_Step) + 30 ' ¨C5¤ÀÄÁ¤@¦C ±q²Ä30¦C¶}©l
  18.             If Range("D" & Tr) = "" Then Range("D" & Tr) = Range("C2")  '¶}©l»ù
  19.             If Range("E" & Tr) = "" Or Range("C2") > Range("E" & Tr) _
  20.                 Then Range("E" & Tr) = Range("C2")                      '³Ì°ª»ù
  21.             If Range("F" & Tr) = "" Or Range("C2") < Range("F" & Tr) _
  22.                 Then Range("F" & Tr) = Range("C2")                      '³Ì§C»ù
  23.             Range("G" & Tr) = Range("C2")                               'µ²§ô»ù
  24.         End If
  25.     End If
  26. End Sub
½Æ»s¥N½X

TOP

·PÁ¤j¤jªº¦^ÂÐ
®Ú¾ÚG¤j§Aªº¼gªk ¥Ø«e¦b D E F GÁÙ¬O¨S¦³¼Æ­È¼g¤J¨Ã¥BÀx¦s

§Ú¾Ç²Lªº¬ÝµÛCode
·N«ä¤W§Ú³£À´ ¦ý¬O§Úı±o¦³ÂI©_©Ç
¬°¦ó¨S¦³°õ¦æ
If Range("D" & Tr) = "" Then Range("D" & Tr) = Range("C2")  ³o¦æ
¤@¶}©l°²­Y¨S¦³¼Æ­È  ·|§PÂ_¿ù»~¦X²z

§Úªº°ÝÃD´N¬O ¼Æ¦r·|¸õ°Ê ¦ý¬O¼Æª½µLªkÀx¦s  ·PÁ¦U¦ì!

TOP

¦^´_ 7# albertbug
«Øij§A«e­±¥[¤W Range("C" & tr) = Format(Time, "HH:MM:SS") ¨Ó¬Ý¬Ý
¬Ý¬Ý§AªþÀɤW66¦C-786¦C¤W¦³¤£³sÄòªº¸ê®Æ,¸ê®Æ¬O¦³°O¿ý¤U¨Óªº.

TOP

¯uªº¦³°O¿ý¤U¨Ó  ¬O§Ú®É¶¡¤Wªº®t²§
¾É­Ptr¼Æ­È¤ñ¸û¤j
¤ÓÄø¤F§Ú
Sorry

¦nªº ·PÁ¤j¤j §Ú¨Ó¦b¦n¦n¬ã¨s¤@¤U³o¨Ç¸ê®Æ
·PÁ¦U¦ì

TOP

¥»©«³Ì«á¥Ñ albertbug ©ó 2011-7-27 18:48 ½s¿è

¦U¦ì¤j¤j §ÚÁÙ·Q°µ¥t¥~¤@¥ó¨Æ±¡
¨C¦æD E F G
¬Ò»Ý­n»P¤W¦æD E F G¤ñ¸û­È¤j¤p
(¦]¬°¨C¦æ³£·|®Ú¾Útr©¹¤U±Æ§Ç)
¤ñ¸û§¹«á ®Ú¾Ú§PÂ_¦¡¿é¥X¤å¦r"+" or "-"

¦p¦¹¤ñ¸ûªk­n¦p¦ó¼g¥X

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD