::: Excel tips 19 :::

::: Data entry solutions with the Template Wizard :::

One of the hardest parts of gathering data from users is creating the right collection application. The data collection front end is often a series of forms in Access or ASP pages. However, the setup and security issues associated with these methods are sometimes too much to deal with for smaller data collection needs.

Excel 97 and 2000 ship with an add-in that can help this situation. The Template Wizard with the Data Tracking add-in will help you create an Excel template that will accept data from users in a familiar environment and then transfer the data to an Access database after it's saved. The wizard will walk you through the steps of defining the tables where the data should be inserted.

This is a great solution for such things as purchase order requests, invoices, or expense forms. Your users can enter their data in Excel, and the data structures set up by the Wizard will place that data into the Access database. The data will be collected into one repository for easy reporting and analysis.

::: Customizing "division by 0" error messages :::

    Working in a complex worksheet, you may have encountered that annoying "#DIV/0!" error when the divisor of your formula is a zero. Using the built-in IF function, you can create your own message for display when you divide by zero:
=IF (DIVISOR = 0,"Your Text", DIVIDED/DIVISOR)
    The IF function evaluates the first parameter (DIVISOR = 0). If it's TRUE, it places the second parameter ("Your Text") in the cell. If it's FALSE, the function places the third parameter (DIVIDED/DIVISOR) in the cell.
    This helps you control the message that's displayed when your formula produces a zero value that is then used to divide another value.

::: Debugging your code :::

    A great way to find errors in your code is to step through it one line at a time. The Step Into button on the Debug toolbar in the VBA Editor lets you do this.
    To start your macro, just click Step Into on the toolbar. The VBA Editor will work through each statement. This allows you to take advantage of some of the other debug tools, such as the Locals window and the Watchwindow.
    When stepping through your code, you can use the Step Out command to run the remaining code as normal. So even if you stepped through half the macro one statement at a time, you can then run the last half normally by clicking Step Out.

Windows XP (P.3)

>>> Stop Error Messages from Displaying on Startup <<<

If you constantly see an error message that you can't get rid offor example, from a piece of software that didn't uninstall properly and continues to give errors on startupyou can disable it from displaying on startup.

• Run the Registry Editor and go to HKEY_LOCAL MACHINE\SYSTEM\CurrentControlSet\Control\Windows. (This key holds a variety of windows system settings, such as the location of your system directory.)
  

Create a new DWORD called NoPopupsOnBoot and give it a value of 1. Exit the Registry and reboot for the setting to take effect. To disable it, either delete the DWORD value or give it a value of 0.

============================

>>> Memory Tweak <<<

• 1. Start Registry Editor (Regedit.exe) and locate the following key in the registry:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager\MemoryManagement\PrefetchParameters
• 2.On the EnablePrefetcher value, change the setting from 3 to 5 (decimal).
• 3. Close the registry editor and restart your computer.

============================

>>> Disabling Recent Documents History <<<

The bad thing about Recent Documents History is that Windows XP has to calculate what should be put there each time you boot Windows, which can slow things down.

• 1. Open the Registry Editor (select Start/Run, type regedit, and click OK).
• 2. Navigate to HKEY_CURRENT_USER\Software\Mcft\Windows\CurrentVersion\Policies\Explorer.
• 3. Create a NoRecentDocsHistory D_WORD key. Double-click the value to open it once it is created.
• 4. Set the Data Value to 1 to enable the restriction.
• 5. Click OK and close the Registry Editor. You'll need to restart the computer for the change to take effect.

Windows XP (P.2)

>>> Disable Unused PORTS <<<

I have just accidentally found out another way to give you an extra boost in windowsXP's boot performance. This is done by disabling your unused devices in Device Manager. for example, if you don't have input devices that are connected to one of your USBs or COM ports, disabling them will give you an extra perfromance boost in booting.

Go to Control Panel > System > Hardware tab > Device manager Disable devices that you don't use for your PC and then restart. See the difference for yourself.

Bonus Tip: Perform a Boot Defragment

There's a simple way to speed up XP startup: make your system do a boot defragment, which will put all the boot files next to one another on your hard disk. When boot files are in close proximity to one another, your system will start faster.On most systems, boot defragment should be enabled by default, but it might not be on yours, or it might have been changed inadvertently.
To make sure that boot defragment is enabled on your system, run the Registry Editor and go to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Dfrg\BootOptimizeFunction
Edit the Enable string value to Y if it is not already set to Y. Exit the Registry and reboot. The next time you reboot, you'll do a boot defragment.