时间戳

首页 > 解决方案 > VBA中的总和标签值

excel - VBA中的总和标签值

问题描述

在此处输入图像描述

Basically, when a day is selected, I will enter each employee's time started and the time ended and their break time, and also enter their output for the day.

选择第二天后，我想对给定的日期进行精确计算，并在最后三列中制表。

Option Explicit

Dim hour1 As Date
Dim hour2 As Date
Dim hour3 As Date
Dim Totalhour As Date
Dim StartNumber As Integer
Dim EndNumber As Integer
Dim Sum As Double
Dim i As Integer
Dim x As Integer
Dim Total As Double

Private Sub cmdHoursWorked_Click()

If OpMon.Value = True Then

hour1 = txtTimeIn1.Text
hour2 = txtTimeOut1.Text
hour3 = cmbBreak1.Text
Total = txtUnitCom1.Text
Totalhour = ((hour2 - hour1) - hour3)
lblHrsWorked1.Caption = Round((Totalhour * 24), 2)
lblMon1.Caption = Round(((lblHrsWorked1.Caption * 3600) / txtUnitCom1.Text), 1)
'lblUnitWeek1.Caption = txtUnitCom1.Text'
'lblTHW1.Caption = lblHrsWorked1'

ElseIf OpWed.Value = True Then

hour1 = txtTimeIn1.Text
hour2 = txtTimeOut1.Text
hour3 = cmbBreak1.Text
Totalhour = ((hour2 - hour1) - hour3)
lblHrsWorked1.Caption = Round((Totalhour * 24), 2)
lblWed1.Caption = Round(((lblHrsWorked1.Caption * 3600) / txtUnitCom1.Text), 1)
'lblUnitWeek1.Caption = txtUnitCom1.Text'
'lblHrsWorked1.Caption = lblTHW1.Caption'
lblSecUnit1.Content = (lblMon1.Caption + lblTue1.Caption + lblWed1.Caption + lblThu1.Caption + lblFri1.Caption)

End If

End Sub

假设我选择星期一，我将输入 9:00 和 17:30 作为开始和结束时间，休息 30 分钟。我在完成的单元文本框中输入 500。然后我计算工作时间（标签），并计算标签星期一标签上每秒完成的单位。

现在，如果选择另一天，我想做完全相同的计算，并根据用户的输入添加到最后三列

ElseIf OpTue.Value = True Then
    start_time = CDate(txtTimeIn1.Text)
    end_time = CDate(txtTimeOut1.Text)
    break_time = CInt(cmbBreak1.Text)
    units = CSng(txtUnitCom1.Text)
    ttl_secUnit1 = CSng(lblSecUnit1.Caption)
    sec_Mon1 = CSng(lblMon1.Caption)
    'sec_Tue1 = CSng(lblTue1.Caption)

   ' Calculation
    hours_worked = (CSng(end_time) - CSng(start_time)) * 24! - break_time / 60!
    rate = units / (3600! * hours_worked)
    ttl_secUnit1 = (CSng(sec_Mon1) + CSng(sec_Tue1))
    lblSecUnit1.Caption = WorksheetFunction.Sum(lblMon1.Caption, lblTue1.Caption)


    ' Results
    lblHrsWorked1.Caption = Round((hours_worked), 2)
    lblTue1.Caption = Format(rate, "0.000")
    'lblSecUnit1.Caption = Format(ttl_secUnit1, "0.000")

End If

标签： excelvba

解决方案

您正在使用字符串进行数学运算，并希望 VBA 解释器能达到您的预期。不要那样做。相反，分三个不同的步骤处理数据：

  1. 从标签中获取输入并将它们转换为Integer,Single或Date使用CInt(),CSng()或类型的值CDate()。
  2. 用值做数学运算。
  3. 使用显示函数将值转换为字符串Format()。

在下面的简单示例中

输出

Public Sub CalculateRate()

    ' Input values
    Dim start_time As Date, end_time As Date
    Dim break_time As Integer, units As Single

    start_time = CDate(Label1.Caption)
    end_time = CDate(Label2.Caption)
    break_time = CInt(Label3.Caption)
    units = CSng(Label4.Caption)

    ' Calculation
    Dim hours_worked As Single, rate As Single
    hours_worked = (CSng(end_time) - CSng(start_time)) * 24! - break_time / 60!
    rate = units / (3600! * hours_worked)

    ' Results
    Label5.Caption = Format(rate, "0.000")

End Sub

请注意，编译器会解释以下文字

  • 1000作为Integer（16 位）
  • 100000作为Long（32 位）
  • 1000!作为Single浮点数（32 位）
  • 1000#作为Double浮点数（64 位）

推荐阅读