Excel VBA将时间特定值存储到userform中的单元格中

I have a userform that requires the user to input a specific date and time through two separate comboboxes, cboStartDate, cboStartTime. The user will also have to input the duration in a text field, txtDuration.

我有一个userform，要求用户通过两个单独的组合框cboStartDate，cboStartTime输入特定的日期和时间。用户还必须在文本字段txtDuration中输入持续时间。

Upon saving, the start date and time will be stored in a formatted cell [DD/MM/YYYY HH:MM AM/PM]. The end date and time will be calculated from the duration field and stored in another cell with the same formatting. Something like this:

保存后，开始日期和时间将存储在格式化的单元格[DD / MM / YYYY HH：MM AM / PM]中。结束日期和时间将从持续时间字段计算，并存储在具有相同格式的另一个单元格中。像这样的东西：

+-----------------------+-----------------------+
| startTime             | endTime               |
+-----------------------+-----------------------+
| 2/4/2012  11:30:00 AM | 2/4/2012  2:00:00 PM  |
+-----------------------+-----------------------+

However, after running the userform through, the start time is not stored, and the end time is not calculated. Something like this:

但是，在运行userform后，不会存储开始时间，也不会计算结束时间。像这样的东西：

+-----------------------+-----------------------+
| startTime             | endTime               |
+-----------------------+-----------------------+
| 2/4/2012  12:00:00 AM | 2/4/2012  12:00:00 AM |
+-----------------------+-----------------------+

Below is my part of my VBA code:

以下是我的VBA代码部分：

Dim iRow As Long
Dim ws As Worksheet
Dim startDate As Date
Dim unFmtStartDuration() As String
Dim startDuration As Double
Dim minTest As Integer
Dim endDate As Date
Dim endDuration As Double

Set ws = Worksheets("EVENTS")

'Search for the last row in the worksheet
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'Date manipulation and set start and end timings
unFmtStartDuration() = Split(cboStartTime.Text, ":")
startDuration = unFmtStartDuration(0)
If unFmtStartDuration(1) = "00" Then
    minTest = 0
Else
    minTest = unFmtStartDuration(1)
    If minTest = 30 Then
        startDuration = startDuration + 0.5
    End If
End If
startDate = DateValue(DateAdd("h", startDuration, cboDate.Text & " 12:00AM"))
ws.Cells(iRow, 4).Value = startDate
endDuration = txtDuration.Value
endDate = DateValue(DateAdd("h", endDuration, startDate))
ws.Cells(iRow, 5).Value = endDate

So how can I get this part sorted out? Would appreciate any help here. Thanks.

那么我该如何解决这个问题呢？非常感谢这里的任何帮助。谢谢。

P.S. Would like to post screenshots here, but my reputation here is too low for it. Sorry.

附：想在这里发布截图，但我在这里的声誉太低了。抱歉。

1 个解决方案

#1

It looks like you are only adding the time when minTest = 30, but this value probably varies quite a bit. Also, in one instance, you are comparing a string, and another a number when referencing unFmtStartDuration, which may work, but is confusing when reading your code.

看起来你只是在minTest = 30时添加时间，但是这个值可能会有很大差异。此外，在一个实例中，您正在比较一个字符串，另一个数字在引用unFmtStartDuration时可能有效，但在阅读您的代码时却很困惑。

To follow your current method, use

要按照您当前的方法，请使用

startDuration = Val(unFmtStartDuration(0) + Round(Val(unFmtStartDuration(1)) / 60, 2)

to replace this

替换这个

startDuration = unFmtStartDuration(0)
If unFmtStartDuration(1) = "00" Then
    minTest = 0
Else
    minTest = unFmtStartDuration(1)
    If minTest = 30 Then
        startDuration = startDuration + 0.5
    End If
End If

This will take whatever the time is and convert it to the decimal form you are using, instead of relying on the 30 match. (Unless you need that specifically. If so, say so, as I think this can still be arranged with rounding tricks.)

这将花费任何时间并将其转换为您正在使用的小数形式，而不是依赖于30匹配。（除非你特别需要它。如果是这样的话，请这样说，因为我认为这仍然可以安排四舍五入。）

However, I think a better option would be to use

但是，我认为更好的选择是使用

startDuration = TimeValue(cboStartTime.Text) * 24

So no other math or checks are involved.

所以不涉及其他数学或检查。

Also, unless cboStartTime.Text (and subsequently startDuration) is greater than 24 hours, this

另外，除非cboStartTime.Text（以及随后的startDuration）大于24小时，否则

startDate = DateValue(DateAdd("h", startDuration, cboDate.Text & " 12:00AM"))

will always return the date specified in cboDate.Text with an implied 12:00:00 AM. To correct this, you will want to change to

将始终返回cboDate.Text中指定的日期，隐含在12:00:00 AM。要更正此问题，您需要更改为

startDate = DateAdd("h", startDuration, cboDate.Text & " 12:00AM")

I think there is some more to fix, but hopefully this gets you going in the right direction...

我认为还有一些问题需要解决，但希望这会让你朝着正确的方向前进......

#1