Tuesday, March 7, 2017


--- My hub ---

2016

Did I mention that I love spreadsheets?

In addition to ledger type calculations (the "real" purpose of spreadsheets), I like them for layouts (which see my LabBooks as an example) and control documents. In the latter case, I plan to start on a toolbox spreadsheet pretty soon that has things like counters and timers and randomizers. I'll chronicle the development of that in the Mathematics LabBook.

Also, I have a control spreadsheet as a hub for the resources in my computer. I'd make a copy available but I also use it to store personal information. I can tell you a little about it, though.

It's a LibreOffice spreadsheet document. There's a page for each topic such as computer programming, language, social sciences, etc. The first page has links to commonly used general resources and links to each of the other sheets. I also have sheets of links to equipment sources, personal information, personal contacts, project plans, and a keyword index to the Yale - New Haven volumes.

Each sheet has several sections. In the upper right corner are buttons that open programs. These are macro driven. For instance, the button that opens GraphCalc is just a rectangle attached to the following macro:

Sub GraphCalc
Shell("C:\Program Files (x86)\GraphCalc\GrphCalc.exe")
End Sub

I'll go into more depth about programming using LibreOffice's version of BASIC, but this is a simple program and works exactly as I have copied it (as long as GraphCal is installed in the Program Files (x86) directory).

All LibreOffice Basic macros are either subroutines (Subs) or functions. A function returns a value, either to another chunk of code or to a cell or range on the spreadsheet. A subroutine doesn't return a value but does something in the document. This one opens a program. And subroutines need some event to activate them from a spreadsheet, like clicking a drawing (they can also be simply called by other programs.) This small program only needs a simple heading that says whether it's a sub or a function and gives the name: Sub GraphCalc.

The shell statement does all the work. It has two parts: a command "Shell", and the path to the executable file it's opening in parentheses and quotes. An easy way to get the path is to right click on a shortcut to the executable file and select Properties. When the dialog opens, the path is already highlighted, so all I have to do is press Ctrl C, which is the keyboard shortcut for copy, position the cursor after "Shell("" in my macro, and press Ctrl V, which is the keyboard shortcut for paste. I close the macro off with another quote and parenthesis and the End Sub statement and it's ready to go.

To connect the rectangle on the spreadsheet to the macro, I simply use the Drawing toolbar to draw the rectangle where I want it and, then right click on the rectangle and select Assign macro. A dialog opens up that allows me to choose the macro to attach to the rectangle.

The Shell statement works well with all executable files with the extension .exe. It works with other executable files also but there are some that it won't open, like some Java files. I just make a note that there's a shortcut in a folder I keep for such things.

Below the program links, I have a section of links to commonly used websites, then, below that, links to useful spreadsheet documents. Those are often specialty calculators. I can use LibreOffice's hyperlinks dialog to create website links and links to other documents.

To the right of the website links, I have a list of equipment in my home lab. I try to keep the material part of my adventuring simple, inexpensive, and in portable containers. I also have an inventory spreadsheet that tells me where everything is. If I have to move (as I have had to do so often in my past), I want everything to be ready to go.

Below that, I have links to my digital library - all the reference books in the topic that I commonly use. Those are just hyperlinks and LibreOffice has a command and dialog that helps construct those. It's easy.

I also have other useful information entered into cells on the spreadsheets.

This hub places everything I commonly use in my memory in one organized place so I can go right to it when I want it.


No comments: