Here's a VBA tip on how to capture the Double Click event and do something useful with it.

This VBA tip is about how to pause code.

• There are many reasons why you might want to pause code:
  • You might be waiting for a file to arrive and need to pause before rechecking
  • Maybe you've launched a batch file and it needs 3 seconds to run before you can continue

This is the code we'll use for this tip:

This VBA tip will demonstrate how to list all the files in a folder.

A lot of VBA code is written to import files into spreadsheets.

• Today, we'll look at how to import a text file

Here's our text file, and we want to import it onto sheet1 of our spreadsheet starting at Range B7.

From this:

To this:

The * character needs special attention if you want to replace it in your spreadsheet

• Replacing * with something will replace everything (not just the * character)

Here's a screen shot of our data in Excel:

Logging an audit trail of your changes can be useful, and can be done with a small amount of VBA... 

Here's our data:

Here's a screen shot of what we're trying to achieve:

Writing your own Excel function is remarkably easy as this tip will show you.

Often during your working environment, you will need to track the history of a spreadsheet.

• This tip will teach you how to seemlessly create a daily backup of your file
• The file will be date stamped so that you can sort by date and easily find a file you're looking for when you need

Here's a screen shot of our VBA code:

Using VBA, there is a quick and easy way to obtain the file path of a file and return it to a sheet

• Use GetOpenFileName
• This brings up a dialog box that allows you to browse your files and choose a file
• You can then use that file path how you want

Here's a screen shot of our setup in Excel:

Using VBA Events, there's a clever way to add a time stamp when cells change so that you don't need to remember whether you have updated it or not:

Most people know that you can multiply numbers by 100 using paste special, but not many people know that you can use the same methodology to multiply (or divide) formulas by 100 (or any number) as well

Here's what we have: number2 =  number1 +3

This Excel Tip is about how to align shapes:

• Suppose you have added 3 buttons and they're out of line.
• How can you align them ?

Here's a screen shot of what we have, and what we want to have:

Here's how to do it

May 2009 Stats update from ExcelExperts.com

Suppose you have data in rows, and you want to convert it to columns, how can you do that in Excel ?

• Use the TRANSPOSE function
• TRANSPOSE will flip data 90 degrees to convert row data to column data and vice versa.

Here's a screen shot of our data in Excel:

There are certain message boxes that will popup by default when you're running code.

In VBA you can use events to capture certain things that happen on a workbook

Finding broken links can be tricky if you don't know how they're caused.

In this Excel Tip, I will show you how to recreate a broken link, and give you some insight on how to fix them.

The most common type of broken links are range names..

To recreate a broken link, follow these steps:

This tip is about removing non-numeric characters from a cell's value:

Here's how to select and copy all the text in a cell:

This is a handy tip that a lot of people don't know:

• You can actually format the text within a cell

Here's a screen shot of what it looks like:

Here's how to do it:

You often need to create a copy of the sheet you're currently working on

• One way is to saveAs another file name, then delete the extra sheets... but there is a better way

Steps:

Adding a link to an email address is simple in Excel:

• All you have to do is to type in the email address into the cell, and it automatically adds a hyperlink that will launch your mail program and compose a mail addressed to the address you typed in

You can then right click and edit the hyperlink, adding a subject, or changing the text that is displayed whilst keeping the link the same.

This tip is something you would never discover on your own, and can be a massive annoyance reducer:

• Pressing F2 toggles between scrolling into the formula and scrolling on the sheet

It's not immdiately obvious what I am talking about, so here's an example.

Here's our data in Excel:

Here's a tip on how to find the Nth largest value in a range, and use it to sort a list of numbers:

Excel files seem to just get bigger and bigger often balooning in size at the blink of an eye, but how do you find out where all the extra file size is coming from ?

• There is a way to separate out each object in your file, and examine it's contribution to the overall file size

Here's a screen shot of our innocent-looking Excel file:

Today's tip is a simple one on how to move a chart

• By default, Excel adds a chart to the current worksheet
• You can easily move a chart off a sheet and onto it's own chart worksheet

Here's a screen shot of how our chart looks in Excel:

You want to use a special character in Excel, but you don't know how to find it:

There is a way to get some simple information quickly and without having to write formulae :

• Use the statusbar (the thing at the bottom)

Here's a screen shot of our data in Excel:

When you add a chart, it's not obvious at all how to construct it so that new additions to your data are automatically added to your graph

Today's tip is one of the most useful tips I will ever write.