Data Input for Datasets in Excel with VBA

While Excel provides the means of entering data directly into datasets, you can build important controls and features by processing data through a VBA script. This post will provide an overview of a user-friendly data input form to prepare data for processing and entry in VBA.

Difficulty Intermediate
Time Est. 45-60 Minutes
VBA/Macros Yes
Template Files None
Solution Files 2016-10-16-user-form-data-input-solution

Spreadsheet Design Exercise 4

This exercise within the Spreadsheet Design series begins the focus on Data Management.

As previously explained within the Data Management summary, Excel should not be used to create data that already exists in other systems — these data should be imported into a spreadsheet for analysis. However, there is often still a need to input data which either does not exist in another system, is rooted in an analysis, or is created with projected assumptions.

As has been discussed in previous posts, data management tasks (the user) and the dataset (the data) should be separated. The user should never input data directly to a dataset. This can lead to a number of complications, as the user may inadvertently change other data, input incorrectly formatted data, or fail to properly run calculations. For these reasons (and others), it’s generally a bad idea to keep datasets open and unsecure.

A data input form is the first step in assuring that data is well managed and protected. Through a data input form, the spreadsheet user will have a friendly interface that walks him/her through the data input process. Additionally, validation features can ensure that data is properly formatted, and any required calculations can be done on the back-end without the user’s input.

In this exercise, we will create a small spreadsheet application that records stock purchases.

Step 1: Create a workbook with three tabs.

The tab names should be as follows: Interface, Data, Operations.

Step 2: Create a simple data input form.

On the Interface tab create a simple data input form with the following form labels: Stock Symbol, Action, Data, Amount, Shares. Also add a shape with the text “Submit” and include a label for Status a few cells below. Finally, it’s a good idea to add basic formatting hints, as this example shows next to the input fields in column D.

Simple Excel data input form.
Simple Excel data input form.

Step 3: Name the form field ranges.

Name each of the input field ranges, starting with “Input_” followed by the input label with underscores separating each word: Input_Stock_Symbol, Input_Action, Input_Date, Input_Amount, Input_Shares, Input_Commission. These names should be placed on the cells where users input data (column C, in our example), not on the form labels. Finally, add a named range to the right of the Status label: Output_Status

Add named ranges to each field.
Add named ranges to each field.

Step 4: Specify data headers.

Similar to step 2, but on the “Data” tab, starting with range A1 and moving right, enter the following in both the formula bar and range name: Data_Purchase_ID, Data_Stock_Symbol, Data_Action, Data_Date, Data_Amount, Data_Shares, Data_Commission, Data_Total_Cost.

Specify header named for the data input script.

Step 5: Setup spreadsheet operations.

This entry form will require two back-end operations: (1) a counter to provide each entry with a unique ID and (2) a data list for the action field, as that field will only have two states: Buy OR Sell.

Add named ranges to operation sheet elements.
Add named ranges to operation sheet elements.

For the operations tab, row 1 does not contain the actual named ranges, simply a hard-coded label. The name ranges are assigned to the contents below row 1. This helps identify named ranges, especially for more complex Excel applications which may have many back-end operations.

Step 6: Add data validation to “Action” field.

There are a number of ways to validate data in Excel, both with VBA and without VBA. While most of this form will be validated in VBA, there is one field we can validate using the Data Validation tool. Given that the action field will only have two options: Buy OR Sell, it makes sense to provide the user with a dropdown list. Dropdown lists require minimal system resources and act as a good UI feature for the user.

Add a dropdown list to the Action field with the Data Validation tool.
Add a dropdown list to the Action field with the Data Validation tool.

To activate this feature.

  1. Click on the “Action” input field
  2. Click on Data
  3. Click on Data Validation. The Data Validation dialog box will appear
  4. Change Allow from “Any value” to “List”.
  5. Specify the Source as “=Ops_Action”, which is one of the ranges created on the Operations tab in Step 5.
  6. Press OK.

A working dropdown list.

If completely successfully, you should now see an arrow to the right of the cell when you click on the “Action” input field. When you click on the arrow, a dropdown list should appear where you can select Buy OR Sell.

Step 7: Time to work with VBA.

Start by opening the VBA editor in your workbook. Click here for guidance on opening the VBA editor. If you have followed the instructions up to this point exactly as they are presented, you should be able to simply copy and paste this code into your a new module. In this example, an easy and advanced approach will be provided.

The following is a straightforward data entry script with no validation features. Validation is an important component of any data entry tool, especially if it is used by multiple users. You should read posts on data validation with Excel’s validation tools, data validation with formulas, and data validation with VBA.

You may be curious as to why so many variables have been declared in this exercise. It is true that you could approach this without using a single variable. However, this example is sticking with these coding practices as:

  1. For inputs, you may decided to add data validation or more complex calculations at a future time.
  2. Although this example could save on system overhead by more specifically declaring input variables as integers, longs, and strings, it’s helpful to declare them as variants should you choose to include your own validation functions, as done in the next example.
  3. For columns, it’s generally a good practice to define a column number on a dataset through a named range. This way, if you decided to insert or remove columns within the dataset, you will not need to update all the column numbers in your code. Errors resulting from this would not be caught by the debugger and could go unnoticed. This approach is especially important if you anticipate a lot of scope creep within your project.

 

Screen Captures

Final screen capture of data input form.

Download Solution: 2016-10-16-user-form-data-input-solution

Other Points

This exercise provides the bare essentials for data input and validation. Some other good features and approaches to provide uses are:

  • Ability to insert data in a specific row so the list remains in order, such as in chronologically or alphabetically. This can be done through a Do While loop or automating Excel’s sort functionality after each entry.
  • Many more validation options exist, such as checking if phone numbers are valid, that a string contains no numbers, and that addresses are properly formatted. These are just a few examples.
  • User input forms can be much more elaborate than the example on this exercise. The more you can control user inputs, the better. Examples of additional form inputs through Excel include radio buttons and listboxes. These will be discussed in details on future posts.

Drawbacks/Bugs

These data entry approaches can eliminate bugs if not correctly. However, some drawbacks exist, as it’s difficult for users to add and or modify large sets of data if they do not have access to the dataset.

Leave a Reply