Category Archives: Excel/VBA

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

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

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

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

A look back to Eusprig 2019

The 20th conference of the European Spreadsheet Risk Interest Group was held in  Browns of Covent Garden, London on 11-12 July 2019, Thursday 11 July 2019 Developing Excel Thought Leadership David Lyford-Smith, ICAEW, Chartered Accountants’ Hall This described the development … Continue reading

Posted in eusprig, Excel/VBA, Research, Risk, Uncategorized | Tagged | Leave a comment

A look ahead to Eusprig 2019

The 20th annual conference of the European Spreadsheet Risk Special Interest Group (http://www.eusprig.org) will be held on July 11 & 12 at The Barrister’s Court, Browns, 82-84 St Martin’s Ln, Covent Garden, London WC2N 4AG, UK. David Lyford-Smith of the … Continue reading

Posted in eusprig, Excel/VBA, Research, Software Quality, Software Testing | Tagged , , | Leave a comment

UK HMRC MTD OAuth2 Excel/VBA demo

I’m making available as open source my VBA routines for the “Hello World” tests on the UK HMRC “Making Tax Digital” (MTD) web service which use oAuth2 and JSON. Thanks to posts from Bruce McPherson (Excel Ramblings), Tim Hall (Excel-REST), … Continue reading

Posted in Excel/VBA, Open Source | Tagged , , , , , | 1 Comment