excel - 函数正在产生不匹配错误。不知道为什么更改变量名解决了它

Question

调用 Day1WeekNum 和 Day1inMonth 的主要函数

Function NthDay(Date1)
' Tells you if a date is the 3rd Tuesday of the Month
Dim Day1Name, Day1WeekNum, A, B, DayName, Nth
Dim Status ' Tells you if there is anything in the rest of the Array
Dim cWeekNum ' Number for the current week
Dim WeekDiff 'Difference between the week numbers
Dim cDayNum   'Number for the day of the week for Date1
Dim Week1Num

Week1Num = Day1WeekNum(Date1) ' tells me the week number of the first day of the month
Day1Name = Day1inMonth(Date1) ' tell me the day of the week for the first day of the month

Day1inMonth 的代码

Function Day1inMonth(Date1)
'Tells you the weekday of the first day in a month of the provided date
Dim cYear, cMonth, month1st, day1


cYear = Year(Date1)
cMonth = Month(Date1)
month1st = DateSerial(cYear, cMonth, 1)
day1 = Weekday(month1st, vbSunday)

Day1inMonth = day1
End Function

Day1WeekNum 的代码

Function Day1WeekNum(Date1 As Date)
'Tells you the week of the first day of the month of the provided date
Dim cYear, cMonth, day1Week
Dim month1st As Date

cYear = Year(Date1)
cMonth = Month(Date1)
month1st = DateSerial(cYear, cMonth, 1)
day1Week = WorksheetFunction.WeekNum(month1st, 1)

Day1WeekNum = day1Week
End Function

为了阻止不匹配错误，我不得不将上面的代码更改为下面的代码。我不知道为什么。它是否必须与变量范围或其他内容有关？我正在努力理解，以便将来避免错误的原因。

允许它工作的新代码:

Function Day2WeekNum(Date1)
'Tells you the week of the first day of the month of the provided date

Dim cYear1, cMonth1, day1Week1
Dim month1st1 As Date

cYear1 = Year(Date1)
cMonth1 = Month(Date1)
month1st1 = DateSerial(cYear, cMonth, 1)
day1Week1 = WorksheetFunction.WeekNum(month1st, 1)

Day2WeekNum = day1Week1
End Function

Answer 1

您在范围内有一个局部变量和一个函数，它们具有相同的名称 - Day1WeekNum
局部变量隐含地是一个 Variant，因为它没有被声明为任何特定类型。变体可以包含数组以及单个(标量)值。

作业Week1Num = Day1WeekNum(Date1)看起来像一个函数调用，但实际上是在尝试访问一个数组。未定义数组，因此您会收到类型不匹配错误。如果你给 Week1Num变量是显式的非数组/非变量类型，则错误将更改为“编译错误:预期数组”。

更改函数名称有效，因为新函数名称与调用过程中的任何局部变量都不匹配。为避免将来出现此问题:

使用 Option Explicit在每个模块的顶部(通过打开
VBA 中工具 > 选项 > 编辑器中的“需要变量声明”
编辑)

给每个变量一个明确的类型 - 例如Dim Day1WeekNum As Integer

避免声明与范围内的子或函数同名的变量，因为这可能会造成混淆并导致问题