Excel VBA macros - tips, tricks and examples
These are my pages about programming in Excel using VBA macros (Visual Basic for Applications).
VBA is a powerful tool that enables you to do things automatically, that would otherwise be very time consuming and prone to errors. You can even build advanced applications for other users, if you know how.
This is not a tutorial (plenty of those). I just share my experience with code examples that have been useful to me. Other examples are the fruits of reader requests.
There isn't much for beginners. Most of the pages target the user with some experience, but who may wonder how to solve a particular problem, or how to make the code more efficient.
That said, there can be ample inspiration, if you are playing with Excel's macro recorder. The easiest way to find out if these pages offer an answer to a specific question may be to use the search function at the top.
On most pages there is code that you can highlight with the mouse, copy and paste into a VBA module. There are also some spreadsheets with macros for download.
All examples were made in either Excel 2000 or 2003. If they don't work in other versions, please blame Microsoft and not me.
I work as a process engineer at a cement plant, and most of my experience with VBA comes from using it on the job - mostly automated import and "crunching" of process data.
More advanced is process modelling and using the Excel Solver for raw mix calculations to get the right chemical composition of the cement clinker.
Feel free to send me a mail, if you have questions or want to suggest new examples.
There is a limit to how much time, I can spend on this, so please understand that I cannot promise an answer or a new webpage; but I read all mails!
Excel 2016 introduced a new bug: When you open (some) spreadsheets with macros made with Excel 2003 or older, you get an error message, "Automation error".
The macros work just fine, but the message is annoying. To get rid of it, just save in the new format as a macro enabled workbook (*.xlsm).
Windows provoked crash of Excel 2003
The other day I updated Windows 10 with the April 2018 release (build 1803), and it caused my Excel 2003 to crash. I saw no other way out than to cancel the uodate and revert to the old installation.
If anybody out there has a better solution, I would be grateful to know.
18th February 2018. A new page about appending text to a text file. The example macro logs date, time and username in a text file, when a workbook is opened, and if the username is different from whatever.
I used the trick at work to prove that a certain workbook wasn't accessed by anybody but myself, so maintaining it was a waste of time.
21. August 2017. I have updated the page about making a calendar in Excel.
The new macros can make different layouts and calculate, when it is Easter. There are 10 holidays with fixed dates (e.g. Christmas) and the user can define up to 30 holidays/anniversaries. The code looks at the system settings to get the right date format etc. You can download a workbook with it all.
11. June 2017. Conditional formatting using macros instead of Excel's built in function. Using a macro you can easily count the cells that are changed (e.g. get a red background colour).
30. October 2016. How to make a calendar in Excel. Macro to make a new workbook with a calendar sheet for every month. Good for keeping track of e.g. hours spent on different tasks, employees or whatever.
24. January 2016. A new page about dependent ComboBoxes, and arrays in a class collection. How the selection in one ComboBox can control the list in another.
Uses a dynamic number of arrays stored in a class collection. Not as complicated as it may sound! Sample workbook for download.
16. October 2015. A new page: Copy formulas to another workbook without links. If you copy formulas to another workbook, they will get a reference to the original workbook.
The reference can be removed by search and replace, but if it is something you do frequently, you can use a macro instead.
10. October 2015. A new page: Copy rows by using criteria. A frequently asked question is how to copy rows from one table to another (maybe in a different workbook) by using criteria such as customer ID, names or patterns with wildcards.
Here is an example doing just that, and to compare it uses the Like operator that allows patterns with wildcards.
22. August 2015. A new page: Calendar and date picker on userform. Different versions of MS Office use different ActiveX for calendars, so this example is in VBA only without ActiveX.
Several examples on the use of date functions and also a simple class module to handle events by declaring "Public WithEvents". Workbook with full code for download.
9th August 2015. Two new pages about worksheet functions written in VBA: Make your own worksheet functions and addins and SUMIF ignoring hidden cells.
A reader asked me how to make Excel's SUMIF function ignore hidden cells. That got me started with tailored worksheet functions.
22. July 2015. A new page about using worksheet functions on arrays. Using arrays when extracting data that meets certain criteria is very, very fast, and afterwards you can use Excel worksheet functions to help analyze the extracted data.
Workbooks with examples for download. The macros in one workbook work with the data in another workbook.
6th June 2015. A new page about deleting rows in a table by using criteria. How VBA macros can delete rows in a table, if values in a column (selected by the user) meets a user defined criteria - e.g. if values are lower than 300.
The macros use ranges, arrays, loops, a boolean function, userforms (also modeless), a listbox - and more. You can download a zip-compressed spreadsheet with the example.
21st February 2015. A new page about calculating time. Sample macro to calculate stops, starts, stop time, runtime, meantime between stops and more.
The essential thing is to calculate time, and that can be used for other purposes than stop and start statistics. Besides the VBA-function DateDiff the macro uses loops, ranges, arrays and a collection. Sample spreadsheet for download.
30th November 2014. A new page about Animated charts. Making animations in Excel isn't that complicated. See how to.
You can watch a video with examples, and you can download the spreadsheet used in the video.
16th November 2014. I uploaded a new page: Round to nearest hundred, thousand etc.
The other day I needed a function that could calculate a value for automatic scaling of a chart's Y axis. If the highest input value was 2, it should return 3, 11 should return 20, 72 should return 80, 117 should return 200, 1399 should return 2000 and so on.
The function can easily be modified to round up or down.
4th October 2014. A Pareto chart or diagram is a bar chart with the highest bars to the left and a curve with accumulated percent. It is used to visualise the most important factors and is a Lean Six Sigma darling.
It isn't difficult to make manually, but it takes time, and using VBA macros is much faster. See the code or download a zip compressed workbook. The chart values can be in the same workbook as the macros or another.
28th August 2014. Fixed a bug in the sample spreadsheet for histograms - some times you couldn't select the sheet with source data. The code on the webpage has been fixed too.
25th August 2014. The page about making histograms in Excel using VBA has been updated. There are now two examples.
The first one makes a histogram (columns) with a bell shaped curve with normal distribution based on the data set's standard deviation and average value.
The other example shows how to make a histogram with a user defined number of columns/intervals. You can download a workbook with the examples.
19th March 2014. I have uploaded a page describing how to use Excel's Solver with VBA. You can also download a sample spreadsheet.
The spreadsheet shows how you can make a user controlled application for Solver problems. In this case the objective is to make a mix of different raw materials to the right chemical composition at the lowest cost.
1st March 2014. Class collections are great for structured programming, but you cannot use the fast For Each Next loop to loop through your own class collections. That is: Unless you know the trick.
On this page I show how: For Each Next in own class collections
1st February 2014. A new page, Split a text file into smaller files, shows how to "divide" a text or csv file into smaller files with a user defined number of lines (rows in Excel).
I wanted to import server log files for my website into Excel for analysis, but I use Excel 2003 with "only" 65536 rows, so I wrote this macro to split big text or csv files into smaller ones.
30th November 2013. The page about ListBoxes has been updated with examples on how to use arrays (1 or more columns) as list source. You simply set the list = the array.
24th November 2013. Importing csv-files to Excel using VBA is straightforward, however if the values are deparated by semicolon, it can be tricky, unless you know how to make VBA behave.
On Import csv-files using VBA macros I show how, and there is also a macro alternative to Excel's built-in import functions.
9th November 2013. Added a new page describing how you can use double-click to navigate between cells, sheets and workbooks instead of using hyperlinks.
The trick is to use a worksheet's or workbook's BeforeDoubleclick event and add a bit of code. You can also download a zipped file with examples.
4th November 2014. I have now made an RSS feed (see page top). Feed it to your favourite feed reader and subscribe to news and updates. Soon to come is a page about an alternative to hyperlinks for navigating in or between spreadsheets.
17th August 2013. I have updated the page about ListBoxes showing how to preselect items on a list.
There is also a new page, Arrays and range formulas, on how to copy a range with cell formulas and references to an array.
If you are using large ranges, your code can get considerably faster by manipulating the data in an array and then insert it as a table in one swift operation.
28th July 2013 with Merge or combine data and tables. A reader asked for help to combine 2 tables in 2 different workbooks using company names as "keys".
That served as inspiration for this page, where I show examples on how to merge or combine data, lists or tables. They may be in one or different workbooks, and the output may be written to a new workbook or not.
The examples use ranges, arrays, collections, loops and the worksheet function "CountIf". Zip-compressed workbooks with the examples are available for download.
13th July 2013 with Fast text file import. I import a lot of text files to Excel, and I got sick and tired of using the import wizard setting the same parameters over and over again.
So I wrote a macro to speed things up. Now I just click a button, select the text file to import, and in the wink of an eye the data has been imported to a new spreadsheet. Read how and/or download a spreadsheet example.
10th March 2013 the English version of these pages came to life. Their Danish counterparts have been around for some time, and I finally decided to translate.