加快Excel宏？ [英] Speed up an Excel Macro?

问题描述

现在我有一个宏 PopulateYearlyValues 但是在我看来，这太长了

  Sub PopulateYearlyValues（ByVal Month As Range）
 Dim c As Double 
 Dim s As Double 
c = Application.WorksheetFunction.Match（UCase（Month.Value），ActiveSheet .Range（AA5：AX5），0）
s =（ActiveSheet.Range（AA5）。列 -  1）
使用ActiveSheet 
 Dim i As Integer 
 Dim j As Integer 
 For i = 7 To 44 
 .Range（G& i）.Value = 0 
 .Range（H& i）.Value = 0 
对于j = 1到c 
 .Range（G& i）.Value =（.Range（G& i）.Value + .Cells（i，s）.Offset 0，j））
 .Range（H& i）.Value =（.Range（H& i）.Value + .Cells（i，s）.Offset（0，（j + 1）））
j = j + 1 
下一个j 
下一个i 
结束
 End Sub 
  pre> 
 
 
我有一个范围 G7：H44 需要使用 SUM 范围 AA7：AX44 但是它只是每一列：
 如果Month.Value =January
 G7 = SUM （AA7）
 H7 = SUM（AB7）
 ... 
 G44 = SUM（AA44）
 H44 = SUM（AB44）
如果
 
如果Month.Value =April
 G7 = SUM（AA7，AC7，AE7，AG7）
 H7 = SUM（AB7，AD7，AF7，AH7）
 .. 
 G44 = SUM（AA44，AC44，AE44，AG44）
 H44 = SUM（AB44，AD44，AF44，AH44）
 End If 
  
但是我的宏已经太长了..有没有其他的方法呢？
解决方案
您可以尝试将通用的vba优化方法设置为手动和禁用ScreenUpdating。
 code> Dim calc As XlCalculation 
 calc = Application.Calculation 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
 
 Application.ScreenUpdating = True 
 Application.Calculation = calc 
  
将代码或函数调用放在 Application.Calculation = xlCalculationManual 和 Application.ScreenUpdating = True  
 
 
 
 这是从我以前的帖子 
 
Right now I have a macro PopulateYearlyValues But it seems to me it's taking way too long
Sub PopulateYearlyValues(ByVal Month As Range)
    Dim c As Double
    Dim s As Double
    c = Application.WorksheetFunction.Match(UCase(Month.Value), ActiveSheet.Range("AA5:AX5"), 0)
    s = (ActiveSheet.Range("AA5").Column - 1)
    With ActiveSheet
        Dim i As Integer
        Dim j As Integer
        For i = 7 To 44
            .Range("G" & i).Value = 0
            .Range("H" & i).Value = 0
            For j = 1 To c
                .Range("G" & i).Value = (.Range("G" & i).Value + .Cells(i, s).Offset(0, j))
                .Range("H" & i).Value = (.Range("H" & i).Value + .Cells(i, s).Offset(0, (j + 1)))
                j = j + 1
            Next j
        Next i
    End With
End Sub
I have a range G7:H44 that needs to be populated with the SUM of range AA7:AX44 but.. it's only every other column:
If Month.Value = "January"
    G7  = SUM(AA7)
    H7  = SUM(AB7)
    ...
    G44 = SUM(AA44)
    H44 = SUM(AB44)
End If

If Month.Value = "April"
    G7  = SUM(AA7, AC7, AE7, AG7)
    H7  = SUM(AB7, AD7, AF7, AH7)
    ...
    G44 = SUM(AA44, AC44, AE44, AG44)
    H44 = SUM(AB44, AD44, AF44, AH44)
End If
But the macro I have is taking way too long.. Is there any other way to do this?
解决方案
You can try the usual vba optimization methods of setting calculation to manual and disabling ScreenUpdating .
Dim calc As XlCalculation
calc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = True
Application.Calculation = calc
Put your code or function call between Application.Calculation = xlCalculationManual and Application.ScreenUpdating = True 

This is from my previous Post

                        
这篇关于加快Excel宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！