优化VBA宏 [英] Optimizing VBA macro

查看：114 发布时间：2017/9/4 2:56:35 excel excel-vba

本文介绍了优化VBA宏的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

宏的工作原理如下：

我们有一个程序以1-120秒的间隔记录值，数据来自Sheet2，动态数据范围B：W列

用户正在Sheet3上输入目标和偏差值

在Sheet2上，如果行中的最大值大于目标值减去偏差值将开始将Sheet复制到Sheet1上的表中

在Sheet1上的表中需要每2分钟显示一次，因此宏将复制每X行宏的运行正常，但是我需要在8个不同的目标值上运行8次。想知道有没有人有任何想法来加快速度。
```
 '具有目标值的单元格地址
 target = Sheets 3）.Cells（93，2）.Value 
'具有偏差值的单元格地址
偏差=表格（3）.Cells（95，2）.Value 
 
'计算测量之间的时间
 lngRowMultiplier = 120 / Sheets（3）.Cells（81，B）。值
 
 
 
'要复制的第一行'
 pasteRow = 34 
'通过'
搜索的行号对于i = 2到8000 
 
'通过'
 s_max_value_range =搜索的范围B&我& ：W& i 
 
'一行中的最大值'
 max_value = Application.WorksheetFunction.Max（Sheets（2）.Range（s_max_value_range））
 
 If（Abs目标 -  max_value）< =偏差）然后
 
'复制最多5个小时或直到行中的最低值将大于目标值+偏差'
对于j = 1至150 
 
'一行中的最小值'
 min_value = Application.WorksheetFunction.Min（Sheets（2）.Range（B& i +（j  -  1）* lngRowMultiplier& ：W&i +（j-1）* lngRowMultiplier））
 
如果（min_value <= target + deviation）然后
 
 
 s_copyRange = B& i +（j-1）* lngRowMultiplier& ：W& i +（j-1）* lngRowMultiplier 
 s_pasteRange =C& pasteRow& ：V& 
 
'复制到Sheet1'
表（2）.Range（s_copyRange）.Copy目的地：=表（1）.Range（s_pasteRange）
表（1）。 Range（B& pasteRow）= Sheets（2）.Range（B& i +（j-1）* lngRowMultiplier）
 pasteRow = pasteRow + 1 
 End If 
下一步j 
i = 8001 
结束如果
下一个i 
  
```
所有帮助赞赏
解决方案
有一些VBA规则，您可以使您的代码更快。

规则＃1。不要复制和粘贴

复制和粘贴（或 PasteSpecial ）功能很慢。使用以下复制和粘贴值的速度快25倍。
```
 范围（A1：Z100）。 = Range（A101：Z200）value 
  
```
如果你这样做，你的代码将大概工作如果您在许多行中执行此操作，可能是Mamory的问题。

规则＃2 。计算

通常，当该单元格或范围的先例更改时，Excel将重新计算单元格或单元格范围。这可能会导致您的工作簿太频繁地重新计算，这会降低性能。您可以通过使用以下语句来阻止Excel重新计算工作簿：
```
  Application.Calculation = xlCalculationManual 
  
```
在代码末尾，您可以使用语句将计算模式设置为自动：
```
  Application.Calculation = xlCalculationAutomatic 
  
```
记住但是，当计算模式为 xlCalculationManual 时，Excel不更新单元格中的值。如果您的宏依赖于更新的单元格值，则必须使用.Calculate方法强制执行Calculate事件，如 Worksheets（1）.Calculate 。

规则＃3。屏幕更新

VBA的其他速度问题是，每当VBA将数据写入工作表时，它将刷新您看到的屏幕图像。刷新图像对性能有相当大的阻力。以下命令关闭屏幕更新。
```
  Application.ScreenUpdating = FALSE 
  
```
在宏的末尾，使用以下命令重新开启屏幕更新。
```
  Application.ScreenUpdating = TRUE 
  
```
规则＃4忽略事件

如果您为工作簿的Sheet1实现了Worksheet_Change事件。在Sheet1上更改单元格或范围时，Worksheet_Change事件将会运行。因此，如果您有一个标准宏来操作Sheet1上的几个单元格，则每次更改表单上的单元格时，您的宏必须在Worksheet_Change事件运行时暂停。你可以想象这个行为会如何减慢你的宏。
```
  Application.EnableEvents = False 
  
```
在代码末尾，您可以使用以下语句将EnableEvents模式设置为True：
```
  Application.EnableEvents = True 
  
```
规则＃5使用语句

当记录宏时，您通常会多次操作相同的对象。您可以通过使用With语句一次性对给定对象执行多个操作来节省时间并提高性能。

以下示例中使用的With语句告诉Excel一次应用所有格式更改：
```
  With Range（A1）。Font 
 .Bold = True 
 .Italic = True 
 .Underline = xlUnderlineStyleSingle 
结束
  
```
将分块操作的习惯纳入到语句中不仅可以使您的宏运行更快，而且可以更容易地读取您的宏代码。

Macro works as follows:
1. We have a program recording values in intervals of 1-120s, data from it is on Sheet2, dynamic data range B:W columns
2. User is entering target and deviation value on Sheet3
3. On Sheet2, if max value in a row is bigger than "target value minus deviation value" it will start copying rows into table on Sheet1
4. In table on Sheet1 values needs to be displayed every 2-min, so macro will copy every X row (depending on intervals)
Macro is working properly but I need to run it 8 times on 8 different target values. Was wondering if anyone have any idea to speed things up
```
'Cell address with target value"  
target = Sheets(3).Cells(93, 2).Value   
'Cell address with deviation value"  
deviation = Sheets(3).Cells(95, 2).Value 

'Calculate time between measurements'  
lngRowMultiplier = 120 / Sheets(3).Cells(81, "B").Value 



'First row to copy into'  
pasteRow = 34  
'Row number to search through'  
For i = 2 To 8000 

    'Range to search through'
    s_max_value_range = "B" & i & ":W" & i

    'Max value in a row'
    max_value = Application.WorksheetFunction.Max(Sheets(2).Range(s_max_value_range))

    If (Abs(target - max_value) <= deviation) Then

        'Copy up to 5 hours or until lowest value in a row will be bigger than target value + deviation'
        For j = 1 To 150

            'Minimum value in a row'
            min_value = Application.WorksheetFunction.Min(Sheets(2).Range("B" & i + (j - 1) * lngRowMultiplier & ":W" & i + (j - 1) * lngRowMultiplier))

            If (min_value <= target + deviation) Then


            s_copyRange = "B" & i + (j - 1) * lngRowMultiplier & ":W" & i + (j - 1) * lngRowMultiplier
            s_pasteRange = "C" & pasteRow & ":V" & pasteRow

            'Copy to Sheet1'
            Sheets(2).Range(s_copyRange).Copy Destination:=Sheets(1).Range(s_pasteRange)
            Sheets(1).Range("B" & pasteRow) = Sheets(2).Range("B" & i + (j - 1) * lngRowMultiplier)
            pasteRow = pasteRow + 1
            End If
        Next j
        i = 8001
    End If
Next i
```
All help appreciated
解决方案
There are a few Rules for VBA with what you can make your code faster.

Rule #1. Don't Copy and Paste

The Copy and Paste (or PasteSpecial) functions are slow. It is about 25 times faster to use the following to copy and paste values.
```
Range("A1:Z100").value = Range("A101:Z200").value
```
If you are doing it this way your Code will Probably work. There is maybe a problem with the Mamory if your are doing this on to many Rows.

Rule #2. Calculation

Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed. This may cause your workbook to recalculate too often, which will slow down performance. You can prevent Excel from recalculating the workbook by using the statement:
```
Application.Calculation = xlCalculationManual
```
At the end of your code, you can set the calculation mode back to automatic with the statement:
```
Application.Calculation = xlCalculationAutomatic
```
Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells. If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method like Worksheets(1).Calculate.

Rule #3. ScreenUpdating

The Other Speed Problem with VBA is, every time VBA writes data to the worksheet it refreshes the screen image that you see. Refreshing the image is a considerable drag on performance. The following command turns off screen updates.
```
Application.ScreenUpdating = FALSE
```
At the end of the macro use the following command to turn screen updates back on.
```
Application.ScreenUpdating = TRUE
```
Rule #4 Ignore Events

If you have a Worksheet_Change event implemented for the Sheet1 of your workbook. Any time a cell or range is altered on the Sheet1, the Worksheet_Change event will run. So if you have a standard macro that manipulates several cells on Sheet1, each time a cell on that sheet is changed, your macro has to pause while the Worksheet_Change event runs. You can imagine how this behavior would slow down your macro.
```
Application.EnableEvents = False
```
At the end of your code, you can set the EnableEvents mode back to True with the statement:
```
Application.EnableEvents = True
```
Rule #5 With statement

When recording macros, you will often manipulate the same object more than once. You can save time and improve performance by using the With statement to perform several actions on a given object in one shot.

The With statement utilized in the following example tells Excel to apply all the formatting changes at one time:
```
With Range("A1").Font
.Bold = True
.Italic = True
.Underline = xlUnderlineStyleSingle
End With
```
Getting into the habit of chunking actions into With statements will not only keep your macros running faster but also make it easier to read your macro code.

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

查看全文

优化VBA宏 [英] Optimizing VBA macro

问题描述

相关文章

Office最新文章

热门教程

热门工具

登录关闭

优化VBA宏 [英] Optimizing VBA macro

问题描述

相关文章

Office最新文章

热门教程

热门工具

登录 关闭

登录关闭