Excel VBA中消息框中的日期格式-Java 学习之路

我找到了一种基于条件显示消息框的方法，但是，出现的日期格式不是我想要显示的内容或工作表中显示的内容 . Cells(i, 4).Value 是我想要显示为"mmmm dd, yyyy"的日期值，但它显示为"m/d/yyyy" . 我的实际Excel数据的日期为"mmmm dd, yyyy" .

我已经尝试了多种格式化日期的方法，但我不断收到错误消息 . 是否可以在消息框中更改日期格式？

Sub Alert()
    Dim i As Long
    i = 3

    While Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 5) <> ""

    With Workbooks("Issuers.xlsm")

        If .Sheets("Summary of Covered Companies").Cells(i, 5).Value = 1 Then

             MsgBox Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 3).Value & " is issuing their next financial statement tomorrow (" & _
            Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 4).Value & ")."

        End If

    End With

   i = i + 1

   Wend

End Sub

2 回答

以下应该解决它

MsgBox Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 3).Value & " is issuing their next financial statement tomorrow (" & _
        Format(Workbooks("Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 4).Value,"[$-409]mmmm d, yyyy;@") & ")."

PS：使用宏录制器获取大多数excel命令，例如数字格式

回复于 2024-04-27T08:52:06+08:00

我认为你可以使用.value的 .text property intead得到你想要的东西

Try this example:

Sub Alert() 

    Dim i       As  Long 
    Dim wb      As Workbook
    Dim ws      As Worksheet

    Dim strDisplayDate  As String
    Dim strCompany      As String

    i = 3

    Set wb = Workbooks("Issuers.xlsm")
    Set ws = wb.Sheets("Summary of Covered Companies")

    With ws
        While .Cells(i, 5) <> ""

            If .Cells(i, 5).Value = 1 Then

                strCompany = .Cells(i, 3).Value

                ' Use text not value to store what's displayed on screen            
                strDisplayDate = .Cells(i, 4).Text 

                strMessage = strCompany & " is issuing their next financial statement tomorrow (" & strDisplayDate & ")."
                MsgBox strMessage

            End If

            i = i + 1

        Wend
    End With

End Sub

如果您确实想自己格式化值，可以使用.Value2来获取基础值，如 Format(.Cells(i,4,Value2),"mmmm dd, yyyy")

回复于 2024-04-27T08:52:06+08:00

Excel VBA中消息框中的日期格式

2 回答

相关问题