–to concat strings, dates and numbers, use text() then “&”
–proper() ==> capitalize
–concat a column of text? Must write a VBA function
–to check if a string starts with T, if(theString = “T*”)
[[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”)
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.
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
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.
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.
Let’s focus on the typical use cases —
* 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.
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
You can export a code “Module” (worksheet? probably no) in the form of a file. You can check in the file.
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 …
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.
* 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.
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.