Recent Comments
Author Archives: Patrick O'Beirne, Excel/VBA developer
Excel dynamic array cumulative sum
To calculate a 2-D array where each row is the cusum of each row in the source table, it’s possible to use the MMULT function. The matrix multiplication is done with an upper-right triangular array of 1s. That’s fine for … Continue reading
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 A.I., ChatGPT, Excel, Officescript, openai, scripting, Typescript
1 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
Patrick O'Beirne @ sysmod 