MSExcel : quickly freeze header row/col

click the cell on 2nd row 2nd col in a range -> VIEW ribbon -> freezePanes ==> will freeze top row and left-most col

 

MSExcel | shortcut for "false" is "0"

 

MSExcel | select suggested function

use TAB key

 

VBA function call - paren

[[Excel 2007 VBA programmer's reference]] puts it nicely - Please add parentheses if you capture return value. Remove parentheses if you don't capture return value. The syntax rules are not clear, so you might hit compilation error if you disobey the rules.

tenor = Replace(myrange.Cells(1, "F").Value2, "D", "Day")

VBA Range variable - skip delcaration?

Without OptionExplicit, you could omit declaration for most variables.

But for a range variable, better declare it like Dim myRange as Range. If you don't declare, it becomes a Variant, which could fails mysteriously half the times.

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.

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
    targetWB.Activate
End Sub

VBA export/import

You can export a code "Module" (worksheet? probably no) in the form of a file. You can check in the file.

VBA special looping construct - foreach

foreach can loop through cells in a range

foreach can loop through sheets in a workbook

Can foreach loop through row in a range? Yes. for each aRow in theRange.rows ...

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.