Mới tìm thấy bài này hay hay nên post lên cho các bạn xem thử.
Tôi sẽ tranh thủ dịch ra tiếng Việt sau.
Excel VBA : Efficiency and Performance
by Dermot Balson - William Mercer
Image of Line Break
Does Excel VBA seem to be abnormally slow and sluggish in large spreadsheets? Try these tips, gained through painful experience and a helpful internet.
Tip #1: minimise traffic between worksheets and VBA.
There is substantial overhead in communications between worksheets and VBA. It works both ways. So if you have a custom VBA function which you call from hundreds of worksheet cells, it could be s-l-o-w. Equally, if you use VBA to read in lots of cells to memory, or write lots of data back to cells, it can be real slow.
When working in VBA, the trick is to read/write data in chunks. We had a real life example, where we needed to read in 1000 records off a worksheet, each of 20 cells, do some calculations, and write back 14 cells at the end of each record. That's 34,000 hits on the worksheet. We cut it to just 15. Here's how.
We read in 125 records at once (that's 125x20 = 2500 cells) into an array, then looped through and calculated the 14 output cells for each of these records, and stored them in a memory array. Then we read the next batch of 125 records, and so on, until we hit empty cells. So far we've only made 8 (input) hits on the worksheet. Then we wrote the 14,000 results back by writing columns instead of rows - in fact, two columns at a time, each containing all the 1000 or so records. That only requires 7 writes to take care of the 14 output columns, or just 15 hits in total. The speed improvement was phenomenal.
You may have some questions about this:
How do you read in/write out lots of cells at once?
'to read in
Dim A as Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)
'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..
Why read in 125 records at a time? Why not read in all 1000?
Excel 5&7 can't read in more than about 3000 cells at once or you get an error. The same goes for writing back to a worksheet. So we had to do it in chunks.
Reading in is quick, but writing back takes forever.
Excel 5&7 have a very slow method of writing back to the sheet, so someone came up with a neat workaround. This is 5-10 times faster than setting a range equal to a VBA array. (Don't use this in Excel 8, it is faster to say Range = Array).
Sub SuperBlastArrayToSheet(TheArray As Variant, TheRange As Range)
With TheRange.Parent.Parent 'the workbook the range is in
.Names.Add Name:="wstempdata", RefersToR1C1:=TheArray
With TheRange
.FormulaArray = "=wstempdata"
.Copy
.PasteSpecial Paste:=xlValues
End With
.Names("wstempdata").Delete
End With
End Sub
What about VBA functions used in worksheets?
It's harder to minimise traffic when your worksheet is calling VBA functions. However, something you can do is avoid reinitialising or reading in values over and over. Suppose your function needs 5 parameters, 4 of which are set at the top of the sheet, and only one of which is set by the calling cell. You can cut down the calculation load, by loading the 4 parameters the first time only, by putting a test in your function like "If A=0 Then Initialise", to call an initialisation routine. This can be done because VBA keeps variable values in memory once they are set (unless they are dimensioned within a subroutine or function, in which case they disappear when the function ends) until either you close the worksheet or make any change to the VBA code. So you can set the variables once and use them forever.
Apart from this, you will generally find that it pays to avoid VBA functions in big spreadsheets. Personally, I try to use either VBA or worksheet formulae, rather than a mixture.
Tip #2 : Declare variables for speed
This is pretty well known stuff so I'll keep it brief. Variants are powerful but slow. Use them only when you have to or when it doesn't matter. Ideally, use integers or booleans wherever possible, followed by long, single and double.
And watch this trap..
Dim A, B, C As Integer 'only defines C as integer, the others will be variants
Dim A As Integer, B As Integer, C As Integer 'defines all 3 as integer
And this one. The function Function Something() returns a variant, whereas Function Something() As Integer returns an integer. Lesson: define functions as something.
Tip #3 : avoid overuse of worksheet functions in code
Here's an example. VBA doesn't have a Max or Min function, but Excel does. So you could write..
C = Application.Max (A, B) 'uses Excel worksheet function in VBA to return max of A & B
or do it the hard way in code like this
If A>=B Then C = A Else C = B
Now you won't believe the next part if you haven't seen it already. The code method is about 150 times faster (on my PC, anyway). So if you are doing some big loops, avoid worksheet functions if you can, or at least test comparative speed.
Some built-in VBA functions are real slow too. It's worth testing the speed on any you use a lot, just to be sure.
Tip #4 : Turn off screen updating, recalculation
If you're working with multiple sheets, screen refresh can slow you down and distract your user. Put Application.ScreenUpdating = False at the top of your routine and it will freeze screen updating until all your code has stopped executing.
Similarly, you can suspend auto-recalculation with Application.Calculation = xlManual, and return it to auto at the end with Application.Calculation = xlAutomatic
Tip #5 : Don't select sheet objects to use them
The macro recorder selects objects before working with them. This is not necessary and slows down execution. So you can change this ..
Sheets("Sheet2").Select
Range("D9").Select
ActiveCell.FormulaR1C1 = "3"
to
Sheets("Sheet2").Range("D9") = 3
Remember, Excel is pretty smart, so you only need to describe an object enough to make it unambiguous. In fact, it pays (in speed) not to mention unnecessary objects, because every object you mention has to be resolved by VBA.
So Range("Hobbes") is faster than Workbooks("Comic").Sheets("Calvin").Range("Hobbes").
Tip #6 : Psychological slowness - Give the user feedback
Isn't it frustrating when something is taking forever and you have no idea when it will finish? Time seems to crawl. You can use the statusbar to let users know how things are going. Below is an example based on reading in a datafile. It includes a way of measuring the percentage read so far.
'get length of file initially, divide by 128 for reason given later
'we will show percentage completed
LenF = FileLen(MyFile) / 128
Open MyFile For Input As #1
Do While Not EOF(1) ' read in file until finished
Line Input #1,SomeText
N = N + 1 'counts lines read
'update statusbar every 100 records - not every record!
'Loc returns position in file, in multiples of 128 bytes. (That's why we divided the LenF
'variable by 128. Loc / LenF will give us the percentage read in).
'you could equally show the number of records read
If N Mod 100 = 0 Then Application.StatusBar = "Processed " & Format(Loc(1)/LenF,"0%")
Loop
Close
'give statusbar back to Excel
Application.StatusBar = False
In addition, if your spreadsheet loads slowly because of initialisation routines, you might consider showing a splash screen (say, a textbox attractively labelled with the name of your program, etc) for a few seconds. It all helps pass the time for the user.
Tip #7 : Read/write data to file quickly
A couple of quickies here.. To read a lot of data quickly, try this..
Open MyFile For Input As #1
myVar = Input$(10000,1) 'reads 10000 chars OR
myVar = Input$(Lof(1),1) 'reads whole file
To read/write delimited data instantly between a file and a memory array, try this (Excel 8 only, sorry).
'to write the data
'dimension and fill array A, then ....
Open "c:\temp\test.txt" For Binary As #1
Put #1,,A 'writes whole of A to file
Close
'to read it back
Dim A(30,10) As Single
Open "c:\temp\test.txt" For Binary As #1
Get #1,,A 'reads whole of A
Close
Sưu tầm tại: http://www.avdf.com
Tôi sẽ tranh thủ dịch ra tiếng Việt sau.
Excel VBA : Efficiency and Performance
by Dermot Balson - William Mercer
Image of Line Break
Does Excel VBA seem to be abnormally slow and sluggish in large spreadsheets? Try these tips, gained through painful experience and a helpful internet.
Tip #1: minimise traffic between worksheets and VBA.
There is substantial overhead in communications between worksheets and VBA. It works both ways. So if you have a custom VBA function which you call from hundreds of worksheet cells, it could be s-l-o-w. Equally, if you use VBA to read in lots of cells to memory, or write lots of data back to cells, it can be real slow.
When working in VBA, the trick is to read/write data in chunks. We had a real life example, where we needed to read in 1000 records off a worksheet, each of 20 cells, do some calculations, and write back 14 cells at the end of each record. That's 34,000 hits on the worksheet. We cut it to just 15. Here's how.
We read in 125 records at once (that's 125x20 = 2500 cells) into an array, then looped through and calculated the 14 output cells for each of these records, and stored them in a memory array. Then we read the next batch of 125 records, and so on, until we hit empty cells. So far we've only made 8 (input) hits on the worksheet. Then we wrote the 14,000 results back by writing columns instead of rows - in fact, two columns at a time, each containing all the 1000 or so records. That only requires 7 writes to take care of the 14 output columns, or just 15 hits in total. The speed improvement was phenomenal.
You may have some questions about this:
How do you read in/write out lots of cells at once?
'to read in
Dim A as Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)
'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..
Why read in 125 records at a time? Why not read in all 1000?
Excel 5&7 can't read in more than about 3000 cells at once or you get an error. The same goes for writing back to a worksheet. So we had to do it in chunks.
Reading in is quick, but writing back takes forever.
Excel 5&7 have a very slow method of writing back to the sheet, so someone came up with a neat workaround. This is 5-10 times faster than setting a range equal to a VBA array. (Don't use this in Excel 8, it is faster to say Range = Array).
Sub SuperBlastArrayToSheet(TheArray As Variant, TheRange As Range)
With TheRange.Parent.Parent 'the workbook the range is in
.Names.Add Name:="wstempdata", RefersToR1C1:=TheArray
With TheRange
.FormulaArray = "=wstempdata"
.Copy
.PasteSpecial Paste:=xlValues
End With
.Names("wstempdata").Delete
End With
End Sub
What about VBA functions used in worksheets?
It's harder to minimise traffic when your worksheet is calling VBA functions. However, something you can do is avoid reinitialising or reading in values over and over. Suppose your function needs 5 parameters, 4 of which are set at the top of the sheet, and only one of which is set by the calling cell. You can cut down the calculation load, by loading the 4 parameters the first time only, by putting a test in your function like "If A=0 Then Initialise", to call an initialisation routine. This can be done because VBA keeps variable values in memory once they are set (unless they are dimensioned within a subroutine or function, in which case they disappear when the function ends) until either you close the worksheet or make any change to the VBA code. So you can set the variables once and use them forever.
Apart from this, you will generally find that it pays to avoid VBA functions in big spreadsheets. Personally, I try to use either VBA or worksheet formulae, rather than a mixture.
Tip #2 : Declare variables for speed
This is pretty well known stuff so I'll keep it brief. Variants are powerful but slow. Use them only when you have to or when it doesn't matter. Ideally, use integers or booleans wherever possible, followed by long, single and double.
And watch this trap..
Dim A, B, C As Integer 'only defines C as integer, the others will be variants
Dim A As Integer, B As Integer, C As Integer 'defines all 3 as integer
And this one. The function Function Something() returns a variant, whereas Function Something() As Integer returns an integer. Lesson: define functions as something.
Tip #3 : avoid overuse of worksheet functions in code
Here's an example. VBA doesn't have a Max or Min function, but Excel does. So you could write..
C = Application.Max (A, B) 'uses Excel worksheet function in VBA to return max of A & B
or do it the hard way in code like this
If A>=B Then C = A Else C = B
Now you won't believe the next part if you haven't seen it already. The code method is about 150 times faster (on my PC, anyway). So if you are doing some big loops, avoid worksheet functions if you can, or at least test comparative speed.
Some built-in VBA functions are real slow too. It's worth testing the speed on any you use a lot, just to be sure.
Tip #4 : Turn off screen updating, recalculation
If you're working with multiple sheets, screen refresh can slow you down and distract your user. Put Application.ScreenUpdating = False at the top of your routine and it will freeze screen updating until all your code has stopped executing.
Similarly, you can suspend auto-recalculation with Application.Calculation = xlManual, and return it to auto at the end with Application.Calculation = xlAutomatic
Tip #5 : Don't select sheet objects to use them
The macro recorder selects objects before working with them. This is not necessary and slows down execution. So you can change this ..
Sheets("Sheet2").Select
Range("D9").Select
ActiveCell.FormulaR1C1 = "3"
to
Sheets("Sheet2").Range("D9") = 3
Remember, Excel is pretty smart, so you only need to describe an object enough to make it unambiguous. In fact, it pays (in speed) not to mention unnecessary objects, because every object you mention has to be resolved by VBA.
So Range("Hobbes") is faster than Workbooks("Comic").Sheets("Calvin").Range("Hobbes").
Tip #6 : Psychological slowness - Give the user feedback
Isn't it frustrating when something is taking forever and you have no idea when it will finish? Time seems to crawl. You can use the statusbar to let users know how things are going. Below is an example based on reading in a datafile. It includes a way of measuring the percentage read so far.
'get length of file initially, divide by 128 for reason given later
'we will show percentage completed
LenF = FileLen(MyFile) / 128
Open MyFile For Input As #1
Do While Not EOF(1) ' read in file until finished
Line Input #1,SomeText
N = N + 1 'counts lines read
'update statusbar every 100 records - not every record!
'Loc returns position in file, in multiples of 128 bytes. (That's why we divided the LenF
'variable by 128. Loc / LenF will give us the percentage read in).
'you could equally show the number of records read
If N Mod 100 = 0 Then Application.StatusBar = "Processed " & Format(Loc(1)/LenF,"0%")
Loop
Close
'give statusbar back to Excel
Application.StatusBar = False
In addition, if your spreadsheet loads slowly because of initialisation routines, you might consider showing a splash screen (say, a textbox attractively labelled with the name of your program, etc) for a few seconds. It all helps pass the time for the user.
Tip #7 : Read/write data to file quickly
A couple of quickies here.. To read a lot of data quickly, try this..
Open MyFile For Input As #1
myVar = Input$(10000,1) 'reads 10000 chars OR
myVar = Input$(Lof(1),1) 'reads whole file
To read/write delimited data instantly between a file and a memory array, try this (Excel 8 only, sorry).
'to write the data
'dimension and fill array A, then ....
Open "c:\temp\test.txt" For Binary As #1
Put #1,,A 'writes whole of A to file
Close
'to read it back
Dim A(30,10) As Single
Open "c:\temp\test.txt" For Binary As #1
Get #1,,A 'reads whole of A
Close
Sưu tầm tại: http://www.avdf.com
