自定义函数

侧田12234556666

写了一个function,然后我在单元格里写了=work(202301,2022/2/21,""),结果显示VALUE，帮忙看下是哪里有问题。


代码如下：


Function work(a As Long, b As Date, c As Date)

Dim x As Range, y As Range
Dim d, e

x = Sheets(节假日).Range("B2:B51")
y = Sheets(节假日).Range("A2:A1000")


  If DatePart(Left(a, 4), Right(a, 2), 1) > b Then
  d = DatePart(Left(a, 4), Right(a, 2), 1)
  Else: d = b
  End If
  
  If DatePart(Left(a, 4), Right(a, 2) + 1, 1) - 1 < c Then
  e = DatePart(Left(a, 4), Right(a, 2) + 1, 1) - 1
  Else: e = c
  End If
  
  If c = "" Or c > DatePart(Left(a, 4), Right(a, 2), 1) Then
工作日 = NetworkDays.INTL(d, e, 1, x) + Application.WorksheetFunction.CountIfs(y, "<=" & DatePart(Left(a, 4), Right(a, 2) + 1, 1) - 1, y, ">=" & DatePart(Left(a, 4), Right(a, 2), 1))
Else: 工作日 = 0
End If


End Function

ykcbf1100

不上个附件吗？
没附件就不看了。

侧田12234556666

function内容更新如下：
Function work(ByVal a As Long, ByVal b As Date, ByVal c As Date)

Dim x As Range, y As Range
Dim d, e As Date
Dim f, g


Set x = Sheets("节假日").Range("B2:B51")
Set y = Sheets("节假日").Range("A2:A1000")

f = DateSerial(Left(a, 4), Right(a, 2), 1)
  If f > b Then
  d = f
  Else: d = b
  End If
  
g = DateSerial(Left(a, 4), Right(a, 2) + 1, 1) - 1
  If g < c Or Str(c) = "" Or c = 0 Then
  e = g - 1
  Else: e = c
  End If
  
  If Str(c) = "" Or c > f Or c = 0 Then
work = Application.WorksheetFunction.NetworkDays_Intl(d, e, 1, x) + Application.WorksheetFunction.CountIfs(y, "<=" & g - 1, y, ">=" & f)
Else: work = 0
End If


End Function


以上代码稍微改动了下格式在宏里面可以运行，但是模块里仍不行

半百

DatePart 似乎应该换成 DateSerial
=work(202301,"2022/2/21","")
别的不知道

侧田12234556666

好的，确实有几处错误。更新代码如下：
但是试了仍不行。

Function work(ByVal a As Long, ByVal b As Date, ByVal c As Date)

Dim x As Range, y As Range
Dim d, e As Date
Dim f, g


Set x = Sheets("节假日").Range("B2:B51")
Set y = Sheets("节假日").Range("A2:A1000")

f = DateSerial(Left(a, 4), Right(a, 2), 1)
  If f > b Then
  d = f
  Else: d = b
  End If
  
g = DateSerial(Left(a, 4), Right(a, 2) + 1, 1) - 1
  If g < c Or Str(c) = "" Or c = 0 Then
  e = g - 1
  Else: e = c
  End If
  
  If Str(c) = "" Or c > f Or c = 0 Then
work = Application.WorksheetFunction.NetworkDays_Intl(d, e, 1, x) + Application.WorksheetFunction.CountIfs(y, "<=" & g - 1, y, ">=" & f)
Else: work = 0
End If


End Function