VBA中的CDate功能问题-ProblemwithCDatefunctioninVBA

1  

The calling cell is exposed via Application.ThisCell however your going to have to manually format before/after the call as an Excel UDF cannot modify the physical characteristic of a cell.

调用单元通过Application.ThisCell公开，但是您必须在调用之前/之后手动格式化为Excel UDF，无法修改单元格的物理特性。

1  

Perhaps you can run something after the cells have been entered?

也许你可以在输入细胞后运行一些东西？

Dim c As range
For Each c In ActiveSheet.UsedRange.Cells
    ''Case sensitive
    If c.Formula = "=test()" Then
        c.NumberFormat = "d/mm/yyy"
    End If
Next

0  

It sounds like what you want to do is actually a two-part process: convert a value in a cell (or range of cells) to a date and then display it as a date, as opposed to simply converting a text value that looks like a date to a date value.

听起来你想要做的事实上是一个由两部分组成的过程：将单元格（或单元格范围）中的值转换为日期，然后将其显示为日期，而不是简单地转换看起来像的文本值日期值的日期。

If that is the case, then I would recommend modifying Remou's suggestion like so (using UsedRange can be problematic on worksheets containing large amounts of data):

如果是这种情况，那么我建议像这样修改Remou的建议（使用UsedRange在包含大量数据的工作表上可能会有问题）：

Dim c As Range
For Each c In Selection.Cells
    c.Value = CDate(c.Value)
    c.NumberFormat = "m/d/yyyy"
Next c

The user would then need to select the cells to which the formatting should be applied and run the macro; it sounds as though you do not wish this to happen, but I'm not sure that is possible.

然后，用户需要选择应该应用格式的单元格并运行宏;听起来好像你不希望这种情况发生，但我不确定这是否可能。

Depending on how you want to use the macro, you can add additional checks: for example, you could apply the formatting only to non-blank cells currently formatted as text (c.Value <> "" and c.NumberFormat = "@").

根据您希望如何使用宏，您可以添加其他检查：例如，您可以仅将格式应用于当前格式为文本的非空白单元格（c.Value <>“”和c.NumberFormat =“@” ）。

0  

If you return the data as a string and in a format that Excel automatically converts to a date, you can get the cell to format properly.

如果以字符串形式返回数据，并且Excel将自动转换为日期格式，则可以使单元格正确格式化。

Function Test() As String
  Test = Format(DateValue("2010/12/23"), "mm/dd/yyyy")
End Function

This works in US Excel, but if you need it to be language agnostic, I think you'll have problems.

这适用于美国Excel，但如果您需要它与语言无关，我认为您会遇到问题。