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!)

Here is a tip which may help you when importing text files into Excel. If you frequently import comma delimited files into Excel, you might not realize that you can open the text file, read each line directly, then close the file, as shown here:

Sub ReadTextFile()
'Open text file for reading
Open "Data.prn" For Input As #1
'Process each line in file, until end of file is reached
Do While Not EOF(1)
'Read first record of a line
Input #1, Rec1
'Determine if blank line
If Rec1 = Empty Then
'Blank line - do nothing
Else
'Otherwise, read remaining records on line
Input #1, Rec2, Rec3, Rec4, Rec5, Rec6
'Write records to the Debug window
Debug.Print Rec1, Rec2, Rec3, Rec4, Rec5, Rec6
End If
Loop
'Close text file
Close #1
End Sub

The alternative is to open the CSV file as a workbook, select the active area, copy it to the clipboard, then paste it into where you need it. (We'll soon modify this to write the data directly to cells, rather than just to the debug menu).

Check out these tips that relate primarily to Peachtree Accounting, but may be helpful in other instances also.

Importing a Lotus 1-2-3 spreadsheet into Excel can leave you with dozens or hundreds of comments where formulas didn't convert. An easy way to clear all of these comments at once is to use the following routine:

Sub ClearComments()
For Each c In ActiveSheet.Comments
c.Delete
Next
End Sub