FORECAST工作表函数失效：无法获取WorksheetFunction类的Forecast属性

原本3列数据时VBA循环预测代码正常运行，扩展到6列后出现异常。尝试改用单循环+Select Case遍历列的写法仍未解决。已排查以下常见诱因：

• newdate参数为Double类型
• FORECAST函数的known_x与known_y范围尺寸一致
  此前代码无修改，当前报错信息为无法获取WorksheetFunction类的Forecast属性。

相关代码：

Sub forecast_daily()

'refresh values (specifically Today and matching index)
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic


'set sheets

Set wb = ThisWorkbook.Sheets("Daily Data")
Set fc = ThisWorkbook.Sheets("Intake Forecasting Daily")

' define values

Dim dates, newdates, EUSSIntake, JFMintake, lateintake, newjfmintake, convertintake, BNOintake, Akinintake, Chenintake, ibtexintake, zambintake, velajintake As Range
Dim i, j As Long
Dim newdate As Double
Dim Rownum, a As Integer
Dim negcheck As Double
Dim toggleB, toggleC, toggleD, toggleE, toggleF, toggleG As Boolean



'Find empty row in EUSSIntake
i = fc.Range("V3").Value
'i equals empty row number

newdate = Cells(i, 1)

Set dates = Range("A22", "A" & (i - 1))
Set EUSSIntake = Range("B22", "B" & (i - 1))

'change starting point for JFM and BNO to account for blanks
Set newdates = Range("A1065", "A" & (i - 1))
Set JFMintake = Range("C1065", "C" & (i - 1))
Set BNOintake = Range("D1065", "D" & (i - 1))

'change start point for EUSS split (922)
Set dates3 = Range("A922", "A" & (i - 1))
Set lateintake = Range("E922", "E" & (i - 1))
Set newjfmintake = Range("F922", "F" & (i - 1))
Set convertintake = Range("G922", "G" & (i - 1))


' Loop Worksheet forecast function
toggleB = True
toggleC = True
toggleD = True
toggleE = True
toggleF = True
toggleG = True



' This is main loop - This originally worked! Frustrating now that it is isnt.     
While i < 3001
    If Cells(i - 1, 2) > -1000 And toggleB = True Then
    Cells(i, 2).Value = Application.WorksheetFunction.Forecast(newdate, EUSSIntake, dates)
    Else
    toggleB = False
    End If
    If Cells(i - 1, 3) > -1000 And toggleC = True Then
    Cells(i, 3).Value = Application.WorksheetFunction.Forecast(newdate, JFMintake, newdates)
    Else
    toggleC = False
    End If
    If Cells(i - 1, 4) > -1000 And toggleD = True Then
    Cells(i, 4).Value = Application.WorksheetFunction.Forecast(newdate, BNOintake, newdates)
    Else
    toggleD = False
    End If
    If Cells(i - 1, 5) > -1000 And toggleE = True Then
    Cells(i, 5).Value = Application.WorksheetFunction.Forecast(newdate, lateintake, dates3)
    Else
    toggleE = False
    End If
    If Cells(i - 1, 6) > -1000 And toggleF = True Then
    Cells(i, 6).Value = Application.WorksheetFunction.Forecast(newdate, newjfmintake, dates3)
    Else
    toggleF = False
    End If
    If Cells(i - 1, 7) > -1000 And toggleG = True Then
    Cells(i, 7).Value = Application.WorksheetFunction.Forecast(newdate, convertintake, dates3)
    Else
    toggleG = False
    End If

' increment i
    i = i + 1

' Update new values
   newdate = Cells(i, 1)

Wend


End Sub

1. 修复未指定工作表的Range/Cells歧义 #

代码中所有Range、Cells调用未指定所属工作表，默认指向当前激活工作表，极易导致范围指向错误（比如激活的是"Daily Data"而非目标预测表），进而触发FORECAST参数无效。

修改方案：所有Range/Cells前添加fc.前缀（预测操作在"Intake Forecasting Daily"工作表）：

newdate = fc.Cells(i, 1)
Set dates = fc.Range("A22", "A" & (i - 1))
Set EUSSIntake = fc.Range("B22", "B" & (i - 1))
' 后续所有Cells/Range调用均需添加fc.前缀

2. 排查FORECAST参数的隐藏合法性问题 #

即使范围尺寸一致，以下情况仍会触发报错：

• known_x范围存在重复值（线性回归要求x值唯一，重复会导致分母为0）
• known_y/known_x包含非数值（文本、空值等）
• newdate超出known_x的数值范围（部分Excel版本外推会触发异常）

排查代码示例：

' 检查范围是否含非数值
Sub CheckRangeValues(rng As Range)
    For Each cell In rng
        If Not IsNumeric(cell.Value) Then
            MsgBox "非数值单元格：" & cell.Address
            Exit Sub
        End If
    Next
End Sub

' 检查known_x是否有重复
Sub CheckDuplicateX(rng As Range)
    Dim xCol As New Collection
    On Error Resume Next
    For Each cell In rng
        xCol.Add cell.Value, Key:=CStr(cell.Value)
        If Err.Number <> 0 Then
            MsgBox "重复X值：" & cell.Value & "，位置：" & cell.Address
            Exit Sub
        End If
    Next
    On Error GoTo 0
End Sub

3. 改用Application.Forecast替代WorksheetFunction.Forecast #

WorksheetFunction.Forecast参数无效时直接抛出运行时错误，而Application.Forecast会返回错误值（如#N/A），便于捕获异常不中断代码：

Dim forecastVal As Variant
forecastVal = Application.Forecast(newdate, EUSSIntake, dates)
If Not IsError(forecastVal) Then
    fc.Cells(i, 2).Value = forecastVal
Else
    toggleB = False
    Debug.Print "EUSS列预测失败：" & newdate ' 日志记录错误
End If

4. 修正变量声明不规范问题 #

代码中变量声明存在隐式变体类型（如Dim dates, newdates... As Range仅最后一个变量为Range类型，其余均为Variant），可能导致意外类型错误：

' 修正后
Dim dates As Range, newdates As Range, EUSSIntake As Range, JFMintake As Range, lateintake As Range, newjfmintake As Range, convertintake As Range, BNOintake As Range, Akinintake As Range, Chenintake As Range, ibtexintake As Range, zambintake As Range, velajintake As Range
Dim i As Long, j As Long
Dim Rownum As Integer, a As Integer
Dim toggleB As Boolean, toggleC As Boolean, toggleD As Boolean, toggleE As Boolean, toggleF As Boolean, toggleG As Boolean

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