- Spreadsheet Design Exercise 4
- Screen Captures
- Other Points
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.
|Time Est.||45-60 Minutes|
Spreadsheet Design Exercise 4
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.
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
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.
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.
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.
To activate this feature.
- Click on the “Action” input field
- Click on Data
- Click on Data Validation. The Data Validation dialog box will appear
- Change Allow from “Any value” to “List”.
- Specify the Source as “=Ops_Action”, which is one of the ranges created on the Operations tab in Step 5.
- Press OK.
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.
Dim inputStockSymbol As Variant
Dim inputAction As Variant
Dim inputDate As Variant
Dim inputPrice As Variant
Dim inputShares As Variant
Dim inputCommission As Variant
Dim opsPurchaseID As Long
Dim calcPurchaseID As Long
Dim calcTotalCost As Variant
Dim columnDataPurchaseID As Integer
Dim columnDataStockSymbol As Integer
Dim columnDataAction As Integer
Dim columnDataDate As Integer
Dim columnDataPrice As Integer
Dim columnDataShares As Integer
Dim columnDataCommission As Integer
Dim columnDataTotalCost As Integer
Dim columnOutputStatus As Integer
inputStockSymbol = Range("Input_Stock_Symbol").Value
inputAction = Range("Input_Action").Value
inputDate = Range("Input_Date").Value
inputPrice = Range("Input_Price").Value
inputShares = Range("Input_Shares").Value
inputCommission = Range("Input_Commission").Value
opsPurchaseID = Range("Ops_Purchase_ID").Value
columnDataPurchaseID = Range("Data_Purchase_ID").Column
columnDataStockSymbol = Range("Data_Stock_Symbol").Column
columnDataAction = Range("Data_Action").Column
columnDataDate = Range("Data_Date").Column
columnDataPrice = Range("Data_Price").Column
columnDataShares = Range("Data_Shares").Column
columnDataCommission = Range("Data_Commission").Column
columnDataTotalCost = Range("Data_Total_Cost").Column
' === Calculations ===
calcPurchaseID = Sheet3.Range("Ops_Purchase_ID").Value + 1 ' Adds 1 to current purchase ID in Operations sheet.
Sheet3.Range("Ops_Purchase_ID").Value = calcPurchaseID ' Updates purchase ID for next purchase.
calcTotalCost = (inputPrice * inputShares) + inputCommission ' Calculates total cost based on user inputs
' === Dataset Entry ===
Sheet2.Cells(2, 1).EntireRow.Insert ' Insert new row on row 2 of dataset.
' If all validations pass, values are placed in the new/empty row 2 of dataset.
Sheet2.Cells(2, columnDataPurchaseID).Value = calcPurchaseID
Sheet2.Cells(2, columnDataStockSymbol).Value = inputStockSymbol
Sheet2.Cells(2, columnDataAction).Value = inputAction
Sheet2.Cells(2, columnDataDate).Value = inputDate
Sheet2.Cells(2, columnDataPrice).Value = inputPrice
Sheet2.Cells(2, columnDataShares).Value = inputShares
Sheet2.Cells(2, columnDataCommission).Value = inputCommission
Sheet2.Cells(2, columnDataTotalCost).Value = calcTotalCost
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:
- For inputs, you may decided to add data validation or more complex calculations at a future time.
- 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.
- 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.
Download Solution: 2016-10-16-user-form-data-input-solution
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.
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.