Microsoft Excel

In order make VBA macros globally available, they should be stored in the special Personal.xlsb workbook.

A worksheet contains a maximum of 1,048,576 rows and a maximum of 16,384 columns. This corresponds to 17,179,869,184 cells. (See Count rows, columns and cells in a worksheet).

Keyboard shortcuts

Some keyboard shortcuts I found useful the other day are …

		VBA equivalent
`home`	move to first cell in row
`shift + F11` or `alt+shift+F1`	Inserts a new worksheet to the left of the currently acctive one. (The current worksheet can be deleted with `alt+h` -> `d` -> `s`)	`sheets.add`
`ctrl + pageUp` / `ctrl + pageDown`	Rotate between worksheets.
`ctrl + space` / `shift + space`	Select entire rows / columns
`ctrl+9` / `ctrl+0`	hides selected rows / columns
`ctrl+1`	opens the format cells dialog.
`ctrl+d`, `ctrl+r`	Copy value above/left of the cell into the current cell.
`ctrl+shift+*` or `ctrl+a`	Selects a rectangular region: the first combination selects a «contguous» range, the second combination the used range of a worksheet	`range.currentRegion`, `activeSheet.usedRange`.
`ctrl+t`	Turn selected region (possibly created with `ctrl+shift+*`) into an Excel table
`ctrl+shif+-t`	Add total column to an Excel table
`ctrl+shift-L`	Turn on/off filtering for specific values (triangle in header row)	`selection.autofilter`
`ctrl+shift` + `=`	Shift cells down	`selection.insert shift:=xlDown`
`ctrl+shift+f` or `ctrl+1`	Opens format cells popup
`ctrl` + `-`	Shift cells up	`selection.delete shift:=xlUp`
`ctrl+q`	opens the query input box (which allows to search for keywords)
`ctrl+F3`	opens name manager (which is found in the ribbon/menu under Formulas -> Defined Names -> Name Manager).
`F9` with and without `ctrl`, `shift`, `alt`	Perform or force formula recalculation
`shift+F10`	opens the context menu.
`alt+F11`	opens the Visual Basic Editor (and when in the VBA Editor, `alt-Q` closes it)
`alt+=`	inserts `=sum(…)`
`ctr+shift+…`	Apply predefined formats to the selected range: `1` = comma, `2`: time, `3`: date, `4`: currency, `5`: percent. `6`: scientific	`.numberFormat = …`
`ctrl+shift+F3`	creates named ranges from a selection (possibly created with `ctrl+shift+*`).	`range.createNames`
`ctr+shift+enter`	enters an array formula.
`ctrl+F1`	shows/hides the Ribbon bar.
`ctrl+backtick` (gravis)	Shows hides formulas in cells	`activeWindow.displayFormulas = true` (or `false`)

TODO: in order to enable content, the sequence alt+f i n enter will do.

Select tabs on the ribbon

Some alt+… combinations select a tab and then allow to select an item on the tab with another key which is displayed next to the element:

alt+f file page, use backstage view
alt+h home tab
alt+p page layout tab
alt+n insert tab
alt+m formula tab
alt+w view tab

F10 shows the keys to be pressed next to the respective tab-selector.

ctrl+F1 shows or hides the Ribbon bar.

Automatically installing a shortcut to calling a sub when an Excel is opened

sub auto_open()
    application.onKey "{F12}", "nameOfSubToCall"
end sub

Reference style for cell addresses

Each cell on an excel worksheet has a unique address. There are two so called reference styles for these addresses:

A1 style
R1C1 style

The A1 style seems to be default: other cells are referenced by the D5 syntax. (fourth column, 5th row)

With R1C1, the syntax for relative addresses of cells is R[-3]C[2] which is the value of the cell 3 rows above and 2 columns to the right.

The syntax for absolute addresses is R4C2 which represents the cell in the 4th row and 2nd column.

The reference style can be changed through the Application object:

application.referenceStyle = xlR1C1 ' or
application.referenceStyle = xlA1

It can also be changed through File -> Options -> Formulas and (un-)ticking R1C1 reference style.

See also the Excel functions functions row and column and address.

In Excel tables, data can be accessed using the so called structured references.

It seems (at least to me) that the reference style cannot be permanently stored except when using a Personal.xlsb file.

State

A running Excel application is always in one of the following four states:

Ready state:

No commands or macros are being run.
No dialog boxes are being displayed.
No cells are being edited and the user is not in the middle of a cut/copy and paste operation.
No embedded object has focus.

Edit Mode:

The user has started to type valid input characters into an unlocked or unprotected cell, or
has pressed F2 on one or more unlocked or unprotected cells.

Cut/copy and paste mode:

The user has cut or copied a cell or range of cells and has not yet pasted them, or
has pasted them using the paste-special dialog box, which enables multiple paste operations.

Point mode:

The user is editing a formula and is selecting cells whose addresses are added to the formula being edited.

When being in one of the last three modes, pressing <ESC> brings Excel back to the ready state.

Connecting to data sources

A data connection allows to tap data that is not stored in a Workbook, but in an external database or file etc.

Such a data connection basically records and stores the information needed to connect to and log into a an external data source and how to get the data from it.

A data connection can be stored with the Workbook or in an Office Data Connection (.odc) or Data Source Name (.dsn) file.

Typical data sources that Excel might connect to are

A data source can obtain the data via an ODBC driver or OLE DB.

Existing data connections can be found in the menu via Data -> Get & Transform Data -> Existing Connections (which is in the Get External Data group).

There are three places where connections might be stored:

In the workbook itself
In connection files (.odc) that are stored in the My Data Sources folder
In connection files on the network

.odc files have the benefit that they can be shared among persons that need to query the same data source.

Query files

Beside data connections files, there are also query files. Besides data source connectin information, they also store a query. There are the following types of query files:

.dqy (relational databases and text files)
.oqy (OLAP)
.rqy (OLE DB data sources)
.iqy (Web query files)

VBA / Object Model examples

Some VBA examples that try to demonstrate how an Excel or a CSV might be accessed include:

TODO

The workbookConnection object.

Coauthoring

Coauthoring allows multiple users to simultaneously edit a workbook that is hosted in the cloud (OneDrive, OneDrive for Business or SharePoint Online). When one users saves the modifications, the other users will see them.

Coauthoring is available to all Excel Online users and to Office 365 customers.

Interesting command line parameters

The imho most interesting command line parameters include

`/e`	Start Excel without startup splash screen and without opening empty workbook. (`/e` = embed). Note, there is also the possibility to turn of the start screen under File -> Options -> General (start up options)
`/p p:\ath\to\working\dir`	Specify the a working dir, for example used when using the Save As functionality)
`/s`	Bypass all files that are located in startup directories (such as, for example, the `%appdata%\Microsoft\Excel\XLSTART` directory) (`/s` = safe mode)
`/a ProgId`
`/x`	Start Excel in a new process.

Misc

On Linux, there is sc, the command line spread sheet.

Finding a named column in a huge sheet

Sometimes, one needs to find a named column in a wide wide sheet, with hundreds or even thousands of columns.

It is possible to select the header-cell by first selecting the row with the column names and then go to the immediate window and type (assuming that one is looking for the column that contains xyz):

application.goto selection.find("xyz")

If one wants to go the the column that is named exactly xyz (rather than it only contains that string), the lookAt option must be used:

application.goto selection.find("xyz", lookAt := xlWhole)

TODO

Apparently, there are add-ins for the Microsoft Dataverse (formerly Common Data Service) that make entities available to Excel.

Links

ImportExcel is a PowerShell module that can be used to create (manipulate?) Excel Sheets without having Excel installed.

Excel-IO is a C# project that simplifies reading and writing Excel workbook