Excel Names collection – like Hashmap+symbolTable

If the Names collection has 55 pairs, then for each you can change the key and you can change the value.

Each pair also has a scope like a variable scope.

Therefore the Names collection is a hybrid between a hashmap and a symbol table (holding global variables).

If you use a name to hold a value (and save the file), it retains the value even after power-down.


MSExcel | special LEDs

These special LEDs “radiate” useful information, but many users don’t recognize them.
–Formula ribbon -> showFormulas
Affects entire workSheet, but not entire workBook
–status bar quick stats, showing sum, average by default. customizable by right-click
–in function wizard, bold args are mandatory
— single $ vs 2 separate $s in a formula
$K33 means FIX column K when we copy the formula;
K$33 means FIX row 33

MSExcel | time-savers

–select a row of cell populated with formulas -> double click on the right-bottom handle ==> will auto drag downward


–rename a tab, quickly — double click the tab

–to see (long) list of sheets Vertically, right click on the tab-navigator buttons

–to clear format on a cell — Home ribbon -> clear button

— sort quickly — click any cell in the key column -> Data ribbon -> ascendingSort button ==> will sort the entire “natural table”

–VIEW ribbon -> arrangeAll -> TILE ==> 2 workbooks side by side …. easiest way to move tabs between workbooks

–to copy formatting from source cell to target cell

click source cell -> home ribbon -> formatPainter -> click target cell



MSExcel | cursor-jump by ctrl-arrow

Very useful when we have large blocks of data.
 –ctrl-anyArrow ==> jump to boundary… always lands on {next populated cell or the physical limit of the “universe”}
This is the basic technique.
–ctrl-shift-anyArrow ==> range-select
–to select current “block”
click on the top left “starting point” -> ctrl-shift-right -> ctrl-shift-down
Alternatively, click anywhere in a block -> ctrl-*
–Range.End() pitfalls

Rule – End(xxx) under VBA automation == Ctrl+Arrow
Rule – it always transports you i.e. jumps to a populated cell, never an unpopulated cell.

Jargon – An “Edge” cell is a populated cell with at least one unpopulated neighbour.

Happy path – If you are in the middle not edge of a populated region, then End() would jump to the Edge

If you are on an unpopulated cell, then you jump to the next edge cell.

If you are already on the right-edge, end(right) would first (implicitly) jump to the RHS neighbour (unpopulated), then do the actual jump.

MSExcel | find/replace

–can change all red cells to blue background
–can limit the replacement to within a (disjoint) selection
–look-In “values”
needed if the search “needle” appears in a formula’s evaluated output but not in the formula’s “source code”

VBA Application.VLookup vs Application.WorksheetFunction.VLookup

WorksheetFunction.VLookup throws no-match exception that can’t be caught!

Dim pair As Variant
pair = Application.WorksheetFunction.VLookup(Key, haystack, 2, False)
If IsError(pair) Then pair = “”

If you remove “WorksheetFunction”, Application.VLookup failure is catchable.

vlookup last arg (“sorted closest match”) defaults to TRUE!
The table array can include headers — does’t matter

VBA range introspection

Myrange.Column (and Row) is a readonly property of Myrange. Absolute address. Myrange.Column == 2 if “B” is first column of the range.

(It's quite hard to find this tip online!)

Note there's no Cell object in EOM i.e. excel object model. You work with single-cell ranges instead. In VBA lingo, “Cell” or “Cells” are always (part of) property of a range or property of a sheet.

implicit objects in excel object model

In the EOM i.e. excel object model, Fully qualified form is Application.Workbooks(“Book1.xlsx”).Sheets(1).Range(“A3”)

* qq[Application. ] is always optional and implicit
* If the workbook you specified is the Active workbook, then qq[Workbooks(….). ] is optional and implicit
* if the Sheets(1) you specified is the Active sheet, then qq[Sheets(1). ] is optional and implicit

These implicits are good for impatient coders, but bad for newbies. For beginners, it’s safer to always fully qualify these references.

If your VBA code behaves strangely, you can fully qualify all EOM references. If behaviour changes, then your assumptions of the implicits are questionable.

Eg: After sheetA.copy after.workbook8.sheet(3), the active workbook switches from the parent of sheetA to workbook8. This is very implicit and caught me by surprise.

VBA ≅ javascript on a webpage (Singletons)

Let’s focus on the typical use cases —

* A typical javascript app Operates on a DOM tree of the enclosing webpage.
* A typical VBA app operates on the EOM tree (Excel object model) of an attached workbook.

—-} In both scenarios, the programmer’s Main job is manipulation of the objects in the *OM. The *OM tree is essentially a big family of singletons. I’d say most GUI apps rely heavily on singletons even if you don’t like singletons.

Usually the VBA app is tailor made for that particular workbook. Tight coupling between the workbook and the VBA module. Javascript is similar.

simple VBA – copy sheets from Workbook A to B

Sub Btn_Click()
    Dim ws As Worksheet
    Dim targetWB, sourceWB As Workbook
    Set targetWB = ActiveWorkbook
    Workbooks.Open Filename:=Range(“B1”).Text, ReadOnly:=True
    Set sourceWB = ActiveWorkbook
    For Each ws In Worksheets
        ws.Copy After:=targetWB.Sheets(targetWB.Sheets.Count)
    Next ws
    sourceWB.Close SaveChanges:=False
End Sub

VBA variables lifespan ^ scope

If you dislike perl’s strictVars, then skip —

OptionExplicit must be declared in each module. Like perl strictVars. VBE -} menubar -} tools -} options -} editor tab -}RequireVariableDefintion would insert OE in every new module.

–var Scopes
* procedure-scope, where “procedure” also includes functions. Remember the “Locals” window.
* module-scope? shared among procedures within the module. Less popular
* public? global, across modules.
* There might be other scopes but less used. I’d say procedure scope and public are the most used.

–var lifespan is a less commonly used feature.
Look at “staic” variables — just like C.

When a macro completes, static/module/public variables will keep the residual value whereas procedure variables are destroyed.

COM api argument passing VBA to c#

Context – a c# COM addin exposes a “service” or API method callable from VBA. VBA passes an excel range object into the api. 2 ways to pass the object.

1) submit (theRange) – c# gets a 2D array of Objects

2) submit theRange – c# gets a “System._ComObject” instance. You need to cast it to a Microsoft.Office.Interop.Excel.Range object. Your debugger may not show the content of the object, but you can read all the data content.