Nieuwste downloads
Leuk en netjes afgewerkt excelvoorbeeld. Dit is de tekst van de schrijver van het bestand:
Dit ...
Bestand voor halfautomatische trekking en rangschikking van viswedstrijden. Dit bestand kan aan...
Doel van het bestand is het administreren van Cliënt bonus spaarpunten.
Per gekocht artikel kan...
FreeZZP is een gratis geautomatiseerd excelbestand waarmee kleine zelfstandigen hun kostenadmini...
Het bijgevoegde bestand biedt de mogelijkheid een voetbalcompetitie bij te houden m.b.v. Excel. H...
|
|
What is the fastest way to scan a large range in Excel? |
 |
 |
 |
Today's author, Dany Hoter, a Product Planner on the Excel team, talks about some performance characteristics he recently discovered using various methods to manipulate large ranges using VBA. Problem descriptionYou have a large range in Excel with data. Let's say it contains 100,000 rows and 50 columns for each row (Yes you are using Excel 2007 of course). So altogether you have 5,000,000 cells. Columns A to F have some alphanumeric data that you need analyze and based on the combination of values for each row you need to use the numeric values in G to H to do some calculations and store the results in columns I and J. You could place 200,000 formulas in I and J but you see that a spreadsheet with such a volume of formulas gets very sow and consumes huge amounts of memory. You decide to try and solve it in a piece of VBA code. The question is how to implement such a task in the most efficient way? What are your optionsHow can you scan a range in Excel, read the values in some cells, and change some others? Use a range objectLet's assume that the range you want to read starts at A1 The code looks something like this: Dim DataRange as Range ' Could also be Dim DataRange as Object
Dim Irow as Long
Dim MaxRows as Long
Dim Icol as Integer
Dim MaxCols as Long
Dim MyVar as Double
Set DataRange=Range("A1").CurrentRegion
MaxRows= Range("A1").CurrentRegion.Rows.Count
MaxCols= Range("A1").CurrentRegion.Columns.Count
For Irow=1 to MaxRows
For icol=1 to MaxCols
MyVar=DataRange(Irow,Icol)
If MyVar > 0 then
MyVar=MyVar*Myvar ' Change the value
DataRange(Irow,Icol)=MyVar
End If
Next Icol
Next Irow Use the selection and move it using offsetMany VBA developers learned VBA techniques from macro recording. When using relative reference the generated VBA code creates statements like: ActiveCell.Offset(0, -1).Range("A1").SelectAs a consequence many developers adopt this technique and use the ActiveCell or selection ranges to move from cell to cell in code and read or write the cell values. The code will look like this: Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
Range("A1").Select
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
For Icol = 1 To MaxCols
MyVar = ActiveCell.Value
If MyVar > 0 Then
MyVar=MyVar*Myvar ' Change the value
ActiveCell.Value = MyVar
End If
ActiveCell.Offset(0, 1).Select ' Move one column to the right
Next Icol
ActiveCell.Offset(1, -MaxCols).Select ' Move one rows down and back to first column
Next Irow Use a variant type variableThis technique copies the values from all cells in the range into a variable in memory, manipulates the values inside this variable and if needed moves the values back to the range after manipulation. Here is the code this time: Dim DataRange As Variant
Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
DataRange = Range("A1").CurrentRegion.Value ' Not using set
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
For Icol = 1 To MaxCols
MyVar = DataRange(Irow, Icol)
If MyVar > 0 Then
MyVar=MyVar*Myvar ' Change the value
DataRange(Irow, Icol) = MyVar
End If
Next Icol
Next Irow
Range("A1").CurrentRegion = DataRange ' writes back the result to the range Another difference is that this method is blazing fast compared to the two others. Performance SummaryI compared the three methods on relatively large ranges and here are the results:
| Method | Operation | Cells/Sec | | Variant | Read | 1,225,490 | | | Write | 714,286 | | | Read/Write | 263,158 | | Range | Read | 250,000 | | | Write | 1818 | | | Read/Write | 1,852 | | Offset | Read | 206 | | | Write | 200 | | | Read/Write | 203 |
As you can see using a variant variable is much faster especially when changing cells. Even if the calculation can be done with Excel formulas, in some cases this method is the only one acceptable because using a very large number of formulas can become very slow. Obviously the one method to avoid is moving the ActiveCell using Offset.  |
Artikel weergeven...
|
Nieuwste vragen op het forum
|
Wie zijn er online?
We hebben 5 gasten online
|
