Author Archives: Patrick O'Beirne, Excel/VBA developer

About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest

Excel Virtually Global 2021

I am one of the 48 speakers at a 48-hour Excel extravaganza online on Oct 12 and 13. It’s FREE but you are encouraged to make a donation to charity to reflect the value to you of the content you … Continue reading

Posted in Uncategorized | Tagged , , , , , , | Leave a comment

Event Tracing is three times faster than OutputDebugString

I use the OutputDebugString API in Windows to send debugging messages without the line limitations of the VBA Immediate Window. This is also useful when tracing the causes of crashes which kill the Excel process. I use TraceSpy to capture … Continue reading

Posted in Uncategorized | Tagged , , , | Leave a comment

VBA Performance Profiler : timing procedure execution

This is an update and replacement for the post on 9th March 2017 titled ‘Free add in to profile VBA speed and coverage’. Updated 8-Jul-2021 with an example profile report. Download XLTEST_VBA_Profiler.xlam. (Updated 22-Jul-2021: Tracing P_.O also sends to OutputDebugString … Continue reading

Posted in Uncategorized | Tagged , , | 1 Comment

Querying Text/CSV files in VBA, method #3: Power Query

The module PowerQueryText.bas contains VBA code to query text/csv/tab delimited files and return tables to worksheets. Continue reading

Posted in Excel/VBA, Power Query | Tagged , , , | 2 Comments

Querying Text/CSV files. Method #2: ADO Recordset

The module ADOQueryText.bas has the functions to read a delimited text file into a worksheet. It uses an ADODB recordset to perform the query. Continue reading

Posted in Excel/VBA | Tagged , , , , | Leave a comment

Querying Text/CSV files. Method #1: Querytable

The legacy Get Data from Text/CSV command in Excel creates a Querytable. This is pretty good for most purposes, except… Continue reading

Posted in Excel/VBA | Tagged , , | Leave a comment

Reading Unicode file contents as text

This is a short update on my post of Sep 28, 2020 on reading Unicode text in VBA. I am reposting the module as ADOStream.bas Continue reading

Posted in Excel/VBA | Tagged | Leave a comment

Hello (the rest of the) World – reading data in other languages

Have you ever opened a text file in Excel and found that the characters looked weird or corrupted? The Wikipedia entry for mojibake says ‘In German, Buchstabensalat (“letter salad”) is a common term for this phenomenon, and in Spanish, deformaciĆ³n … Continue reading

Posted in Excel/VBA | Tagged , , , | Leave a comment

UK Covid-19 Track & Trace Excel snafu: Uncontrolled spreadsheets lead to data loss

The story broke around October 5th that Public Health England temporarily lost 16,000 Covid test results because they did not notice that the XLS data format they were using was insufficient to process the number of results they were getting. … Continue reading

Posted in Uncategorized | 1 Comment

Nested JSON to flat data tables

Have you ever had to convert a file of JSON text to Excel tables? Not simple JSON, but one with a collection of dictionaries inside a dictionary inside a collection of dictionary records inside another collection of records inside a … Continue reading

Posted in Uncategorized | Tagged , , , , | Leave a comment