VBA Arrays: Make Your Macros 100 Times Faster
Excel VBA course lesson 9: read a whole range into an array, work in memory, write it back once. Static and dynamic arrays, ReDim…
Read article →The Excel Camera Tool: A Live Picture of Any Range
The Excel Camera Tool: A Live Picture of Any Range
Excel’s hidden Camera tool puts a live, resizable picture of a range anywhere — perfect for one-page dashboards that mix tables of different column…
Read article →CUBERANKEDMEMBER Function in Excel: The n-th Member of a Set
CUBERANKEDMEMBER Function in Excel: The n-th Member of a Set
CUBERANKEDMEMBER returns the n-th member of a set, e.g. the 1st, 2nd and 3rd best customers from a CUBESET: the way to build Top-N…
Read article →SCAN Function in Excel: Running Totals in One Formula
SCAN Function in Excel: Running Totals in One Formula
SCAN walks through an array carrying an accumulator and returns every intermediate result — running totals, running balances, running max.
Read article →VBA String Functions: Left, Mid, InStr, Replace, Split and More
VBA String Functions: Left, Mid, InStr, Replace, Split and More
Excel VBA course lesson 8: clean and reshape text with VBA — Len, Left, Right, Mid, InStr, InStrRev, Replace, Trim, UCase/LCase, StrConv, Split/Join, Like…
Read article →VBA Error Handling and Debugging: On Error, Breakpoints and the Immediate Window
VBA Error Handling and Debugging: On Error, Breakpoints and the Immediate Window
Excel VBA course lesson 7: read VBA error messages, step through code with F8, use breakpoints, the Immediate and Locals windows, Debug.Print, and handle…
Read article →DSTDEV Function in Excel: Sample SD of Matching Records
DSTDEV Function in Excel: Sample SD of Matching Records
DSTDEV returns the sample standard deviation of a field for matching records. Like all D-functions it uses a database range and a criteria range…
Read article →VBA Loops Explained: For, For Each, Do While and Do Until
VBA Loops Explained: For, For Each, Do While and Do Until
Excel VBA course lesson 6: repeat work with For…Next, Step, For Each over ranges and sheets, Do While/Until loops, Exit For, nested loops and…
Read article →RANK Function in Excel: The Old Ranking Function
RANK Function in Excel: The Old Ranking Function
RANK still works for compatibility and behaves exactly like RANK.EQ. New workbooks should use RANK.EQ or RANK.AVG.
Read article →5 Handy VBA Macros: Mod, Second Highest, Sum by Colour and Delete Blank Rows
5 Handy VBA Macros: Mod, Second Highest, Sum by Colour and Delete Blank Rows
Excel VBA course lesson 5: five small, reusable macros explained line by line — every Nth row with Mod, second-highest value, sum by fill…
Read article →Decisions in VBA: If…Then, And/Or and Select Case
Decisions in VBA: If…Then, And/Or and Select Case
Excel VBA course lesson 4: make macros decide with If…Then…Else, ElseIf, And, Or and Not, check empty and error cells, and replace long If…
Read article →DPRODUCT Function in Excel: Multiply Values Matching Criteria
DPRODUCT Function in Excel: Multiply Values Matching Criteria
DPRODUCT multiplies the values of a field for matching records (rarely needed; e.g. compounding growth factors). Like all D-functions it uses a database range…
Read article →