Need to eliminate all of the range names from a workbook? Here's a quick means to do so:

Sub Delete Names()
For Each n In Active Workbook.Names
n.Delete
Next
End Sub

This user defined function will elminate non-printing line-feed characters from a string:

Function NoReturns(oldStr As String)
newStr = ""
For i = 1 To Len(oldStr)
If Asc(Mid(oldStr, i, 1)) <> 10 Then
newStr = newStr & Mid(oldStr, i, 1)
End If
Next
NoReturns = newStr
End Function

In Excel, you cannot poke data from a hidden cell or range of cells. If you try, Excel will sometimes crash with a General Protection Fault.

If a company is password protected, you MUST poke the password first. In Excel, we've found the password must be contained in a worksheet cell, and the cell can not be hidden. The cell can be on a hidden sheet, but not in a hidden column. You can, however, disguise the password by making the font color white, or the same color as your worksheet backround. If you don't properly poke the password, Peachtree will return an error message when you try to request or poke data.

In Excel, we've had difficulty poking text information from a VBA string. Instead, the data must reside in a cell or range of cells. If you're using VB or Delphi, you must use the tab character to delimit fields, such as in distributions.

Be careful when working with hidden sheets within a workbook via VBA. Excel will allow you to copy a hidden sheet out of a workbook, into a new file. You can then, via VBA, save that workbook with only a hidden worksheet. Of course, this is a corrupt workbook that you then can't open.

Tommy Flynn's VBA site offers many VBA routines that automate various tasks in Excel.

If you're distributing a solution to others that involves the Analysis ToolPak, you'll want to add the following routine to your workbook. This checks to see if the add-in is loaded, and if not, loads it for you. Otherwise, the recipient of your spreadsheet will see a host of #NAME errors wherever analysis ToolPak functions, such at =EOMONTH for determining the last day of a month, are used in your workbook.

Sub Auto_Open()
If AddIns ("analysis toolpak").Installed Then
'No action required since add-in already loaded
Else
AddIns ("analysis toolpak"). Installed = True
End If
End Sub

Let's say you're trying to use a VBA routine to input a string that looks like a number into a cell. In some cases, Excel will enter a number, instead of a string. Robert Bruce offers the following work around:

Sub NumberAsString()
Dim objCell As Range
For Each objCell In Selection
objCell.FormulaR1C1= "'" & objCell.Value
Next
End Sub

Use the NumberFormat property when you're trying to use VBA to write a formatted number directly to a cell. For instance, if cell A1 contains the date 12/1/02, formatted as Dec-02, the following routine

x = Range("A1")
Range("B1") = Format(x, "m/yy")

puts Dec-1 (or your default date format) into cell B1. This is because the Format command changes the formatting of the string, not the cell. Thus, the cell uses the default date format. However, the following routine provides the desired effect:

x = Range("A1")
With Range("B1")
.Formula = x
.NumberFormat = "m/yy"
End With

(Thanks to Jan Holmback for providing this insight!)