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 questions at the Financial Modelling Summit 2022

Slugify text We want to slugify text, ie include only characters a-z, 0-9 and “-“, so that it can become part of an URL. So that for example “Why I say ‘Bazinga!” – my #1 Reason for Using =INDEX(MATCH())!!” Becomes … Continue reading

Posted in Uncategorized | Tagged , | Leave a comment

Excel for Mac, VBA OnKey macros, and MacOS Monterey

You know that feeling when a manufacturer pulls the rug from under you? When a feature (OK, a workaround, even a hack) you’ve been relying on for years suddenly stops working because of an “upgrade”? Welcome to the world of … Continue reading

Posted in Uncategorized | Leave a comment

WordXLe: Wordle in Excel

An Excel version of the word game Wordle, with helper sheets. Continue reading

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

XLTest 1.74 spreadsheet audit addin

I have released an update to my XLTest Excel add-in for spreadsheet auditing, visualisation, and testing. Changes up to version 1.74 (September 2021) Start Session dialog changed to show internal workbook information before opening, and to open in manual calculation … Continue reading

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

Get the real used range of a worksheet

For finding the last used cell of a sheet:
1) The .Find method omits the rows hidden by filtering.
2) Shapes can be placed outside the used range.
3) For reasonably sized sheets, the MAX formula is considerably faster than looping if there are many blank rows below the data. Otherwise, looping comes out about equal.
4) For sheets with more than 45M cells, the looping method is the only one that works.
5) Any data cell found needs to be adjusted for any merged range that it is part of. Continue reading

Posted in Excel/VBA | Leave a comment

Power Query infer data types with Table.Profile

The Power Query UI automatically detects column data types and performs the transformations for you. Typically these steps look like this: However, when I am calling Power Query M code from VBA ( see my previous posts) to open any … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

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 , , | 3 Comments

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 , , , | 3 Comments