vba 强制字符串变量为英国日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16301636/
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
Force string variable to UK date format
提问by bawpie
I've a piece of VBA code that is available via www.contextures.com. It enables users to add multiple entries to a data validation list seperated by a comma. The user selects from a list of dates, and a list of text entries.
我有一段 VBA 代码，可通过 www.contextures.com 获得。它使用户能够将多个条目添加到以逗号分隔的数据验证列表中。用户从日期列表和文本条目列表中进行选择。
The user can free type a date, or use the pick list to select a date or text entry. Sometimes two dates may need to go into a cell, so the VBA below allows the user to select/type one date, hit enter, then type another date in the same cell. As soon as the user hits enter, the previous date appears in the cell, with the new date following separated by a comma.
用户可以自由键入日期，或使用选择列表来选择日期或文本条目。有时两个日期可能需要进入一个单元格，因此下面的 VBA 允许用户选择/键入一个日期，按 Enter，然后在同一单元格中键入另一个日期。用户一按回车键，单元格中就会出现上一个日期，新日期之后用逗号分隔。
This is the original piece of VBA.
这是 VBA 的原始部分。
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
The problem is, when I enter a date in UK format into the cells the VBA applies to (such as 01/06/2013 i.e. dd/mm/yyyy), the VBA is intervening and returning the date as 06/01/2013 (mm/dd/yyyy).
问题是，当我在 VBA 适用的单元格中输入英国格式的日期时（例如 01/06/2013，即 dd/mm/yyyy），VBA 会进行干预并将日期返回为 06/01/2013（毫米/日/年）。
My control panel settings are all UK format, so I figure it's something to do with how the VBA stores and then returns the date. I tried making amendments to the dims oldval and newval (to dates) but then the VBA doesn't allow multiple entries in one cell as before.
我的控制面板设置都是英国格式，所以我认为这与 VBA 存储然后返回日期的方式有关。我尝试对 dims oldval 和 newval （日期）进行修改，但随后 VBA 不允许像以前一样在一个单元格中输入多个条目。
I amended the VBA to a hacky workaround (below) which formats the dates to the non standard dd.mm.yyyy (dd/mm/yyyy doesn't work) but this would require me to tell users to input dates in this format which I'd rather not do (I'd rather allow the standard 01/01/2013 or 01-01-2013 both of which are recognised as dates by Excel):
我将 VBA 修改为一个 hacky 解决方法（如下），它将日期格式化为非标准的 dd.mm.yyyy（dd/mm/yyyy 不起作用），但这需要我告诉用户以这种格式输入日期我宁愿不这样做（我宁愿允许标准 01/01/2013 或 01-01-2013 两者都被 Excel 识别为日期）：
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Format(Target.Value, "dd.mm.yyyy") 'amendment
Application.Undo
oldVal = Format(Target.Value, "dd.mm.yyyy") 'amendment
Target.Value = newVal
If Target.Column > 1 Then 'amendment
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
I'm using Excel 2007, but the spreadsheet needs to be compatible with 2003.
我使用的是 Excel 2007，但电子表格需要与 2003 兼容。
Edit: The way the data looks is in this question here: Excel 2003 - How to count dates in row, including multiple dates in cell
编辑：数据的显示方式在此问题中：Excel 2003 - 如何计算行中的日期，包括单元格中的多个日期
Edit: I should also mention that if I type 01/07/2013 into a cell twice, it comes out like this:
编辑：我还应该提到，如果我在一个单元格中输入 01/07/2013 两次，结果是这样的：
07/01/2013, 01/07/2013
07/01/2013, 01/07/2013
It's like the first time the cell is populated it switches it to US date format, but anything after is ok.
就像第一次填充单元格时，它会将其切换为美国日期格式，但之后的任何内容都可以。
采纳答案by bawpie
Okay, so after reading this article here: http://allenbrowne.com/ser-36.htmlI made some tweaks to the code:
好的，所以在阅读本文后：http: //allenbrowne.com/ser-36.html我对代码进行了一些调整：
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
'newVal = Target.Value 'amendment
newVal = Format(Target.Value, "\ dd\/mm\/yyyy\")
Application.Undo
'oldVal = Target.Value 'amendment
oldVal = Format(Target.Value, "\ dd\/mm\/yyyy\")
Target.Value = newVal
If Target.Column > 1 Then 'amendment
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler: Application.EnableEvents = True End Sub
exitHandler: Application.EnableEvents = True End Sub
The important bit appears to be the format:
重要的一点似乎是格式：
oldVal = Format(Target.Value, "\ dd\/mm\/yyyy\")
Allen's suggested code was:
艾伦建议的代码是：
Format$(varDate, "\#mm\/dd\/yyyy\#")
I changed it to dd/mm and replaced the # with a space, and hey presto - it appears to be working! A very frustrating issue though, which I'll now have to make sure works on other users machines! Thanks to @sous2817 and @GSerg for their comments/suggestions.
我将其更改为 dd/mm 并将 # 替换为一个空格，嘿，很快 - 它似乎正在工作！不过，这是一个非常令人沮丧的问题，我现在必须确保在其他用户的机器上也能正常工作！感谢@sous2817 和@GSerg 的评论/建议。
回答by sous2817
This seems to work for me...unless I'm miss understanding your issue....
这似乎对我有用......除非我想念你的问题......
newVal = Format(Sheet1.Range("A1"), "dd/mm/yyyy;@")
Code in its entirety:
完整代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Format(Target.Value, "dd/mm/yyyy;@") 'amendment
Application.Undo
oldVal = Format(Target.Value, "dd/mm/yyyy;@") 'amendment
Target.Value = newVal
If Target.Column > 1 Then 'amendment
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
回答by Vinod
Just this small change worked perfectly for me.
只是这个小小的改变对我来说非常有效。
newVal = Format(Sheet1.Range("A1"), "mm-dd-yyyy;@")
newVal = Format(Sheet1.Range("A1"), " mm-dd-yyyy;@")
My VBA was picking date as US format (mm-dd-yyyy). I wanted UK format (dd-mm-yyyy)
我的 VBA 选择日期为美国格式 (mm-dd-yyyy)。我想要英国格式 (dd-mm-yyyy)
I just used the above code to read the value as US format itself, so VBA picks the date and month correctly. Ultimately, because my system settings are UK based, end result is UK format.
我只是使用上面的代码将值读取为美国格式本身，因此 VBA 正确选择了日期和月份。最终，因为我的系统设置是基于英国的，最终结果是英国格式。
