RSS

Pages about Excel VBA on this site - sitemap

On this page

Cells - test content

Conditional formatting and
layout using VBA

Worksheet functions

Arrays and ranges

Userforms, dialogues
and control elements

Dates and time

Menus and toolbars

Navigation and hyperlinks

Text and csv files

Collections and classes

Charts & diagrams

Miscellaneous

Here you find short descriptions of the Excel VBA pages on this site. I have tried to group them by topics, but in some cases that is difficult, because the pages cover several topics. However I hope it will give some order to chaos.

Tests and conditions

Cells - test or check content. You often need to test or check cell content. Is the cell empty, is it a number, a text, a formula, an error, a date - or does it have a comment or conditional formatting?

If Then conditions. A beginner's page on how to test conditions or constraints with "If Then".

Select Case. If you have several conditions to test, Select Case is often better and less confusing than "If Then".

Conditional formatting and layout using VBA

Animated charts. How to animate Excel charts with VBA's DoEvents function and the Windows function Sleep, that can pause code execution. See video and download spreadsheet.

Blinking cells in Excel and the OnTime function. How to make one or more cells blink automatically, if the cell content doesn't meet a condition, and how to stop the blink, if the cell value is changed to something okay.

Conditional formatting using macros. Using VBA instead of Excel's built in function you can easily count the cells that are changed (e.g. get a red background colour).

Hide or show rows and columns. How to hide or show rows and columns conditionally with Excel VBA macros.

Worksheet functions and addins

Make your own worksheet functions and addins. How to make your own worksheet functions and save them as an addin, so you can use them in all your workbooks.

SUMIF function ignoring hidden cells. Excel's SUMIF function doesn't exclude hidden cells. This function does. An example on how to make your own worksheet functions.

Arrays and ranges

The Range object. A Range represents any number of cells in a worksheet. It is handy and flexible to use when programming. A range can be changed dynamically, and it is far more flexible than to operate with fixed cell addresses or "selected" cells.

Arrays. An array is a table in the computer's memory. It is very fast and can have several dimensions. About the array types and how to use them.

Arrays and ranges. If you work with big tables/ranges, you can usually speed up the code a lot, if you copy the range to an array. Once you have finished operating on the array, you copy the array to the spreadsheet in one lightning fast operation.

Arrays and range formulas. Copying the values from a range to an array can speed up things (se above), but you can also copy the formulas instead of the values, and that can be very useful. See how.

Copy formulas to another workbook without links. How to use a macro to copy formulas from one workbook to another and avoid references to the workbook, you copy from.

Copy rows by using criteria. An example on how to copy rows from one table to another in a different workbook, if a cell value matches e.g. a customer ID, a name or a pattern with wildcards. It uses the Like operator to compare.

Delete rows by using criteria. How to delete rows in a table if values in a user selected column meets a user defined criterion - e.g. < 300. The macros use ranges, arrays, userforms, a listbox, a boolean function and more.

Using worksheet functions on arrays. You can use worksheet functions on arrays, and that is smart indeed if you use arrays for fast extraction of data that meets certain criteria.

Userforms, dialogues and control elements

Userforms and inputboxes. An introduction to Excel VBA's built-in inputbox and the basics of how to make and handle your own userforms.

Listboxes on userforms. This page shows how to fill and handle listboxes on your own userforms. It also shows how to preselect items on the list.

Dependent ComboBoxes and arrays in class collection. How the selection in one ComboBox can control the list in another. The example uses a dynamic number of arrays by storing them in a class collection. Not as complicated as it may sound!

TextBoxes on userforms. How to check and validate user input in a textbox, and how to do it the smart way using a class module, if the text boxes are alike.

Loop through controls on a Userform. If there are many controls on a Userform, checking can be quite a job - especially if you check the controls one by one. Quite often it is much easier to loop through the controls and take action depending of the control type and the user's input.

Date format in an Excel VBA ComboBox or ListBox. Dates in a ComboBox or ListBox can be a pain, because VBA changes the format, and that may not be the way you want the dates displayed. Here are a few tips on how you can control the format.

Dates and time

Calendar and date picker on userform. Pure VBA and no ActiveX. The code uses several date functions and there is also a simple class to control events by declaring Public WithEvents.

Make calendars in Excel. Easter days are calculated, and the user can define up to 30 holidays/anniversaries. Different layouts and class collections made easy. A calendar is put in a new workbook.

Calculating time with VBA. Examples on how to calculate time with VBA.

Calculate stop time, runtime, meantime between stops etc. If you can get a log file with times into Excel, you can tailor macros to measure process efficiency.

Menus and toolbars

Make your own toolbar in Excel with VBA macros. It is easy to make your own toolbars with a few macros. You control the look and assign a macro or a built-in Excel function to each button.

How to make your own menus in Excel. It is pretty easy to make menus tailored to your needs.

How to replace Excel's standard menu. How to replace Excel's standard menu with your own. Doesn't work in Excel 2007 or higher, where the menu is added to Excel's standard menu.

Make your own popup menu. With VBA you can easily make a popup menu (shortcut menu) that pops up, when you right-click, and replaces Excel's built-in menu for right-click. Use it to call your own macros or worksheet functions.

Navigation and hyperlinks

Find next empty cell. Functions for finding the next empty cell in a column or row.

Double-click macro as alternative to hyperlinks in Excel. This page describes how a double-click can send you to another cell with identical content or activate/open another sheet or workbook. In short: An alternative to hyperlinks.

Find cells and insert hyperlinks. How a macro can find cells with a certain text or value and then insert hyperlinks.

Sort hyperlinks in Excel with VBA. There is a known bug in Excel that makes errors in hyperlinks, if you sort the cells. Fortunately there is a workaround using VBA macros.

Text and csv files

How to import a text file with VBA. How to import a semicolon delimited text file to a worksheet without using Excel's built-in import function.

Fast text file import with Excel VBA. How to automate the import of text or csv files without displaying the wizard.

Import csv files. Some examples on how to import csv files using VBA, and an example on how to parse the file using code insted of using Excel's built-in import functions.

Split a text file into smaller files. How VBA can split a text file into smaller files with a user defined number of max lines/rows.

Write and save a text file. This example shows, how you can easily write a semicolon delimited text file and save it to disk using VBA macros.

How to check if a directory/folder exists. Two examples on how to do this using VBA.

Collections and classes

How to make your own collections. There are many built-in collections in Excel (and other Office programs), and it is easy to make your own.

Classes and class collections. With your own classes and class collections VBA gets real power, and it is not that complicated.

Process and mass balance. Using a class collection to simulate an industrial process, and iteration to achieve balance between input and output in what would otherwise be circular references.

For Each Next in own class collections. 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.

Charts & diagrams

Histogram in Excel with VBA-macros. A histogram is a graphical representation of data distribution. This page shows how to make a histogram with a bit of VBA code

Pareto charts fast and easy by using VBA macros. A Pareto chart is a sorted bar chart that also displays the accumulated percent.

Miscellaneous

The VBA homepage. The front page for these Excel VBA pages.

Excel spreadsheets with VBA macro examples. Downloads.

Merge or combine data and tables. Examples on how to merge and combine data and tables (lists) using criteria.

Faster code in Excel VBA - speed up your macros. When your program starts to grow, efficient code and speed becomes an isssue. Tips on speed.

Ticket or number control with VBA. A spreadsheet with macros for ticket control at a music festival. It checks if a number is in a series of numbers.

Random numbers and drawing lots. How to generate random numbers using Excel VBA.

Using Excel's Solver with VBA macros. Explains how to control Solver with macros. Sample spreadsheet for download.

Round to nearest 100, 1000 etc. A function where 2 returns 3, 33 returns 40, 177 returns 200, 2366 returns 3000 etc.