如何在excel VBA中提高userform的节省速度 [英] How to improve the saving speed for userform in excel VBA

问题描述

这是由我创建的用户形式。然后，它被用作输入平台。有一些不同的表，例如.2016，2017 ....保存按钮的逻辑是搜索年份（日期）用户输入和位置正确的工作表。然后，它会找到该工作表的最后一行。

例如，最后一行是第1000行。userform的第一行将保存在第1001行。第二行userform将保存在第1002行....

问题

但是，当我测试在真正的excel文件中，节省的速度太慢了，因为真正的excel文件很大（每个工作表中大约有1XXXX行）。使用8秒保存一行用于userform，13秒保存两行。

显然，节省速度是不可接受的。任何方法都可以改善吗？

 如果ComboBox3.Value =2016然后
工作表（2016）。激活
j = WorksheetFunction.CountA（Worksheets（2016）。Range（A：A））+ 1 
 End If 
 
如果ComboBox3.Value =2017然后
 Worksheets（2017）。激活
j = WorksheetFunction.CountA（Worksheets（2017）。Range（A：A））+ 1 
如果
 
 
 
'1st 
 
 
 
如果ComboBox4.Value =然后
 Else 
单元格，1）= ComboBox434.Value 
单元格（j，5）= ComboBox1.Value 
单元格（j，4）= ComboBox2.Value 
单元格（j，3）= ComboBox3.Value 
如果ComboBox4.ListIndex<> -1然后
单元格（j，6）= TimeValue（ComboBox4.Value&& ComboBox5.Value）
单元格（j，24）= ComboBox4.Value 
单元格（j，25）= ComboBox5.Value 
 Else 
单元格（j，6）=
如果
单元格（j，7）= ComboBox6.Value 
细胞（j，8）= ComboBox7.Value 
细胞（j，9）= ComboBox8.Value 
细胞（j，10）= TextBox2.Value 
细胞（j，11）= TextBox3单元格（j，12）= TextBox4.Value 
单元格（j，13）= TextBox5.Value 
单元格（j，14）= TextBox42.Value 
单元格（j，15）= TextBox43.Value 
单元格（j，16）= TextBox44.Value 
单元格（j，17）= TextBox666.Value 
 
'如果ComboBox4.Value = 然后
 
如果
 
'2nd 
 
j = j + 1 
 
如果ComboBox9.Value =然后
 Else 
单元格（j，1）= ComboBox434.Value 
单元格（j，5）= ComboBox1.Value 
单元格（j，4）= ComboBox2.Value 
 Cells（j，3）= ComboBox3.Value 
如果ComboBox9.ListIndex<> -1然后
单元格（j，6）= TimeValue（ComboBox9.Value&&ComboBox10.Value）
单元格（j，24）= ComboBox9.Value 
单元格如果
单元格（j，7）= ComboBox11.Value 
单元格（j，6）=单元格（j，8）= ComboBox12.Value 
单元格（j，9）= ComboBox13.Value 
单元格（j，10）= TextBox6.Value 
单元格（j，11）= TextBox7单元格（j，12）= TextBox8.Value 
单元格（j，13）= TextBox9.Value 
单元格（j，14）= TextBox45.Value 
单元格单元格（j，16）= TextBox47.Value 
单元格（j，17）= TextBox617.Value 
  

解决方案

您可能可以通过将计算切换为手动来节省一些时间，然后在插入信息后计算。

具体来说，在您的代码开始时：

 应用程序
 .ScreenUpdating = False 
 .EnableEvents = False 
 .Calculation = xlCalculationManual 
结束
  

你的代码的结尾：

 应用程序
 .ScreenUpdating = True 
 .EnableEvents = True 
 .Calculation = xlCalculationAutomatic 
结束
  

此外，您可能想要存储变量数组中的数据，可以一次性插入到工作表中，或者一个数组，并将 Cells（j，1）调整为数组的大小

例如

 单元格（j，1）.resize（Ubound（arr，1），Ubound（arr，2））= arr'需要检查确切的语法
  

这可以最大限度地减少工作表的次数。

This is the userform created by me. Then ,it is used as an input platform .There are some different tables ,eg.2016 ,2017.... The logic of that save button is to search the Year(Date) that user input and location the right worksheet. Then , it will find the last row of that worksheet .

For example , the last row is row 1000. The first row of the userform will save on row 1001.The second row of the userform will save on row 1002....

Question

However , when i test in the real excel file , the saving speed is too slow .As the real excel file is large (Around 1XXXX rows in every worksheet) .It uses 8 sec to save one row for userform , 13sec to save two row .

Obviously , the saving speed is unacceptable . Any method can improve it ?

  If ComboBox3.Value = "2016" Then
    Worksheets("2016").Activate
    j = WorksheetFunction.CountA(Worksheets("2016").Range("A:A")) + 1
    End If

    If ComboBox3.Value = "2017" Then
    Worksheets("2017").Activate
    j = WorksheetFunction.CountA(Worksheets("2017").Range("A:A")) + 1
    End If



    '1st



    If ComboBox4.Value = "" Then
    Else
    Cells(j, 1) = ComboBox434.Value
    Cells(j, 5) = ComboBox1.Value
    Cells(j, 4) = ComboBox2.Value
    Cells(j, 3) = ComboBox3.Value
    If ComboBox4.ListIndex <> -1 Then
    Cells(j, 6) = TimeValue(ComboBox4.Value & ":" & ComboBox5.Value)
    Cells(j, 24) = ComboBox4.Value
    Cells(j, 25) = ComboBox5.Value
    Else
    Cells(j, 6) = ""
    End If
    Cells(j, 7) = ComboBox6.Value
    Cells(j, 8) = ComboBox7.Value
    Cells(j, 9) = ComboBox8.Value
    Cells(j, 10) = TextBox2.Value
    Cells(j, 11) = TextBox3.Value
    Cells(j, 12) = TextBox4.Value
    Cells(j, 13) = TextBox5.Value
    Cells(j, 14) = TextBox42.Value
    Cells(j, 15) = TextBox43.Value
    Cells(j, 16) = TextBox44.Value
    Cells(j, 17) = TextBox666.Value

    'If ComboBox4.Value = "" Then

    End If

    '2nd

    j = j + 1

    If ComboBox9.Value = "" Then
    Else
    Cells(j, 1) = ComboBox434.Value
    Cells(j, 5) = ComboBox1.Value
    Cells(j, 4) = ComboBox2.Value
    Cells(j, 3) = ComboBox3.Value
    If ComboBox9.ListIndex <> -1 Then
    Cells(j, 6) = TimeValue(ComboBox9.Value & ":" & ComboBox10.Value)
    Cells(j, 24) = ComboBox9.Value
    Cells(j, 25) = ComboBox10.Value
    Else
    Cells(j, 6) = ""
    End If
    Cells(j, 7) = ComboBox11.Value
    Cells(j, 8) = ComboBox12.Value
    Cells(j, 9) = ComboBox13.Value
    Cells(j, 10) = TextBox6.Value
    Cells(j, 11) = TextBox7.Value
    Cells(j, 12) = TextBox8.Value
    Cells(j, 13) = TextBox9.Value
    Cells(j, 14) = TextBox45.Value
    Cells(j, 15) = TextBox46.Value
    Cells(j, 16) = TextBox47.Value
    Cells(j, 17) = TextBox617.Value

解决方案

You can probably save some time by switching calculation to manual and then calculate after the information has been inserted.

Specifically, at the start of your code:

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

and again at the end of your code:

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

In addition, you might want to store your data in a variant array, which can be inserted into the worksheet all at one go, or an array and resizing Cells(j,1) to the size of the array

e.g.

Cells(j, 1).resize(Ubound(arr,1), Ubound(arr,2)) = arr 'Need to check for exact syntax

This could minimize the number of times the worksheets are hit.

这篇关于如何在excel VBA中提高userform的节省速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！