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.
- Troubleshooting 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 Excel LAMBDA function; the basics
- The Excel LAMBDA function; Examples
- The Excel Lambda function; converting VBA UDFs to Lambda functions
- 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
External Data
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
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: