VBA / Office Splitting Strings with Excel TEXTBEFORE / TEXTAFTER: Changing a Single Delimiter for Observation
Reviewing string splitting with Excel TEXTBEFORE / TEXTAFTER for practical business use cases.
VBA / Office
VBA / Office VBA / Office Creating Master Candidates with Excel UNIQUE + SORT: Check for Duplicates Without Deleting Originals
Use UNIQUE and SORT to generate a duplicate-free list while keeping the original data intact, and observe the changes when adding a single entry.
VBA / Office Safely Handling 0 Results with Excel FILTER: Trying the Third Argument if_empty
Explicitly handle the case of 0 results in the FILTER function using the third argument if_empty, and observe the difference from #CALC!.
VBA / Office Finding Unregistered Data with Excel XLOOKUP
Check for missed reconciliations using the unmatched display feature of XLOOKUP.
VBA / Office Breaking down long formulas with the Excel LET function: How to verify intermediate results step by step
Try using the LET function to assign names to intermediate results and verify long Excel formulas one step at a time.
VBA / Office Preventing monthly CSV failures from changing column names: Handling column name variations with Power Query M
We test a monthly CSV import process that standardizes only permitted column name variations in Power Query M while ensuring missing columns or unknown changes are not overlooked.
VBA / Office Can you create custom events in VBA? Trying out Class, WithEvents, and RaiseEvent with minimal code
Observe a minimal event-driven setup where a custom event is declared in a VBA Class Module, raised with RaiseEvent, and handled with WithEvents.
VBA / Office Dictionary is Convenient for Counting Duplicates in VBA — Aggregate Securely Without References
Use Scripting.Dictionary with Late Binding to aggregate duplicate values in column A. Output results to a dedicated sheet to prevent accidental deletion of input data.
VBA / Office Bulk Delete Empty Rows in Excel — Safely Cleaning Only the Target Table with Office Scripts
Using Office Scripts to delete only completely empty rows in a specified Excel table from the bottom up. When multiple tables exist, the target name must be specified to make it harder to accidentally modify other tables.
VBA / Office Saving UTF-8 Without Garbled Characters in VBA — Using ADODB.Stream Without Reference Settings
Call ADODB.Stream from VBA using Late Binding to save UTF-8 BOM-included text to a file. We also organize where and when to use Charset.