Excel Articles

Excel Tips & VBA Tricks

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

Excel Office Script & Automation

• 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)

Advanced VBA Techniques & Add-in Development

• Storing meta information about Excel cells. While I was designing my Spill Manager for Excel add-in I needed a way for the add-in to quickly find spill ranges and to remember which spill ranges the add-in should manage (which spills it needs to watch for size changes). This artcile shows how that was done using dedicated range names.
• 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

Miscellaneous Excel Tips & Tools

• The history of my free Name Manager add-in
• 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

Working with Excel Defined Names

• The history of my free Name Manager add-in
• 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

Excel Troubleshooting & Error Fixes

• 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

Excel Charting Techniques & Visualizations

• Charting a mathematical equation using just defined names

Excel Cell Formatting & Styles

• 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

Excel Formulas, Functions & Lambda Examples

• 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

Excel Forms & Worksheet Controls

• Using controls on worksheets
• Keeping modeless userforms on top of Excel

Importing & Connecting External Data in Excel

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

Creating & Registering Excel UDFs (User Defined Functions)

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

Excel Add-in Deployment & Integration Tools

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

Excel Challenges & Interactive Projects

• Wheel of Fortune
• Excel challenges

Recommended Excel Resources from Third Parties

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