Make Excel VBA Pause

Sometimes when running a macro you need to make sure Excel has had time to do something before progressing.

This is typically in large models were it can take time (a few seconds) to do a specific task eg removing a filter or updating an external data source.

You can pause a macro to allow Excel to do something by using the Wait command.

Application.Wait (Now + TimeValue("0:00:02"))

The above code will pause the macro for 2 seconds.

VBA to Clear a Filter

Using Excel’s built-in filtering can speed up your VBA code.

It is important if you are applying filters that you clear any existing filters before you apply a new filter. Otherwise the existing filters will usually affect a new filter you apply.

The line of code below will remove filters on Sheet1 (Sheet1 is the sheet code name that you see on the left side of the VBA screen – it may not be the sheet tab name).

If Sheet1.FilterMode Then Sheet1.ShowAllData

The .FilterMode property is True if a filter is in place on the sheet and False if not.

The .ShowAllData method will return an error if no filter is in place – hence the use of the If statement.

Data Validation Search – Free Add-in

Jon Acampora
2017-04-27

A common Excel request is to be able to type characters and see the in-cell data validation drop down list reduce, based on what you have typed.

This free add-in from Jon Acampora (Excel MVP) does just that. He has recently added a few new features.

If you have long drop down lists this add-in is a great addition to Excel.

This link has a video of how it works and the new features like Auto Open when a data validation list cell is selected.

Export a sheet as a PDF

It takes a few clicks but it is possible

I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)

Excel Cell Comments and Documentation

Macro to Create a Comment Report for a File

I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.

Ron DeBruin

Ron DeBruin is an Excel MVP and has been for a long time.

His site has lots of excellent macro code and free Add-ins.

He has tips and macro examples for the Mac as well as Windows.

I have used his pdf creator code to create pdfs of certain sheets in a file.

If you want to learn about modifying the ribbon he has some great tips and examples.

He also has code examples for amending Excel’s pop-up menus.