通过仅使用excel VBA引用一个数字来自动填充日期 [英] Autofill date by referring only one number with excel VBA

问题描述

如果我们仅通过引用数字就可以填写日期怎么办?这种类型的功能将在某些excel情况下提高用户友好性.

What if we could fill a date by only referring a number ? This type of feature would improve user-friendliness in some excel scenarios.

例如:在3月工作表中，当我在给定的日期列中键入"7"时，我插入该值的单元格将返回"07/03/19"(或03/07/19).

For instance : In March sheet, when I type "7" in a given dates column, the cell in which I inserted the value would return "07/03/19" (or 03/07/19).

如果可能的话，这意味着我需要在VBA代码中指定此工作表的月份和年份，并为每张工作表(2月等)更改此变量.如果工作表名称是月份名称(例如"March")，则甚至可以采用一种解决方案的VBA代码来实现.以下公式利用了它的优势，因此我想VBA可以做到这一点.

If possible, this means I need to specify in the VBA code the month and year for this sheet, and change this variable for every single sheet (february etc.). If the sheet names are months names (Eg "March"), there could even be a way to do it with a one solution VBA code. The following formula takes advantages of it, so I guess VBA could do it to.

=MONTH(DATEVALUE(MID(CELL("filename";$A$1);FIND("]";CELL("filename";$A$1))+1;255)&" 1"))

在名为三月"的工作表中输入此公式，它将返回"3".

Enter this formula in a sheet named "March" and it will return "3".

我一直在寻找一种简单的方法来执行此操作，但是据我所知(直到您带上光辉:)).据我所知，数据验证功能无济于事.另外，插入数字的单元格本身(而不是其他单元格)自动填充也很重要.

I have been looking for a simple way to do this, but there is none to my knowledge (until you bring your light :)). Data validation feature won't help as far as I know. Also, it's important that the cell in which the number is inserted autofill itself (not another cell).

这至少有可能吗?我敢打赌.有人告诉我要研究事件函数，但是我对VBA知之甚少.

Is this at least possible ? I bet yes. I've been told to look at event function, but I know too little with VBA.

推荐答案

这可能需要修改以满足您的需要，但是也许可以使用 Worksheet_Change 事件来解决类似的问题.

This may need modified to fit your needs, but maybe a solution like this using the Worksheet_Change event.

工作表更改部分:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo SafeExit:
    Application.EnableEvents = False
        If Target.Cells.Count = 1 Then ' 
            If Not Intersect(Target, Me.Columns("A")) Is Nothing Then 'change as needed
                Target.Value = DateFromDay(Me.Name, Target.Value)
            End If
        End If
SafeExit:
    Application.EnableEvents = True
End Sub

主要功能

Public Function DateFromDay(monthName As String, dayNum As Integer) As Date
    On Error Resume Next
    DateFromDay = DateValue(dayNum & " " & monthName & " " & Year(Now()))
End Function

您可能会考虑 Workbook_SheetChange 事件，也可以将此功能添加到多个工作表中.

You might consider the Workbook_SheetChange event as well to add this functionality to multiple sheets.

这篇关于通过仅使用excel VBA引用一个数字来自动填充日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！