创建将分钟转换为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屋！