VBA强制字符串变量为uk日期格式 [英] VBA force string variable to uk date format
问题描述
编辑：经过阅读后，我遇到了一个我以下发布的答案。
好的，所以我一直在看VBA可通过www.contextures.com获取。相关代码使用户可以将多个条目添加到由逗号分隔的数据验证列表中。列表允许用户从日期列表中选择一个小列表的文本条目。它的工作方式是用户可以自由键入日期，或使用选择列表来选择日期或文本输入。有时候两个日期可能需要进入一个单元格，所以下面的VBA允许用户选择/输入一个日期，按回车键，然后在同一单元格中键入另一个日期。一旦他们按下Enter键，上一个日期就会出现在单元格中，新的日期会以逗号分隔。
这是VBA的原始片段。
Option Explicit
'由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
如果Target.Count> 1然后GoTo exitHandler
在错误恢复下一步
设置rngDV = Cells.SpecialCells（xlCellTypeAllValidation）
错误GoTo exitHandler
如果rngDV不是GoTo exitHandler
如果Intersect（Target，rngDV）不是，然后
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
如果Target.Column = 3然后
如果oldVal =然后
'do nothing
Else
如果newVal =然后
'不做任何
Else
Target.Value = oldVal _
& ，& newVal
End If
End If
End If
End If
exitHandler：
Application.EnableEvents = True
End Sub
现在，我遇到的问题是VBA有效，但是，因为我是英国基于，每当我将英国日期输入到VBA适用的单元格中（例如01/06/2013即dd / mm / yyyy），VBA将插入并返回日期为06/01/2013（mm / dd / YYYY）。我的控制面板设置都设置为uk格式，所以我认为这与VBA如何存储然后返回日期有关。我已经尝试修改oldval和newval（的日期），但是VBA不允许我像以前一样在一个单元格中有多个条目（我猜vba不喜欢连接日期？）。
我已将VBA修改为一个黑客（下面），将日期格式设置为非标准dd.mm.yyyy（dd / mm / yyyy doesn'但是这需要我告诉用户输入这种格式的日期，我宁愿不做（我宁愿允许更标准的01/01/2013或者01-01-2013两者都被认定为date by excel）：
Option Explicit
'由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
如果Target.Count> 1然后GoTo exitHandler
在错误恢复下一步
设置rngDV = Cells.SpecialCells（xlCellTypeAllValidation）
错误GoTo exitHandler
如果rngDV不是GoTo exitHandler
如果Intersect（Target，rngDV）不是，然后
'do nothing
Else
Application.EnableEvents = False
newVal = Format（Target .Value，dd.mm.yyyy）'修正
Application.Undo
oldVal =格式（Target.Value，dd.mm.yyyy）'修正
Target.Value = newVal
如果Target.Column> 1然后'修改
如果oldVal =然后
'不做任何
Else
如果newVal =然后
'不做任何
Else
Target.Value = oldVal _
& ，& newVal
End If
End If
End If
End If
exitHandler：
Application.EnableEvents = True
End Sub
无论如何，任何建议/指针都将不胜感激！我正在使用Excel 2007，但是电子表格需要与2003兼容。
编辑：数据在这个问题中的方式在这里： Excel 2003 - 如何计算行中的日期，包括单元格中的多个日期
编辑：我还应该提到，如果我将01/07/2013输入一个单元格两次，它出现如下： / p>
07/01/2013，01/07/2013
这是非常奇怪的。这就像第一次单元格填充它将其切换到美国日期格式，但任何事情都可以。我将尝试在同事的机器上和我的家用电脑上测试这个结果。
好的，在这里阅读这篇文章后： http://allenbrowne.com/ser-36.html
我对代码进行了一些调整：
Option Explicit
'由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
如果Target.Count> 1然后GoTo exitHandler
在错误恢复下一步
设置rngDV = Cells.SpecialCells（xlCellTypeAllValidation）
错误GoTo exitHandler
如果rngDV不是GoTo exitHandler
如果相交（目标，rngDV）不是，然后
'不做任何
Else
Application.EnableEvents = False
'newVal = Target。值'修正
newVal =格式（Target.Value，\ dd\ / mm\ / yyyy\）
Application.Undo
'oldVal = Target .Value'revision
oldVal = Format（Target.Value，\ dd\ / mm\ / yyyy\）
Target.Value = newVal
如果Target.Column> ; 1然后'修改
如果oldVal =然后
'不做任何
Else
如果newVal =然后
'不做任何
Else
Target.Value = oldVal _
& ，& newVal
End If
End If
End If
End If
exitHandler：
Application.EnableEvents = True
End Sub
重要的位似乎是格式：
oldVal =格式（Target.Value，\ dd\ / mm\ / yyyy\）
>格式$（varDate，\＃mm\ / dd\ / yyyy\＃）
我把它改成了dd / mm，用空格代替了＃，而且它似乎是有效的！一个非常令人沮丧的问题，我现在必须确保在其他用户的机器上工作！感谢@ sous2817和@GSerg的意见/建议。
Edit: After some reading, I've come across an answer which I've posted below.
Okay, so I've been looking at a piece of VBA that is available via www.contextures.com. The piece of code in question enables users to add multiple entries to a data validation list seperated by a comma. The list allows the user to select from a list of dates, and also a small list of text entries. The way it works is 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 they hit enter, the previous date appears in the cell, with the new date following seperated by a comma. So far so good.
This is the original piece of 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
Now, the problem I'm having is that the VBA works, however, as I'm UK based, whenever I enter a UK date 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). My control panel settings are all set to uk format, so I figure it's something to do with how the VBA stores and then returns the date. I've tried making amendments to the dims oldval and newval (to dates) but then the VBA doesn't allow me to have multiple entries in one cell as before (I guess vba doesn't like concatenating dates?).
I've amended the VBA to a hacky work around (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 more standard 01/01/2013 or 01-01-2013 both of which are recognised as dates by 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
Anyway, any advice/pointers would be much appreciated! I'm using Excel 2007, but the spreadsheet needs to be compatible with 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
Edit: I should also mention that if I type 01/07/2013 into a cell twice, it comes out like this:
07/01/2013, 01/07/2013
Which is very odd. It's like the first time the cell is populated it switches it to US date format, but anything after is ok. I am going to try and test this on a colleague's machine, and on my home PC to see what the results are.
解决方案 Okay, so after reading this article here: http://allenbrowne.com/ser-36.html
I made some tweaks to the code:
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
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.
这篇关于VBA强制字符串变量为uk日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
