创建将分钟转换为 hh:mm:ss 的 VBA 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14985481/
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
Creating VBA function that converts minutes to hh:mm:ss
提问by engineerchange
I am trying to take a minute value (such as 3.83 minutes) and convert it to the hh:mm:ss time format (which I know is 0:03:50)
我正在尝试获取分钟值（例如 3.83 分钟）并将其转换为 hh:mm:ss 时间格式（我知道是 0:03:50）
For some reason, the .NumberFormat as recorded from a macro isn't working and giving me a #VALUE! error.
出于某种原因，从宏记录的 .NumberFormat 不起作用并给我一个 #VALUE！错误。
Function MINtoHMS(MIN)
MIN = MIN / (24 * 60)
MINtoHMS = MIN
MINtoHMS.NumberFormat = "[h]:mm:ss;@"
End Function
回答by Ripster
-Edit- To use as an add-in
-编辑-用作加载项
Excel Add-In: http://www.filedropper.com/mintohms
Excel 插件：http: //www.filedropper.com/mintohms
Create a class module named SheetChangeHandler with the following code:
使用以下代码创建名为 SheetChangeHandler 的类模块：
Option Explicit
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Err
If InStr(Target.Formula, "=MINtoHMS") Then
Target.NumberFormat = "hh:mm:ss"
End If
On Error GoTo 0
Exit Sub
Err:
End Sub
Add a module with the following code:
添加具有以下代码的模块：
Option Explicit
Public MySheetHandler As SheetChangeHandler
Sub Auto_Open()
Set MySheetHandler = New SheetChangeHandler
End Sub
Function MINtoHMS(MIN)
MIN = MIN / (24 * 60)
MINtoHMS = MIN
End Function
Click File > Save As > Excel 97-2003 Add-In (*.xla) > Save
单击文件 > 另存为 > Excel 97-2003 加载项 (*.xla) > 保存
Click File > Options > Add-Ins
单击文件 > 选项 > 加载项
Click "Go..." next to Manage: Excel Add-ins
单击“管理：Excel 加载项”旁边的“执行...”
Check the box next to the add-in you just created
选中您刚刚创建的加载项旁边的框
Click "OK"
点击“确定”
回答by RBarryYoung
First, You cannot change the format of an Excel Cell through its Formula. A Cell's formula can onlyassign to the value of a cell (or range).
首先，您不能通过其公式更改 Excel 单元格的格式。单元格的公式只能分配给单元格（或范围）的值。
Secondly, You really ought to declare some data-types in your functions, which will prevent a host of mysterious errors and other strange results.
其次，你真的应该在你的函数中声明一些数据类型，这将防止大量神秘错误和其他奇怪的结果。
Something like this should be fine:
这样的事情应该没问题：
Function MINtoHMS(MIN As Double) As Date
MIN = MIN / (24 * 60)
MINtoHMS = MIN
End Function
The only way to absolutely control what is seen through a function, would be to return a formatted string instead, as Ripster shows in his/her answer.
绝对控制通过函数看到的内容的唯一方法是返回格式化的字符串，正如 Ripster 在他/她的回答中所示。
回答by Krishna
Try these following methods.
试试以下这些方法。
DAYS = Format(Int([Expr3]/86400), "00") - will correctly display days
HOURS = Format(Int(([Expr3])/3600) - ((Int([Expr3]*86400)/3600), "00") - DOES NOT CORRECTLY display correct hours
HOURS = Format(Int([Expr3]/3600),"00") - What will display hours
MINUTES = Int(([Expr3]-(Int([Expr3]/3600)*3600))/60)
SECONDS = Format((([Expr3] Mod 60)),"00")
dTotalSeconds = DateDiff("S", Now(), dChristmasDate)
iDays = Int(dTotalSeconds / 86400)
iHours = Int((dTotalSeconds Mod 86400) / 3600)
iMinutes = Int(((dTotalSeconds Mod 86400) Mod 3600) / 60)
iSeconds = ((dTotalSeconds Mod 86400) Mod 3600) Mod 60
' By Using this function you can convert minutes to hh:mm:ss
' 通过使用此函数，您可以将分钟转换为 hh:mm:ss
Public Function HMStoSec(strHMS As String) As Long
HMStoSec = Split(strHMS, ":")(0) * 3600 + _
Split(strHMS, ":")(1) * 60 + _
Split(strHMS, ":")(2)
End Function
