vba - excel日期格式与vba中的不一样

Question

我发现excel中日期格式有问题，显示格式与vba中的numberformat不一样我已经为此案例创建了一个测试。

短日期的区域设置:MM/dd/yyyy

模块:

Function RetrieveNumberFormat() As String
    Dim rng As Range
    Set rng = Application.Caller    
    RetrieveNumberFormat = rng.NumberFormat       
    Set rng = Nothing
End Function

Function SetDate() As String
    Dim rng As Range    
    Set rng = Application.Caller    
    SetDate = Format(Now, rng.NumberFormat)    
    Set rng = Nothing
End Function

在 Excel 中

Col A (Date w default format) | Col B (MM/dd/yyyy)      | Col C (dd-mmm-yyyy)     | Col D (dd/mm/yyyy)      | Col E ([$-404]mm/dd/yyyy)
=Now()                        | =Now()                  | =Now()                  | =Now()                  | =Now()
=RetrieveNumberFormat()       | =RetrieveNumberFormat() | =RetrieveNumberFormat() | =RetrieveNumberFormat() | =RetrieveNumberFormat()
=SetDate()                    | =SetDate()              | =SetDate()              | =SetDate()              | =SetDate()

默认格式的日期(A 列):

结果:

请问为什么 Excel 将系统日期格式 MM/dd/yyyy 更改为 m/d/yyyy，有解决方法吗？

Answer 1

尝试改用Application.Text。

所以你的 SetDate 函数看起来像

Function SetDate() As String
    Dim rng As Range    
    Set rng = Application.Caller    
    SetDate = Application.Text(Now, rng.NumberFormat)    
    Set rng = Nothing
End Function

我对 Format 函数的体验是它并不完整。