excel - 将公式添加到 Excel VBA 表单保存按钮

Question

我正在构建一个表单并对保存按钮进行编码，我希望某些单元格运行一个公式来自动计算日期。

我正在尝试输入:.Cells(lRow, 20).Formula = "=PEDate.Value-PSDate.Value"

我只想从 PSDate 中减去 PEDate 框中的值，以计算这两个日期之间的天数。

我写的代码如下:

Private Sub SBut_Click()

Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Projects")
lRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

With ws
    .Cells(lRow, 1).Value = SCBox.Value
    .Cells(lRow, 2).Value = SearchBox.Value
    .Cells(lRow, 3).Value = BHNumber.Value
    .Cells(lRow, 4).Value = SDate.Value
    .Cells(lRow, 5).Value = PSDate.Value
    .Cells(lRow, 6).Value = PEDate.Value
    .Cells(lRow, 7).Value = EDate.Value
    .Cells(lRow, 10).Value = COS.Value
    .Cells(lRow, 11).Value = Interviews.Value
    .Cells(lRow, 13).Value = Placement.Value
    .Cells(lRow, 15).Value = FTDate.Value
    .Cells(lRow, 17).Value = Rework.Value
    .Cells(lRow, 19).Value = PlacementConf.Value
    .Cells(lRow, 20).Formula = "=PEDate.Value-PSDate.Value"
End With
   

SCBox.Value = ""
SearchBox.Value = ""
BHNumber.Value = ""
SDate.Value = ""
PSDate.Value = ""
PEDate.Value = ""
EDate.Value = ""
COS.Value = ""
Interviews.Value = ""
Placement.Value = ""
FTDate.Value = ""
Rework.Value = ""
PlacementConf.Value = ""

Unload Me

有人对做什么有建议吗？

提前致谢

Answer 1

如果您希望它成为一个公式(根据值的变化重新计算)，您需要引用您写入这些值的单元格，而不是文本框的值:

.Cells(lRow, 20).Formula = "=" & .Cells(lRow, 6).Address & "-" & .Cells(lRow, 5).Address

如果您只想计算一次值并将它们写为常量值，您可以通过以下方式实现:

.Cells(lRow, 20).Value = PEDate.Value - PSDate.Value

---

问题是如果您在文本框中输入日期，它们不是日期而是字符串。因此，您需要将它们拆分为日、月和年，并使用 DateSerial() 构建一个真正的数字日期(以便您可以使用该日期进行计算)。

.Cells(lRow, 20).Value = ConvertDDMMYYYYToDate(PEDate.Value) - ConvertDDMMYYYYToDate(PSDate.Value)

Public Function ConvertDDMMYYYYToDate(ByVal DateString As String) As Date
    Dim DateSplit() As String
    DateSplit = Split(DateString, "/")
    
    Dim RetVal As Date
    If UBound(DateSplit) = 2 Then
        ' build a real numeric date
        RetVal = DateSerial(DateSplit(2), DateSplit(1), DateSplit(0))
        
        ' test if the string was a real date
        If Format$(RetVal, "DD\/MM\/YYYY") = DateString Then
            ConvertDDMMYYYYToDate = RetVal
        End If
    End If
End Function