Your VBA/Automation Headquarters!

 

 

 

 

 
 

Home Access Excel FrontPage Outlook PowerPoint Word Office VBA VBA Samples Newsletter Free Downloads

 

A truly global firm, The Office Experts provides services to clients all over the world, and hires consultants from all over the world.

Not only do we provide automation, we'll fix, update, or convert your current automated applications.

 

Services Store Contact Us
 

Excel

Most people realize that Excel can be automated. There are many resources all over the internet that provide information on Excel VBA. You'll quickly find the popular names. Meanwhile, we offer some general information and troubleshooting.

Excel Troubleshooting

Startup Folders

File Layout for Databases

Common Excel Issues

File Corruption

The Dreaded Vlookup

Dropdown Lists (Data Validation)

 

Excel Troubleshooting

Excel crashes when you open it, or when you try to copy cells, or when you try to save the file? The first thing you will want to do toward troubleshooting your Excel program is read about Startup Folders. Also read about File Corruption issues if the program only crashes when using one or two files that you use.

We assume that you keep your PC clean of unnecessary temporary files (all kinds) and that the PC and the hard drive are healthy. To ensure this, check out the Clean Your PC instructions we provide.

Next, if you can get Excel to open, try running Detect and Repair from the Help dropdown menu in either Excel 2000 or Excel XP.

If none of the above works for you, continue reading.

Edit the Registry

Many people will tell you not to go into the registry without adult supervision. And that's true. However, if you follow these steps EXACTLY, you will not only be fixing your registry, but backing it up at the same time.

You must first CLOSE Excel.

Start-->Run and type:

regedit

Hit your Enter key. As you might browse folders in Windows Explorer, browse to the appropriate path:

For Excel 97:
hkey_current_user\software\microsoft\office\8.0\excel

For Excel 2000:
hkey_current_user\software\microsoft\office\9.0\excel

For Excel XP:
hkey_current_user\software\microsoft\office\10.0\excel

If you are unable to find this folder, or do not understand how to find it, then you should stop here and get support over the phone or in person.

Once you have found the path, right click the Excel folder, hit Rename and rename it to OldExcel. Hit Enter, and then exit the Registry Editor; and then relaunch Excel. If this still has not resolved the problem, keep reading.

Uninstall, Erase, and Reinstall Office

Uninstall using Add/Remove programs in the Control Panel. Start-->Settings-->Control panel. Select Office, and then choose Remove.

Run Eraser 97 or 2000, as appropriate. To-date, there is no Eraser program for Office XP. Instructions and downloads can be found at:

Office 97:
http://support.microsoft.com/support/kb/articles/Q176/8/23.ASP

Office 2000:
http://support.microsoft.com/support/kb/articles/Q219/4/23.ASP

Prior to reinstalling any program, make sure that there are no programs running in the background that could interfere with the installation. Hit Ctrl-Alt-Del to bring up your task list. One by one, end task on all items EXCEPT Explorer and Systray.

Reinstall the program using the disks. If none of these options have helped, there is likely some other software on your system that is interfering with the operation. Some anti-virus programs have settings that affect the operation of programs.

Startup Folders

It is very important to understand the XLSTART folder. One day, and you may have heard of this before, you open Excel and it attempts to open a whole bunch of files--and not just Excel files either. Why does this happen?

When you launch Excel, it opens all of the files in two different folders. It first looks in the XLSTART folder on your hard drive. It also looks in the folder you designate under Tool-Options, General tab as the Alternate startup folder. Generally, people confuse the Alternate startup folder as their Default file location. Unfortunately, if you enter an Alternate startup folder of C:\My Documents, Excel launches and attempts to open all of the files in there, including non-Excel files. If you have hundreds of files in there, it could take some time to get past all of the errors you'll receive.

Why does it open these files? Many people use Excel VBA and/or recorded macros in their daily routines. A file named Personal.xls or any other file, can be created to store these macros and make them available for use with any file. To make these macros available to all files, they must be loaded--or opened when Excel is launched. See more on macros under Excel VBA.

File Layout for Databases

Most people use Excel for the purpose of having a database of information or for financial purposes. We recently saw a document from a very large corporation that contained financial reports, one on each page. What a mess! How much easier it would be to do all of your calculations, and data entry on one sheet. Then, create a Word document and copy the cells from Excel and EditàPaste specialàPaste link so that your reports are updated when you change your Excel data.

The absolute best file layout for using Excel and its features is as follows:

Column headings (call them field names if you like) are in Row 1

First record is in Row 2

No completely blank rows in your data

No completely blank columns in your data

This layout allows you to use the features of Excel as they were intended to be used. Many people never discover the features because their files are not set up in this manner, the feature doesn't work the first time they try it, and they forget about it. Don't stop trying! You'll find that the things you're doing manually every day might easily be accomplished with one of Excel's features. If you're unsure, ask at the Forum!

No-Nos and Workarounds

These are the rules to follow to avoid problems with Excel's features:

Use more than one row for column headings: To insert a "paragraph return" within a cell, use Alt-Enter on your keyboard. You may need to adjust row height to accommodate the text.

Report title on the worksheet instead of in the header. If you must have the title on your worksheet, increase the height of Row 1 and place the title in a textbox using the Drawing toolbar. Place the textbox above your column headings in row 1.

Place headings at the top of columns, not in rows. For instance, A2 has "Branch 1", and then B2 through B7 have address and contact information about Branch 1. A8 has "Branch 2", and then B8 through B13 have address and contact information about Branch 2. This is a bad setup for use with Excel. Though it might print out nice, you will have a hard time getting, for instance, a branch phone list. Instead, put each item of information under its own column headings. You can then sort by any column you wish, and using Mail Merge with Word will allow you to display this information in any manner that you want.

Do not use Excel for documents that contain a lot of text. Use Word tables instead. They work very similar to Excel columns, but allow the freedom of entering and viewing a lot more text. If you are convinced that you need Excel because you need to total a column, think again. Calculations can also be done with Word, especially simple ones. Decide what is the priority for the document: having it look nice and text that is easy to manipulate versus extensive calculations.

Common Excel Issues

Cannot find the file . . .

Seems it's becoming more and more popular.  There are other causes for this, but go to Tools-Options, General tab and make sure Ignore other applications is NOT checked.

Excel opens a bunch of files when I launch it . . .

Go to the top and read about the XLSTART folder under Startup Folders

Cursor selects cells when I'm not holding my mouse button . . .

It could be your wheel mouse. Just click on the wheel. Otherwise, you need to Clean your PC.

Cannot shift non-blank cells off of the worksheet error . . .
-or-
Excel prints extra blank pages . . .

This occurs when Excel believes that all of your rows or columns are filled. If you constantly use a file over and over, then delete the contents of cells by hitting your delete key, eventually you'll get this error message. You may have deleted the cell's contents, but the cells still contain formatting information. This can also cause file bloating.

Go to your first completely blank column to the right of your data. Hold your Shift and Ctrl keys and hit your right arrow key until it hits column IV. From the menu hit EditàClearàAll. Go to the first completely blank row at the bottom of your data. Hold your Shift and Ctrl keys and hit your down arrow key until it his row 65536. Hit EditàClearàAll. Save the file now, because it will not recognize the clearing of those cells until you do.

Too many formats error . . .

Formatting a group of cells at one time creates one format. So it makes sense to format as many cells that have the same format as you can at one time. For instance, select any columns that contain dates (hold the ctrl key to select noncontiguous ranges) and format them as dates. Do the same with numbers that are formatted alike. As often as possible, do formatting on entire columns and/or rows; and select as many as you reasonably can at one time, then do the cell formatting. For instance, your columns A, D, and F may all contain currency. Select column A by clicking on the A. Hold your ctrl key and click on D and F. Then format the cells. Cell formatting includes font name and type, size, borders...everything.

File Corruption

Our belief that a file is corrupt isn't always true. Sometimes, we just have a dirty file: dusty in the corners, cobwebs on the ceilings, and the like. Here are the steps you can take to keep file corruption from occurring and, if you can still open it, get the corruption out.

Precautions

Keep the file clean by taking these steps on each worksheet. Go to your first completely blank column to the right of your data. Hold your Shift and Ctrl keys and hit your right arrow key until it hits column IV. From the menu hit Edit-Clear-All. Go to the first completely row at the bottom of your data. Hold your Shift and Ctrl keys and hit your down arrow key until it his row 65536. Hit Edit-Clear-All. Save the file now, because it will not recognize the clearing of those cells until you do.

Do not copy a sheet and then make a copy of the copy, and a copy of the copy of the copy. Instead, make a boilerplate sheet and always copy that one sheet. We have not yet determined if this is an issue only in Excel 97, so better to be safe than sorry. Otherwise, you may get the Sheet111111111111111111 error. I've looked for the fix recently at Technet and have been unable to locate it. If you've already got this error, and if it's possible to open it, save the file down to Excel 5.0, close it, reopen, and save as your current version again. Or open it in Excel XP, which often fixes the document for you--even after you have been unable to open it in lesser versions.

If you're getting illegal operations or other errors in a specific file, copy the worksheets into a new workbook. Right-click any sheet tab, choose select all sheets (that is, if you have more than one sheet). Right-click a sheet tab again, and hit Move or Copy. Choose New book from the dropdown, checkmark the Make a copy box. Hit Ok. If your file is excessively large, you may need to copy one sheet at a time using New book the first time, save the new book, then copy the remaining sheets to the newly saved file. You may find that one sheet in the workbook may need to be recreated.

Creating drawing objects: Create these in another file. When your drawing is complete, group all of the drawing objects and paste the one object into your *real* file. Having many drawing objects pieces floating around in MS Office files seems to be a real source for corruption.

Inserting a mixture of MS Office drawings, graphics, and other objects all in one file appears to cause corruption.

After inserting graphics, save your file and check its size. Save your file to another name. Now select each graphic, cut it, and hit Edit-Paste special-As a picture. See if this reduces file size, while retaining good resolution on your graphics. This is almost always helpful in reducing the size of files, but on rare occasion has actually increased file size.

With files that are used often, possibly by many users, and have been around for years, you may want to *standardize* the formatting. Formatting one cell at a time creates one format that is saved in the file. Formatting a group of cells at one time creates one format. So it makes sense to format as many cells that have the same format as you can at one time. For instance, select any columns that contain dates (hold the ctrl key to select noncontiguous ranges) and format them as dates. Do the same with numbers that are formatted alike. As often as possible, do formatting on entire columns and/or rows; and select as many as you reasonably can at one time, then do the cell formatting. For instance, your columns A, D, and F may all contain currency. Select column A by clicking on the A. Hold your ctrl key and click on D and F. Then format the cells. Cell formatting includes font name and type, size, borders . . . everything.

The Dreaded Vlookup

This image breaks down the Vlookup. If a real file will help you to understand better, go to the Free Downloads page for a free download that explains the *dreaded* Vlookup formula.

Dropdown Lists

This is so easy. This method works whether your data list is on the same worksheet or not; but you must have your data in the same workbook.

Create your list of choices. I like to have the very first option be Select One or Choose One or Choose from Dropdown--we'll use "Select One" for our example. Then, you can put conditional formatting on the cell so if the cell is equal to "Select One", the fill is Yellow, which lets your user know that a selection needs to be made in that cell. Once choice each in cells going down a column or across a row. For our example, we'll put out list on Sheet2. We can put a bunch of lists in different columns here. You might want to give your lists headings for your reference.

Now, select the cells that contain your list--do not select the heading if you've given it one. From the menu, hit Insert-Name-Define. Give it a one-word name (no spaces allowed)--for our example, we'll name it MyList. (You could instead type that name into the box above cell A1, as shown in the picture.) 

Next, click on the cell where you want your dropdown to appear. From the menu, choose Data-Validation. Choose List in the Allow box. In the Source box, type "=MyList". If you forget the = sign, it will NOT work.

Hit OK. Now, when you click on that cell, you'll get a dropdown from which to choose.

Tips:

  1. Select the cell that contains the dropdown, and hit Format-Conditional formatting. Set it so that if your cell value is equal to the first item in your list, that the pattern fill should be yellow.

That will bring attention to the cell and prompt your users to select an item:

  1. In our example, we placed all of our dropdown lists data on sheet2. We can now hide sheet2 so that nobody sees our ugly data lists.

Access ] [ Excel ] FrontPage ] Outlook ] PowerPoint ] Word ] Office VBA ] VBA Samples ] Newsletter ] Free Downloads ]

Copyright© 2003 - 2010 The Office Experts and Tickling Keys, Inc.

Microsoft Office®, Access®, Excel®, FrontPage®, Outlook®, PowerPoint®,
Visio®, and Word®, are registered trademarks of the Microsoft Corporation