VBA函数模块无法计算全部输出值问题求助

你遇到的这个问题，核心原因是VBA的编译机制导致运行时修改模块代码后无法实时生效。当你第一次修改MyNewTest模块并调用SortedArray函数时，Excel会立即编译这个函数的版本；之后循环里再修改模块代码，Excel依然会复用之前编译好的旧版本，所以所有行的结果都对应第一个KWATT的值。

而且运行时动态修改模块代码本身就很危险，不仅容易导致编译冲突（你提到的Excel崩溃就是这个原因），还会让代码难以维护。下面是最稳妥的解决方案：

最优方案：用参数传递替代动态代码生成 #

把需要动态计算的KWATT值作为参数直接传递给SortedArray函数，完全不需要在循环里修改模块代码。

步骤1：固定MyNewTest模块的代码 #

把原来动态生成的函数改成接收参数的版本，代码固定不变：

Option Explicit
Public Function SortedArray(ItemsCounter As Long, KWATT As Double) As Variant()
    Dim TempSortedArray() As Variant
    
    ' 直接用传入的KWATT计算，不需要硬编码
    Sheet4.Cells(ItemsCounter, 2) = KWATT + 5
    
    ' 如果函数需要返回数组，补充你的数组逻辑（示例）
    ' ReDim TempSortedArray(1 To 1)
    ' TempSortedArray(1) = KWATT + 5
    ' SortedArray = TempSortedArray
End Function

步骤2：修改主循环代码 #

删除所有修改模块代码的逻辑，直接在循环里调用带参数的函数：

Option Explicit
Public Sub AddNewWorkBookTEST()
    Dim LastUsedRowList As Long
    Dim x As Long
    Dim KWATT As Double
    Dim folderPath As String
    
    folderPath = Application.ActiveWorkbook.Path
    LastUsedRowList = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 1 To LastUsedRowList
        KWATT = Sheet4.Cells(x, 1)
        ' 直接传递参数调用函数，无需修改模块
        Call MyNewTest.SortedArray(x, KWATT)
    Next x
End Sub

为什么这个方案可行？ #

• 彻底避免了运行时修改代码带来的编译冲突，不会再出现Excel崩溃的情况。
• 逻辑清晰，参数传递明确，后续维护起来非常方便。
• 性能更稳定，不需要频繁操作VBA模块。

特殊场景：必须动态生成代码的处理（不推荐） #

如果你的实际需求确实需要动态生成复杂代码（比如无法通过参数传递的逻辑），可以尝试修改代码后强制重新编译，但这种方法兼容性差且风险高：

在修改完MyNewTest模块代码后，添加以下代码强制编译：

' 强制重新编译所有模块
Application.VBE.MainWindow.Visible = False ' 隐藏VBE窗口避免干扰
Application.VBE.CommandBars.FindControl(ID:=578).Execute ' 执行编译命令

注意：这个方法依赖Excel的VBE命令栏，不同版本可能失效，而且依然存在崩溃风险，仅作为极端场景的备选方案。

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