VBA中Application.WorksheetFunction.WorkDay函数调用报错求助

Hey there, let's sort out why that WorkDay function is throwing errors for you. I've dealt with this exact headache when working with user form text boxes and date functions in VBA, so here's the breakdown of what's going wrong and how to fix it:

问题根源 #

Your code is passing formatted text strings directly to the WorkDay function, but WorkDay requires a genuine Date data type to work correctly. Even though your xDateValidation checks confirm the input is a valid date, the text box still stores it as a string—and VBA might misinterpret that string (especially if your system's date format doesn't match dd/mm/yyyy).

On top of that, when you use Format(xTran.Text, "dd/mm/yyyy"), you're converting the value back to a string right after validation, which makes it even harder for WorkDay to recognize it as a date value.

修复方案 #

The fix is simple: first convert the text box input to a proper Date variable, then use that variable in the WorkDay calculation. Here's your adjusted code:

Dim inputDate As Date
Dim settlementDate As Date

If xDateValidation(xTran.Text) = True Then
    ' Convert the validated text to a real Date type
    inputDate = CDate(xTran.Text)
    
    ' Format the display for the transaction date text box (still a string for UI)
    xTran.Text = Format(inputDate, "dd/mm/yyyy")
    
    ' Calculate the settlement date using the Date variable
    settlementDate = Application.WorksheetFunction.WorkDay(inputDate, 2)
    
    ' Format and assign to the settlement text box
    xSettlement.Text = Format(settlementDate, "dd/mm/yyyy")
    Debug.Print Format(settlementDate, "dd/mm/yyyy")
Else
    xTran.Text = ""
End If

额外的区域设置兼容方案 #

If you're worried about system date settings messing up CDate() (e.g., your system uses mm/dd/yyyy but users input dd/mm/yyyy), you can manually split the text and build the date with DateSerial:

Dim dateParts() As String
dateParts = Split(xTran.Text, "/")
inputDate = DateSerial(dateParts(2), dateParts(1), dateParts(0))

This ensures the date is parsed exactly as dd/mm/yyyy, regardless of system settings.

另一个检查项 #

If you're using an older version of Excel, make sure the Analysis ToolPak is enabled:

• Go to Excel Options > Add-Ins
• Under Manage, select "Excel Add-ins" and click Go
• Check the box for "Analysis ToolPak" and click OK
  Modern Excel versions usually include WorkDay without this add-in, but it's worth verifying if you're still seeing errors.

补充说明 #

Both Application.WorksheetFunction.WorkDay and Application.WorkDay are valid— the difference is that the former throws a runtime error if something goes wrong (like an invalid date), while the latter returns an error value you can check with IsError() if you want more error handling flexibility.

内容的提问来源于stack exchange，提问作者Fox Feng