英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论