插入 Excel 单元格时的 VBA 日期值更改其格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23847720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Date values when inserted to Excel cells change their format
提问by Codo
I have date variables formatted like: 25_December_2010
我的日期变量格式如下： 25_December_2010
Once i use
一旦我使用
Dim strDate As String
strDate = "25_December_2010"
strDate = Replace(strDate,"_"," ")
MsgBox strDate
Surely enough a MsgBoxpops up and gives me: 25 December 2010.
果然，一个MsgBox弹出窗口给了我：2010 年 12 月 25 日。
However once i try to put the value into a cellfor example:
但是，一旦我尝试将值放入单元格中，例如：
Sheets("Sheet1").Range("A1").Value = strdate
Instead of populating the cellwith: 25 December 2010; Excel acts on it's own accord and populates the cell with the vexing entry configuration: 25-Dec-2010!
而不是填充cell：25 December 2010; Excel 自行操作并使用令人烦恼的条目配置填充单元格：25-Dec-2010！
How can I have my cell populated with no hyphencharacters inbetween and not having the month name trimmed?
如何让我的单元格填充hyphen中间没有字符并且没有修剪月份名称？
回答by Ron Rosenfeld
This code puts the date into A1 in the format you write that you want:
此代码将日期以您想要的格式写入 A1：
Option Explicit
Sub WriteDate()
Dim strDate As String
strDate = "25_December_2010"
strDate = Replace(strDate, "_", " ")
MsgBox strDate
With Sheets("Sheet1").Range("A1")
.Value = strDate
.NumberFormat = "dd mmmm yyyy"
End With
End Sub
I'm not sure if it is necessary, but for clarity, since strDate is a string data type, I would probably use
我不确定是否有必要，但为了清楚起见，由于 strDate 是字符串数据类型，我可能会使用
.Value = CDate(strDate)
Explicitly converting it to the Date data type, before writing it to the worksheet, might be of value in non-English versions, but I've not checked that specifically.
在将其写入工作表之前，将其显式转换为 Date 数据类型可能在非英语版本中有价值，但我没有专门检查过。
回答by Alex P
Use a custom date format:
使用自定义日期格式：
Sheets("Sheet1").Range("A1").NumberFormat = "dd mmmm yy" //A1 = 25 December 10
回答by user3654095
The Excel sheet is not wrong, so stop saying it is. A date is a count of the number of days since a start date. So a date is a NUMBER. You can format it how you want.
Excel 表没有错，所以不要再说了。日期是自开始日期以来的天数计数。所以日期是一个NUMBER。您可以根据需要对其进行格式化。
This is VBA, excel is similar though the starting dates are different.
这是VBA，虽然开始日期不同，但excel是相似的。
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59. Any recognizable literal date values can be assigned to Date variables. Date literals must be enclosed within number signs (#), for example, #January 1, 1993# or #1 Jan 93#.
日期变量存储为 IEEE 64 位（8 字节）浮点数，表示从 100 年 1 月 1 日到 9999 年 12 月 31 日的日期和从 0:00:00 到 23:59:59 的时间。任何可识别的文字日期值都可以分配给日期变量。日期文字必须用数字符号 (#) 括起来，例如，#January 1, 1993# 或 #1 Jan 93#。
Date variables display dates according to the short date format recognized by your computer. Times display according to the time format (either 12-hour or 24-hour) recognized by your computer.
日期变量根据计算机识别的短日期格式显示日期。时间根据计算机识别的时间格式（12 小时制或 24 小时制）显示。
When other numeric types are converted to Date, values to the left of the decimal represent date information while values to the right of the decimal represent time. Midnight is 0 and midday is 0.5. Negative whole numbers represent dates before 30 December 1899.
其他数值类型转换为Date时，小数点左边的值代表日期信息，小数点右边的值代表时间。午夜为 0，中午为 0.5。负整数表示 1899 年 12 月 30 日之前的日期。
Date 8 bytes January 1, 100 to December 31, 9999
日期 8 个字节 100 年 1 月 1 日至 9999 年 12 月 31 日
This is what recording it in Excel shows.
这就是在 Excel 中记录它所显示的内容。
Selection.NumberFormat = "d mm yyyy"
This works here for me
这对我有用
Sub DateF()
Dim strDate As String
strDate = "25_December_2010"
strDate = Replace(strDate, "_", " ")
MsgBox strDate
Worksheets("Sheet1").Range("A1").NumberFormat = "dd mm yy"
Worksheets("Sheet1").Range("A1").Value = strDate
End Sub
I also changed it from sheet. to worksheet.
我也从工作表中改变了它。到工作表。
