-
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
Author Archives: Patrick O'Beirne, Excel/VBA developer
ChatGPT 3.5 Excel script
Everybody seems to want to use AI to create Excel formulas, so here is a way of building an interface to the OpenAI ChatGPT 3.5 model. Leila Gharani has already published a video and the Office Script code to call … Continue reading
Posted in Excel/VBA
Tagged Excel, scripting, A.I., ChatGPT, Officescript, Typescript, openai
Leave a comment
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 formula to test if a word contains certain letters
Victor Momoh in the Global Excel Summit, to start a discussion on readability, provocatively used MMULT in a formula to determine whether a word contains all five vowels. As Victor said, in real life he’d use a simple SUM:=SUM(1*ISNUMBER(SEARCH(MID(“aeiou”,SEQUENCE(1,5),1),A2))) SEQUENCE generates … Continue reading
Posted in Excel/VBA
7 Comments
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
WordXLe: Wordle in Excel
An Excel version of the word game Wordle, with helper sheets. Continue reading
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
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
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