Skip to main content
news

Re: spreadsheet ergonomics

DFS
Google Google
SubjectRe: spreadsheet ergonomics
FromDFS
Date04/06/2017 04:01 (04/05/2017 22:01)
Message-ID<oc479v$oou$4@dont-email.me>
Client
Newsgroupscomp.os.linux.advocacy
Followsowl
FollowupsDFS (16m)
owl (42m) > DFS
On 4/5/2017 5:17 PM, owl wrote:

owl
https://vid.me/pG0C

zero to 52000 tables in 2.57 seconds. Each with locked labels and formulas. Instantaneous jump to any table.

DFS
hot damn!

owl
1.4 million tables in a just over a minute. This is in bash.

code?

I might try it in C and see if I can get it down to a few seconds per million.

Looking at your vid, a 'table' appears to be an 11-row by 1-column name-defined range in an sc sheet.

Max in Excel 2003 would be approx 1.525M 'tables': (65536 rows x 256 columns) / 11.

Public Sub createtables()

Const MAXROWS = 65535 Const MAXCOLS = 256 Dim startTime As Date, endTime As Date Dim row As Long, col As Integer Dim tblcnt As Long, tablesize As Integer row = 1 col = 1 tblCount = 1 tblSize = 11

startTime = Timer For col = 1 To MAXCOLS For row = 1 To (MAXROWS - tblSize) Step tblSize Cells(row, col).Select ActiveCell.FormulaR1C1 = "Table" & tblCount ActiveWorkbook.Names.Add Name:="Table" & tblCount, _ RefersToR1C1:=Chr(col + 64) & row & ":" & Chr(col + 64) & (row + tblSize) Cells(row + tblSize - 1, col).Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)" tblCount = tblCount + 1 If Timer - startTime >15 Then Debug.Print tblCount & " tables in 15 seconds" Exit Sub End If Next row Next col endTime = Timer Debug.Print tblCount & " tables in " & Format(endTime - startTime, "0.0") & " seconds"

End Sub

Results: 5169 tables in 15 seconds

http://imgur.com/a/2CugK

(note: it's not perfect - the range definitions are set for the correct range of cells, but they're surrounded by quotes, like ="F12:F23" so you can't use goto TableN. My bug and I don't care to fix it. The SUM formula is correct, though)

Or I could try it in Python to see how slow I could make it.

The way to think about python speed is this: the code is purty.

Good article here - not about python http://blog.fourthbit.com/2014/03/01/the-best-programming-language-or-how-to-stop-worrying-and-love-the-code

DFS
You could be a heavyweight contender in the sc event in the Owlympics.

owl
I've got enough on my plate already, what with managing Bohemian Grove and suing pornographic comic book publishers...

I understand. I'm busy preparing my Ted talk ("Usenet addiction: healthier than cocaine?") for later this month, and kidnapping neighbors' dogs for ransom.

Google
DFS (16m)
owl (42m) > DFS