Excel VBA 将数值强制转换为时间格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9005964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA to force numeric value into time format
提问by John M
There are two columns copied into a workbook from a Access database.
有两列从 Access 数据库复制到工作簿中。
I want to ensure that the data is formatted correctly so I added this code:
我想确保数据格式正确，所以我添加了以下代码：
'DateTime Column
Sheets("Sheet1").Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy hh:mm"
'Time Column
Sheets("Sheet1").Columns("B:B").Select
Selection.NumberFormat = "hh:mm"
The datetime column formats correctly.
The time column is initially copied as a numeric equivalent (ie 0.595277777777778) instead of the time value (14:17).
日期时间列格式正确。
时间列最初被复制为等效的数字（即 0.595277777777778）而不是时间值 (14:17)。
Running the macro code does nothing to the visual display and it isn't until I hit F2 and enter on the cell that the format applies.
运行宏代码对视觉显示没有任何影响，直到我按 F2 并在格式适用的单元格上输入。
Is there a method (short of a loop) to force Excel to apply the formatting?
是否有强制 Excel 应用格式的方法（缺少循环）？
采纳答案by Steve Homer
If you copy the column and paste as values (through a macro if needs be) then Excel should re-interpret the data type and the above should work correctly.
如果您复制列并粘贴为值（如果需要，通过宏），则 Excel 应该重新解释数据类型，并且上述内容应该可以正常工作。
回答by phoog
You can use VBA to accomplish the same effect as Steve Homer's answer by setting the range's value property to itself:
您可以使用 VBA 通过将范围的 value 属性设置为自身来实现与 Steve Homer 的答案相同的效果：
Dim r As Range
Set r = Sheets("Sheet1").Columns("B:B")
r.Value = r.Value
回答by elhex
You can do this with out declare TMP as DATE:
您可以在不将 TMP 声明为 DATE 的情况下执行此操作：
TMP = CDate(Range("A1").Value) ' we suppose A1 value is 12:45:00
TMP = "" & TMP ' convert to char
TMP = Left(TMP, 5) ' extract the first 5 char
MsgBox TMP ' for displaying 12:45
