插入Excel单元格时的VBA日期值会更改其格式 [英] VBA Date values when inserted to Excel cells change their format

问题描述

我的日期变量格式如下:25_December_2010

I have date variables formatted like: 25_December_2010

我一旦使用

Dim strDate As String
strDate = "25_December_2010"
strDate = Replace(strDate,"_"," ")
MsgBox strDate

肯定会弹出一个MsgBox并给我:2010年12月25日.

Surely enough a MsgBox pops up and gives me: 25 December 2010.

但是，一旦我尝试将值放入单元格，例如:

However once i try to put the value into a cell for example:

Sheets("Sheet1").Range("A1").Value = strdate

而不是用25 December 2010填充cell; Excel自行执行操作，并使用令人讨厌的条目配置填充单元格:25-Dec-2010！

Instead of populating the cell with: 25 December 2010; Excel acts on it's own accord and populates the cell with the vexing entry configuration: 25-Dec-2010!

如何在我的单元格之间插入没有hyphen字符并且不修剪月份名称?

How can I have my cell populated with no hyphen characters inbetween and not having the month name trimmed?

推荐答案

此代码以您想要的格式将日期放入A1:

This code puts the date into A1 in the format you write that you want:

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

我不确定是否有必要，但为清楚起见，由于strDate是字符串数据类型，因此我可能会使用

I'm not sure if it is necessary, but for clarity, since strDate is a string data type, I would probably use

.Value = CDate(strDate)

在将其写入工作表之前，将其明确转换为Date数据类型在非英语版本中可能具有价值，但我没有对此进行专门检查.

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.

这篇关于插入Excel单元格时的VBA日期值会更改其格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！