创建将分钟转换为hh:mm:ss的VBA函数 [英] Creating VBA function that converts minutes to hh:mm:ss
问题描述
我正在尝试获取分钟值(例如3.83分钟)并将其转换为hh:mm:ss时间格式(我知道是0:03:50)
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)
由于某种原因,从宏记录的.NumberFormat无法正常工作,并给了我#VALUE!错误.
For some reason, the .NumberFormat as recorded from a macro isn't working and giving me a #VALUE! error.
Function MINtoHMS(MIN)
MIN = MIN / (24 * 60)
MINtoHMS = MIN
MINtoHMS.NumberFormat = "[h]:mm:ss;@"
End Function
推荐答案
-编辑-用作加载项
Excel加载项: http://www.filedropper.com/mintohms
Excel Add-In: http://www.filedropper.com/mintohms
使用以下代码创建名为SheetChangeHandler的类模块:
Create a class module named SheetChangeHandler with the following code:
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
点击文件>另存为> Excel 97-2003加载项(* .xla)>保存
Click File > Save As > Excel 97-2003 Add-In (*.xla) > Save
点击文件>选项>加载项
Click File > Options > Add-Ins
点击管理:Excel加载项"旁边的转到..."
Click "Go..." next to Manage: Excel Add-ins
选中您刚刚创建的加载项旁边的框
Check the box next to the add-in you just created
点击确定"
这篇关于创建将分钟转换为hh:mm:ss的VBA函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!