Excel Articles

This page lists all articles currently available on this site (articles that fall under various categories will be listed at each one).

Tips and tricks

On my Excel tips page I collect numerous tips and tricks about Excel and VBA.

Office Script

Excel Office Script Basics Recording Office Script in Excel on-line
Excel Office Script Examples Some example Office Scripts, like a script to add a Table Of Contents to your workbook
Calling an office script in Power automate (add a table of content to all files in a OneDrive folder)

VBA

Creating modern-looking userforms in VBA: How do I create a more modern-looking userform in VBA
An Excel Add-in that installs itself: How do I make it easy for my users to install this add-in I created
Create Addins: How to rework a macro so it is ready for distribution (as e.g. an addin)
Smart VBA Macro Recording (YouTube video), Presentation at the Excel Virtually Global conference, July 23rd, 2020
Handling Worksheet control events using a class module
How to put the CallByName function to use to find cells by a specified property.
A macro to change the formatting of cells so they only show a predefined number of significant digits
An example of how to disable your events
VBA Bug: Macro in Excel Stops After A Workbook.Open Command
How to use Parameters With Web Queries
An article about Registering a User Defined Function with Excel, showing how to show argument descriptions in the function wizard
A method to enable the user to undo changes done by your VBA routines
Getting a range from the user with VBA (Bug in Application.InputBox function)
Transpose a table of cells without garbling formulas
Docking your windows in the VBE
Updating addins through the internet
Installing An Excel Add-in Using Setup Factory (also shows how Excel add-ins are managed in the registry)
Working with Data Tables in Excel (VBA)
The Excel File format; Working with Worksheet data
The Excel File Format; Changing properties of Form controls
The Excel File Format; Editing elements in an OpenXML file using VBA
The Excel File Format; Adding RibbonX code to an Office OpenXML file using VBA
A simple Object listing tool
Catching Paste Operations (prevents wrecking of your validation)
Showing An Array On A Form; Autosizing ColumnWidths Of A ListBox
Fixing links to UDFs in an addin
Editing elements in the OpenXML file using VBA
Preventing Workbook_Open event from running
Declaring API functions in 64 bit Office
Pivottable Slicers
Keeping modeless userforms on top of Excel
An MSForms (all VBA) treeview
A class module to measure performance of your VBA code
Building an Excel Add-in
Wheel of Fortune
Enabling Drag and Drop between two Listboxes on a VBA Userform

Various stuff

Working with Data Tables in Excel
Working with Data Tables in Excel (VBA)
Pivottable Slicers
Excel add-ins fail to load
Opening Corrupt Excel files
A Simple Inventory System Built In Excel
Co-authoring in Office 365. A great feature with an unexpected side-effect
Data Validation in Excel Displaying help out of the way
XML and Excel
Creating webpages with Excel Web App mashups
A Generic Spreadsheet Template
Excel Data Types
Fitting curves to your data using least squares

Defined names

Updated and greatly improved!!! Excel names: A paper about Excel's defined names.
Show a picture from a list of pictures
Charting a mathematical equation using just defined names

Trouble shooting

Error messages at startup due to deleted add-in: Fixing an error about a missing add-in that pops up every time you launch Excel
Corrupt files: Opening corrupt workbook files.
Startup problems: A list of things to do to fix them.
Working with Circular references in Excel
Co-authoring in Office 365. A great feature with an unexpected side-effect

Charting

Charting a mathematical equation using just defined names

Cell formatting

A macro to change the formatting of cells so they only show a predefined number of significant digits
Working with cell styles
Creating a user-friendly Data Validation in Excel Displaying help out of the way

Formulas and functions

The new Excel LAMBDA function; the basics
A small macro to wrap formulas in a range in an error test, from e.g. =Formula to =IF(ISERROR(Formula),"",Formula)
Transpose a table of cells without garbling formulas
Excel data table recipe, batchprocessing: how to calculate your model with many input values
Working with Circular references in Excel

Forms

Using controls on worksheets
Keeping modeless userforms on top of Excel

External Data

How to use Parameters With Web Queries
Importing text files into your worksheets

User Defined functions

An article about Registering a User Defined Function with Excel, showing how to show argument descriptions in the function wizard

Other Applications

Installing An Excel Add-in Using Setup Factory (also shows how Excel add-ins are managed in the registry)

Fun

Wheel of Fortune
Excel challenges

Third party articles

As a special thanks to the people from spreadsheetdaddy.com (for helping me optimize my website for SEO) two links to their great content:

Creating a Gantt chart in Excel

Best Gantt chart software for project management