使它更快？ [英] Make it faster?
问题描述
我发现即使关闭屏幕更新，尽管更快，仍然会救我约5/10分钟的处理。
所以我的问题是这样的：
速度有多大差异如果程序没有使用Variable = Cells（1，x），而是使用Variable = Worksheets（Sheet1）。Cells（1，x）？
因为它在整个事情之间切换标签之间只需两次，是否值得开始重写它，所以它不？
请考虑所有我的VBA知识已经通过尝试和错误自我教学，所以尽可能使用小字。
编辑：
我收到3张数据（我不创建这些数据，我也不能改变什么/如何显示信息） / p>
工作表A列出了我公司的客户以及每个帐户的负责人。
表B包含的数据详细说明了客户在过去两年中向我公司（包括成本，收入，规模，产品等）的信息购买/出售的信息。
C包含了我们为新的承诺而设计的新产品（所以如果我们的一个员工设法让公司1从现在开始就要求他们从我们的产品X中购买，那么承诺将会被列出这里）。
我被要求做的事情（和我的项目）首先用表B中的信息填写了表A中每个客户的详细信息（所以对于客户端我们现在看到他们在2014年买了X€），那么它将创建一张新的表格，显示在表B中已经履行的C表的所有承诺（我们详细说明这些是因为提供的对于承诺购买某些金额的客户而言，价格（单位）将会降低，问题是许多客户利用这一点，并承诺比实际购买的更多，所以我们需要让它看到谁保持他们的话或者不）。
在这一点上，我创建一个Main.xlsm并将信息转储在那里，我必须为每位员工擅长细节，只显示客户的信息（约56名员工，您可以了解为什么我拼命地将其作为一个程序，而不是每次他们问对于这个报告（至少每月两次））。
我没有使用可怕的.select选项时遇到的主要问题是我被要求为我的报告的某些部分提供具体的格式，它们高度依赖于多个变量。这样做以及大量检查，以确保数据没有错误，使我的程序中的代码批量，因为他们有很多事情，如去年他们有更多的交付，但他们比以前少了百分之一，那么你必须使用blablablabla，如果减少了Z％，那么你必须...或者如果客户去年购买了这个，而不是这个，那么你必须从Sheet A（包括格式）中复制整行，并将其放在一张新表上，您将会命名为No Purchases。
我已经开始使用像：
带范围（单元格（行，detUnit），单元格（BottomRow，detUnit + 2））边框（xlEdgeTop）
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
结束
对于格式化，但我也有很多：
列（prtFilesFORWINAnoMes2）。选择
Selection.Replace What：=，Replac ement：=，LookAt：= xlPart，SearchOrder：= xlByRows，MatchCase：= False，SearchFormat：= False，ReplaceFormat：= False
列（prtAfrKgAnoMes1）。选择
Selection.Replace What： ，替换：=，LookAt：= xlPart，SearchOrder = = xlByRows，MatchCase：= False，SearchFormat：= False，ReplaceFormat：= False
我必须删除潜在的错误，无论输入表A，B，C的数据，都可以（有一个很好的）。 / p>
为了节省一大堆时间，我还要以各种方式重组各种表格，所以我也有
行（1：1）。选择
Selection.AutoFilter
如果ActiveSheet.AutoFilterMode = False然后Selection.AutoFilter
ActiveWorkbook.Worksheets（ActiveSheet.Name）.AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets（ActiveSheet.Name）.AutoFilter.Sort.SortFields.Add Key：= Cells（1，columnaDestiny），SortOn： = xlSortOnValues，Order：= xlAscending，_
DataOption：= xlSortTextAsNumbers
使用ActiveWorkbook.Worksheets（ActiveSheet.Name）.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
。应用
结束
ActiveWorkbook.Worksheets（ActiveSheet.Name）.AutoFilter.Sort.SortFields.Add Key：= Cells（1，columnaOrigin），SortOn： = xlSortOnValues，order：= xlAscending，_
DataOption：= xlSortTextAsNumbers
使用ActiveWorkbook.Worksheets（ActiveSheet.Name）.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
。应用
结束
实际上在每个Sub的开始。
例如，这是从看到表C中的承诺在表B中实现
Do While row< = rowLength
first = CoutryReference'I我们知道哪个是主要国家
选择案例单元格（行，detOrigin）
案例Is = CoutryReference
dir =EXPORT'确定它是导出/导入/ Xtrade
second = Cells（row，detDestiny）
Case Else
如果Cells（row，detDestiny）= CoutryReference然后
dir =IMPORT
second = Cells（row，detOrigin ）
Else
dir =XTRADE
first = Cells（row，detOrigin）
second = Cells（row，detDestiny）
End If
End选择
如果SearchInForwin（dir，first，second，Cells（row，detClient），Cells（row，detProduct））= True Then Call FoundLine（row，dir）
'SearchInForwin将循环（已经有组织）列表B，如果找到匹配
'，它将该行复制到SheetFulfilled并返回TRUE
'FoundLine然后将复制我们当前正在读的inf将其粘贴并粘贴到已完成中
row = row + 1
循环
这是SearchInForwin：
函数SearchInForwin（方向As String，onecountry As String，othercountry As String，company As String，mode As String）As Boolean
Sheets（SHEET B）。选择
Dim foUnd As Boolean，lookingRow As Long
lookingRow = lastHiddenWon'由于它是按字母顺序由公司，
foUnd = False'我们可以跳转到最后一个找到并从那里开始
Do While lookingRow< = Cells（Rows.Count ，forwOrigen）.End（xlUp）.row
如果Cells（lookRow，forwEmpresa）= company Then
foUnd = True首先循环，以快速确定是否有一个简单的匹配
如果Cells（ lookRow，forwDireccion）= direction然后GoTo SecondBuc
End If
If（Cells（lookingRow，forwEmpresa）<公司和foUnd = True）或单元格（lookingRow，forwAno） yearAno然后
foUnd = False'这是因为我们应该只考虑最近一年的购买数据（并且它是预先组织的，所以最新的数据在列表的顶部）
GoTo FIn
End If
lookingRow = lookingRow + 1
Loop
SecondBuc：
foUnd = False
尽管细胞（lookRow，forwEmpresa）=公司和细胞（lookRow，forwDireccion）=方向和细胞（lookingRow，forwAno）= yearAno
'条件是唯一保持这个第二个循环非常短的事情
如果Cells（lookingRow，forwAno）= yearAno和细胞（lookRow，forwDestino）= othercountry和_
细胞（lookRow，forwOrigen）= onecountry和InStr（1，Cells（lookingRow，forwTIPO），mode）> 0然后
调用CopyToHidden（lookingRow，mode）'复制行
foUnd = True
lastHiddenWon = lookingRow + 1
End If
lookingRow = lookingRow + 1
循环
FIn：
SearchInForwin = foUnd
结束函数
我可以上传我的模块的.bas，但是所有的评论/变量都是西班牙语，因为我应该让潜在的同事看到它是可以理解的（意思是他们想要能够点燃我，让别人继续工作，如果他们觉得这样）
如果问题是，速度会提高从某些角度转换像：
对于i = 1到4000
如果Cell（i，2 ）= X然后
...做某事
EndIf
p>
Set Ranges = Cell（i，2），Cell（4000,2）
循环变量
然后，答案是更快的方式。我有类似的情况，大约有10-15张，从1000行到900K行。检查细胞，花费30-60分钟，当我把它变量在5分钟以下。至少在我的经验中，变量总是比从表中读取的更快。我想如果你的阅读1或2次可能不明显。附：上面没有真正的代码。
I am currently with a bit of a dilemma. After months working on a Macro/vba project the thing's gotten massive (about 4K lines of code) and because it has to loop through many lists several times, it can sometimes take up to half an hour before it finishes, sometimes even stopping on its own without any apparent reason (or error message).
I found out that even after turning off the screen update, though faster, it still would save me about 5/10 minutes of processing.
So my question is this:
How much of a difference in speed would there be if the program didn't use "Variable = Cells(1, x)" and instead went for "Variable = Worksheets("Sheet1").Cells(1,x)"?
Because it switches between tabs just twice throughout the whole thing, would it be worth it to start rewriting it so that it doesn't?
Please consider all my VBA knowledge has been self taught through trial and error, so use small words if possible.
EDIT:
I get 3 sheets with a bunch of data (I don't create these and neither can I change what/how the information is shown).
Sheet A has a list of my company's clients along with who's in charge of each account.
Sheet B consists of data detailing things those clients have bought/sold to my company (with information such as costs, earning, size, product, etc) over the past 2 years.
Sheet C contains what new products we've managed to make a new 'promise' for (So if one of our workers manages to get company 1 to claim they'll buy from us product X from now on, that 'promise' will appear listed here).
What I've been asked to do (and my project) is firstly fill out the details of each client on Sheet A with the information from Sheet B (So for client A we'll now see that they bought X€ in 2014), then it will create a new sheet showing all the 'promises' from sheet C that have been 'fulfilled' in Sheet B (The reason we detail these is because the offered prices (per unit) will be lower to clients that've 'promised' to buy certain amounts. The problem is that many clients take advantage of this and promise more than what they actually buy, so we had a need to make this to see who was keeping their word or not).
At this point, I create a "Main.xlsm" and dump the information there, and from there I've got to make excels for each of our employees detailing only the information about their clients (with about 56 employees you can figure why I desperately went to make this into a program rather than do it by hand every time they ask for this 'report' (which is twice a month at least)).
The main problem I'm having in not using the dreaded ".select" option is that I've been asked to give specific format to certain parts of my report, that're highly dependent on multiple variables. This along with a lot of checks to make sure the data isn't faulty somehow makes the 'bulk' in lines of code in my program as they're a lot of things like "If last year they had X% more deliveries but they paid Y% less than the one before, then you've got to blablablabla, and if it was Z% less you have to..." or "If the client's made purchases last year but not this one, you've got to copy the whole line from Sheet A (format included) and put it on a new sheet you'll name 'No Purchases'".
I had started using stuff like:
With Range(Cells(row, detUnit), Cells(BottomRow, detUnit + 2)).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
For the formatting, but I've also got a lot of:
Columns(prtFilesFORWINAnoMes2).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Columns(prtAfrKgAnoMes1).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Where I've got to 'delete' potential mistakes whoever it is that inputted the data for Sheets A,B,C could've made (and there are a LOT).
To save a bunch of time, I also got to reorganize the various sheets in various ways, so I've also got
Rows("1:1").Select
Selection.AutoFilter
If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
ActiveWorkbook.Worksheets(ActiveSheet.Name).AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets(ActiveSheet.Name).AutoFilter.Sort.SortFields.Add Key:=Cells(1, columnaDestiny), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(ActiveSheet.Name).AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets(ActiveSheet.Name).AutoFilter.Sort.SortFields.Add Key:=Cells(1, columnaOrigin), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(ActiveSheet.Name).AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Practically at the beginning of every Sub.
As an example, this is the main piece of code from the "See if promises in sheet C are fulfilled in Sheet B"
Do While row <= rowLength
first = CoutryReference 'I've got to know which is the main country
Select Case Cells(row, detOrigin)
Case Is = CoutryReference
dir = "EXPORT" 'Determine if it's Export / Import / Xtrade
second = Cells(row, detDestiny)
Case Else
If Cells(row, detDestiny) = CoutryReference Then
dir = "IMPORT"
second = Cells(row, detOrigin)
Else
dir = "XTRADE"
first = Cells(row, detOrigin)
second = Cells(row, detDestiny)
End If
End Select
If SearchInForwin(dir, first, second, Cells(row, detClient), Cells(row, detProduct)) = True Then Call FoundLine(row, dir)
'SearchInForwin will loop through the (already organized) list in Sheet B and if it finds a match
' it will copy that line to Sheet "Fulfilled" and return "TRUE"
' FoundLine will then copy the line we're currently reading the information from and paste it into "Fulfilled" as well
row = row + 1
Loop
And This is SearchInForwin:
Function SearchInForwin(direction As String, onecountry As String, othercountry As String, company As String, mode As String) As Boolean
Sheets("SHEET B").Select
Dim foUnd As Boolean, lookingRow As Long
lookingRow = lastHiddenWon 'Since it's alphabetical by Company, with
foUnd = False ' this we can jump to the last one found and start from there
Do While lookingRow <= Cells(Rows.Count, forwOrigen).End(xlUp).row
If Cells(lookingRow, forwEmpresa) = company Then
foUnd = True 'First Loop it to quickly determine if there's a simple match
If Cells(lookingRow, forwDireccion) = direction Then GoTo SecondBuc
End If
If (Cells(lookingRow, forwEmpresa) <> company And foUnd = True) Or Cells(lookingRow, forwAno) < yearAno Then
foUnd = False 'This is because we should only take into account purchase data from the latest year (and it's pre-organized so the most recent data is on the top of the list)
GoTo FIn
End If
lookingRow = lookingRow + 1
Loop
SecondBuc:
foUnd = False
Do While Cells(lookingRow, forwEmpresa) = company And Cells(lookingRow, forwDireccion) = direction And Cells(lookingRow, forwAno) = yearAno
'The conditions are the only thing that keeps this second loop extremely short
If Cells(lookingRow, forwAno) = yearAno And Cells(lookingRow, forwDestino) = othercountry And _
Cells(lookingRow, forwOrigen) = onecountry And InStr(1, Cells(lookingRow, forwTIPO), mode) > 0 Then
Call CopyToHidden(lookingRow, mode) 'Copies the line
foUnd = True
lastHiddenWon = lookingRow + 1
End If
lookingRow = lookingRow + 1
Loop
FIn:
SearchInForwin = foUnd
End Function
I could upload a .bas of my modules, but all the comments/variables are in Spanish because I'm supposed to make it 'understandable' for potential coworkers that might have to take a look at it (Meaning they want to be able to fire me and have someone else continue my work if they feel like it)
If the question is, will the speed improve to switch from something Like:
For i = 1 to 4000
If Cell(i,2) = X then
...do something
EndIf
to
Set Ranges = Cell(i,2),Cell(4000,2)
Loop through Variable
Then the answer is it will be WAY WAY faster. I had a similar situation where there was about 10-15 sheets ranging from 1000 rows to 900K rows. Check cell by cell, took 30-60 min, when i put it in variables it was under 5 min. Variables are ALWAYS faster than reading from a sheet, at least in my experience. I suppose if your reading 1 or 2 times it probably isn't noticeable. p.s. not real code above.
这篇关于使它更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
