VBA Excel定时器（秒表）时间在保存，关闭并重新打开文件后重置

问题描述：

这只是我的第二篇文章，我希望我说得对。VBA Excel定时器（秒表）时间在保存，关闭并重新打开文件后重置

我有一个工作簿与几个定时器（停止手表）哪些工作正常。他们停止开始并用“活动的x”按钮清除，没有问题。问题是当我关闭工作簿并重新打开时。我点击开始按钮，时间重置为00：00：00：00，然后...这是我用来追踪实际花费一个月以上的时间，并确实需要它关闭后不重置为零重新开放。以下是我的代码..我自学成功并在线上“借阅”了大部分代码。在此先感谢

Public StopIt As Boolean 
Public ResetIt As Boolean 
Public LastTime 

Public StopIt2 As Boolean 
Public ResetIt2 As Boolean 
Public LastTime2 


Private Sub CommandButton1_Click() 
Dim StartTime, FinishTime, TotalTime, PauseTime 

Me.CommandButton1.Enabled = False 
StopIt = False 
ResetIt = False 
If Range("D5") = 0 Then 
    StartTime = ("D5") = Timer 
    PauseTime = 0 
    LastTime = 0 
Else 
    StartTime = 0 
    PauseTime = Timer 
End If 
StartIt: 
    DoEvents 
    If StopIt = True Then 
    LastTime = TotalTime 
    Exit Sub 
    Else 
    FinishTime = Timer 
    TotalTime = FinishTime - StartTime + LastTime - PauseTime 
    TTime = TotalTime * 100 
    HM = TTime Mod 100 
    TTime = TTime \ 100 
    hh = TTime \ 3600 
    TTime = TTime Mod 3600 
    MM = TTime \ 60 
    SS = TTime Mod 60 
    Range("D5").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00") 
    If ResetIt = True Then 
     Range("D5") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00") 
     LastTime = 0 
     PauseTime = 0 

     End 
    End If 
    GoTo StartIt 

    End If 
End Sub 

Private Sub CommandButton4_Click() 
Dim StartTime2, FinishTime2, TotalTime2, PauseTime2 

    Me.CommandButton4.Enabled = False 
StopIt2 = False 
ResetIt2 = False 
If Range("D8") = 0 Then 
    StartTime2 = Timer 
    PauseTime2 = 0 
    LastTime2 = 0 
Else 
    StartTime2 = 0 
    PauseTime2 = Timer 
End If 
StartIt2: 
    DoEvents 
    If StopIt2 = True Then 
    LastTime2 = TotalTime2 
    Exit Sub 
    Else 
    FinishTime2 = Timer 
    TotalTime2 = FinishTime2 - StartTime2 + LastTime2 - PauseTime2 
    TTime = TotalTime2 * 100 
    HM = TTime Mod 100 
    TTime = TTime \ 100 
    hh = TTime \ 3600 
    TTime = TTime Mod 3600 
    MM = TTime \ 60 
    SS = TTime Mod 60 
    Range("D8").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00") 
    If ResetIt2 = True Then 
     Range("D8") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00") 
     LastTime2 = 0 
     PauseTime2 = 0 
     End 
    End If 
    GoTo StartIt2 
    End If 
End Sub 

Private Sub CommandButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 
    Me.CommandButton1.Enabled = True 
    StopIt = True 
End Sub 
Private Sub CommandButton5_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 
    Me.CommandButton4.Enabled = True 
    StopIt2 = True 
End Sub 

Private Sub CommandButton3_Click() 
    Dim Msg As String, Ans As Variant 

    Msg = "Are you sure you want to reset ALL timers .. Did you send your monthly report?" 

    Ans = MsgBox(Msg, vbYesNo) 

    Select Case Ans 

    Case vbYes 
    Range("D5").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00") 
    LastTime = 0 
    Range("D8").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00") 
    LastTime2 = 0 
    ResetIt = True 

    Case vbNo 
GoTo Quit: 
    End Select 

Quit: 
End Sub

您将需要保存t他目前在工作簿中的某处，例如工作簿关闭时作为NAME（Google它），然后在开始备份时从名称中重新载入“计时器”。 – hoodaticus

你会希望将值保存为INI文件（这只是一个奇特的文本文件） - 但说实话，你真的应该使用数据库这样的东西。你有没有Microsoft Access？ – braX

我有访问，但不幸的是零经验使用它。 –

答

存储持久性数据

在表属性的第一部分存储数据，所以你可以单独跟踪每个工作表的，如果你想

第二部分使用自定义属性将数据存储在工作簿属性中工作簿定制属性也在“属性”下的“文件选项卡”中设置

Sub customProperties() 

    ActiveWorkbook.Sheets("Sheet1").customProperties.Add "abc123", 123 
    Debug.Print ActiveWorkbook.Sheets("Sheet1").customProperties(1)  ' custom properties are not indexed by name 

    ActiveWorkbook.Sheets("Sheet1").customProperties(1).Value = "this is my data" 
    Debug.Print ActiveWorkbook.Sheets("Sheet1").customProperties(1) 

    ActiveWorkbook.Sheets("Sheet1").customProperties(1).Delete 


    ' CustomDocumentProperties Types 
    ' msoPropertyTypeBoolean 2 
    ' msoPropertyTypeDate  3 
    ' msoPropertyTypeFloat 5 
    ' msoPropertyTypeNumber 1 
    ' msoPropertyTypeString 4 


    ActiveWorkbook.CustomDocumentProperties.Add Name:="xyz", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="xyz" 
    Debug.Print ActiveWorkbook.CustomDocumentProperties("xyz") 

    ActiveWorkbook.CustomDocumentProperties("xyz").Value = "abcdefg" 
    Debug.Print ActiveWorkbook.CustomDocumentProperties("xyz") 

    ActiveWorkbook.CustomDocumentProperties("xyz").Delete 




End Sub

VBA Excel定时器（秒表）时间在保存，关闭并重新打开文件后重置

相关推荐