-
Recent Posts
Recent Comments
Patrick O'Beirne, Ex… on Excel formula to test if a wor… Peter Bartholomew on Excel formula to test if a wor… Rick Rothstein on Excel formula to test if a wor… Patrick O'Beirne, Ex… on Excel formula to test if a wor… Peter Bartholomew on Excel formula to test if a wor… Archives
- Mar 2023
- Feb 2023
- Oct 2022
- Mar 2022
- Jan 2022
- Dec 2021
- Nov 2021
- Oct 2021
- Aug 2021
- Jul 2021
- Jan 2021
- Oct 2020
- Sep 2020
- Jul 2019
- Jun 2019
- Apr 2019
- Jul 2018
- May 2018
- Apr 2018
- Mar 2017
- Feb 2017
- Jan 2017
- Sep 2016
- Aug 2016
- Jun 2016
- Apr 2016
- Jan 2016
- Sep 2015
- Aug 2015
- May 2015
- Oct 2014
- Jul 2014
- May 2014
- Apr 2014
- Jan 2014
- Sep 2013
- Aug 2013
- Jul 2013
- Apr 2013
- Mar 2013
- Feb 2013
- Nov 2012
- Oct 2012
- Jul 2012
- Jun 2012
- May 2012
- Mar 2012
- Feb 2012
- Jan 2012
- Dec 2011
- Nov 2011
- Oct 2011
Categories
Meta
Category Archives: Uncategorized
VBA Application methods compatibility in Excel and Access
When working on VBA code that should work in both MSExcel and MSAccess VBA, the different application object methods can be hard to manage. For example, Application.Screenupdating = False is used in Excel but would raise a compile error in … Continue reading
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
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
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
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
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 CSV, Data, Excel, ExcelVirtuallyGlobal, StructuredData, Unicode, VBA
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
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
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