请求编写VBA脚本：将选定区域单元格公式转为数值计算函数

需求说明 #

编写VBA脚本，对Excel选定区域内的每个单元格，将其原有公式转换为可计算该公式数值结果的形式；同时需处理公式包含其他单元格引用的情况。

尝试的代码 #

Sub Functions()
End Sub

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function
    
Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

Sub FixFormat(gyaat As Ranges)
    For Each c In Range(gyaat).Cells
        c.Formula = Eval(GetFormula(c.Adress))
    Next
End Sub

Sub bruh()
    
    If TypeName(Selection) = "Range" Then
        Dim myRange As Range
        Set myRange = Selection
        Dim myCells As Object
        
    Else
        MsgBox "Please select a Range", vbInformation
    End If
    
    For Each myCells In myRange
        myCells.Formula = Eval(GetFormula(myCells.Adress))
    Next
    
End Sub

现存问题 #

• 自定义函数仅能修改自身所在单元格，无法实现批量修改选定单元格的需求
• 代码存在语法错误：参数类型Ranges应为Range，Adress拼写错误（正确为Address），myCells声明为Object不合适

正确实现方案 #

方案1：直接将公式替换为计算结果（常用场景） #

如果只需把单元格公式替换为计算后的固定数值，无需保留动态计算逻辑，使用以下代码：

Sub ReplaceFormulasWithValues()
    ' 校验是否选中单元格区域
    If TypeName(Selection) <> "Range" Then
        MsgBox "请先选择一个单元格区域", vbExclamation
        Exit Sub
    End If
    
    Dim cell As Range
    ' 遍历选定区域的每个单元格
    For Each cell In Selection
        ' 仅处理包含公式的单元格
        If cell.HasFormula Then
            cell.Value = cell.Value ' 将单元格值设置为公式计算结果
        End If
    Next cell
End Sub

方案2：将公式转换为动态计算的自定义函数（保留逻辑） #

若需要保留原公式的动态计算逻辑（当引用单元格值变化时自动更新结果），可通过宏给单元格插入自定义函数调用：

' 自定义函数：计算传入的公式字符串结果
Function CalcFormula(formulaStr As String) As Variant
    Application.Volatile ' 确保引用单元格变化时重新计算
    On Error Resume Next ' 处理公式错误情况
    CalcFormula = Evaluate(formulaStr)
    On Error GoTo 0
End Function

Sub ReplaceFormulasWithCalcFunction()
    If TypeName(Selection) <> "Range" Then
        MsgBox "请先选择一个单元格区域", vbExclamation
        Exit Sub
    End If
    
    Dim cell As Range
    For Each cell In Selection
        If cell.HasFormula Then
            ' 转义原公式中的双引号，避免公式语法错误
            Dim originalFormula As String
            originalFormula = Replace(cell.Formula, """", """""")
            ' 将原公式替换为调用自定义函数的公式
            cell.Formula = "=CalcFormula(""" & originalFormula & """)"
        End If
    Next cell
End Sub

方案说明 #

• 方案1性能高效，适合不需要后续动态更新的场景
• 方案2保留原公式逻辑，支持动态更新，但计算效率略低于原生公式
• 两种方案均会跳过无公式的单元格，避免无效操作

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