::: 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.