VBA宏将单元格函数转换为文本表达式非常慢。

huangapple go评论54阅读模式
英文:

VBA macro to convert cell Function into text expression is very slow

问题

我正在尝试在每个公式前面设置',以便不计算函数并以纯文本显示。

Sub ConvertFormulasToTextForSheet()
  Dim ws As Worksheet
  Dim rng As Range
  Dim cell As Range
  Dim sheetName As String
  
  sheetName = "Sheet1" 

  Set ws = ThisWorkbook.Worksheets(sheetName)

  Set rng = ws.UsedRange

  For Each cell In rng
      cell.Value = "'" & cell.Formula
  Next cell
End Sub

执行该函数非常慢,基本上每秒只能转换3-4个。有没有办法加快速度?

英文:

I am trying to set ' in front of every formula so that the function is not evaluated and is displayed as plain text.

Sub ConvertFormulasToTextForSheet()
  Dim ws As Worksheet
  Dim rng As Range
  Dim cell As Range
  Dim sheetName As String
 
  sheetName = "Sheet1" 

  Set ws = ThisWorkbook.Worksheets(sheetName)

  Set rng = ws.UsedRange

  For Each cell In rng
      cell.Value = "'" & cell.Formula

  Next cell

End Sub

executing that function is extremly slow, it basically convert 3-4 per seconds.
Is there any way how to speed that up?

答案1

得分: 3

使用数组:

Dim vals As Variant
vals = ws.UsedRange.Formula

If IsArray(vals) Then
    Dim i As Long, j As Long
    For i = LBound(vals, 1) To UBound(vals, 1)
        For j = LBound(vals, 2) To UBound(vals, 2)
            vals(i, j) = "'" & vals(i, j)
        Next
    Next
Else
    vals = "'" & vals
End If

ws.UsedRange.Value = vals
英文:

Use an array:

Dim vals As Variant
vals = ws.UsedRange.Formula

If IsArray(vals) Then
    Dim i As Long, j As Long
    For i = LBound(vals, 1) to UBound(vals, 1)
        For j = LBound(vals, 2) to UBound(vals, 2)
            vals(i, j) = "'" & vals(i, j)
        Next
    Next
Else
    vals = "'" & vals
End If

ws.UsedRange.Value = vals

答案2

得分: 1

数组比迭代每个公式要高效得多。设置 Application.Calculation = xlCalculationManual 也会提高代码性能。我添加了 Suspend 作为参数。这将使启用和禁用公式变得容易。

用法

SuspendFormula ActiveSheet, True
SuspendFormula ActiveSheet, False

代码

Sub SuspendFormula(Worksheet As Worksheet, Suspend As Boolean)
    Dim InitialCalculationState As XlCalculation
    Application.ScreenUpdating = False
    InitialCalculationState = Application.Calculation
    Application.Calculation = xlCalculationManual
        
    Dim Target As Range
    Set Target = Worksheet.UsedRange
    Dim Data As Variant
        
    If Target.Count = 1 Then
        ReDim Data(1 To 1, 1 To 1)
        Data(1, 1) = Target.Formula
    Else
        Data = Target.Formula
    End If
        
    Dim r As Long, c As Long
        
    For r = 1 To UBound(Data)
        For c = 1 To UBound(Data, 2)
            If Suspend Then
                If Left(Data(r, c), 1) = "=" Then Data(r, c) = "'" & Data(r, c)
            Else
                If Left(Data(r, c), 2) = "='" Then Data(r, c) = Mid(Data(r, c), 2)
            End If
        Next
    Next
        
    Target.Formula = Data
    
    Application.Calculation = InitialCalculationState
End Sub
英文:

Arrays are much more efficient that iterating over each formula. Setting Application.Calculation = xlCalculationManual will also give the code a performance boost.
I added Suspend as a parameter. This will make it easy to enable and disable the formulas.

Usage:
> SuspendFormula ActiveSheet, True
> SuspendFormula ActiveSheet, False

Code:

Sub SuspendFormula(Worksheet As Worksheet, Suspend As Boolean)
    Dim InitialCalculationState As XlCalculation
    Application.ScreenUpdating = False
    InitialCalculationState = Application.Calculation
    Application.Calculation = xlCalculationManual
        
    Dim Target As Range
    Set Target = Worksheet.UsedRange
    Dim Data As Variant
        
    If Target.Count = 1 Then
        ReDim Data(1 To 1, 1 To 1)
        Data(1, 1) = Target.Formula
    Else
        Data = Target.Formula
    End If
        
    Dim r As Long, c As Long
        
    For r = 1 To UBound(Data)
        For c = 1 To UBound(Data, 2)
            If Suspend Then
                If Left(Data(r, c), 1) = "=" Then Data(r, c) = "'" & Data(r, c)
            Else
                If Left(Data(r, c), 2) = "'=" Then Data(r, c) = Mid(Data(r, c), 2)
            End If
        Next
    Next
        
    Target.Formula = Data
    
    Application.Calculation = InitialCalculationState
End Sub

huangapple
  • 本文由 发表于 2023年7月12日 22:15:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76671545.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定