.
...
rows | columns | cells | memory (bytes) | memory (MB) |
|---|---|---|---|---|
1,000 | 10 | 10,000 | 4,000,000 | 4 |
20,000 | 20 | 400,000 | 160,000,000 | 153 |
50,000 | 20 | 1,000,000 | 400,000,000 | 381 |
50,000 | 30 | 1,500,000 | 600,000,000 | 572 |
If you have a complex workbook with many sheets and you aren't sure how large it is, here's a simple macro you can use for calculating the total number of cells and approximate memory required:
Private Sub Workbook_Open()
MsgBox ("Worksheet count: " & ActiveWorkbook.Worksheets.Count)
Dim cellcount As Double
cellcount = 0
For Each Sheet In ActiveWorkbook.Worksheets
cellcount = cellcount + Sheet.UsedRange.Cells.Count
'MsgBox (Sheet.Name & ": " & Sheet.UsedRange.Cells.Count)
Next
Dim message As String
message = "Total cells in workbook: " & vbCrLf & FormatNumber(Round(cellcount), 0) & vbCrLf
message = message & "Approximate memory required (at 300 bytes per cell): " & vbCrLf & FormatNumber(Round(cellcount * 300), 0) & " bytes " & vbCrLf & Math.Round(((cellcount / 1024 / 1024 / 1024) * 300), 2) & " gigabytes"
MsgBox (message)
End Sub
| Vbnet | ||||
|---|---|---|---|---|
| ||||
Private Sub CalculateCells() End Sub |
Best Practices for Large Reports
...