With VBA, you can create user forms that take your Excel spreadsheets to another level. User forms are interactive pages within your application that let you provide controls and text boxes for input. VBA comes with a number of pre-made forms and controls that you can use to customize your Excel application. In this article, we'll cover the way you can create these forms and code interactive controls to work with the Excel data.
Creating User Forms
You can do the same with a user form. Click the Insert menu item and select UserFrom from the menu. VB automatically creates a user form and you can see it in project explorer. You can see the form in the "Forms" tree item. VB provides the default name UserForm1. You can right-click this item and rename it. When you rename the form, you rename it globally for all of your application, so ensure that you give the form a name prior than coding any part of the user form application.
When you create the user form, you'll notice a toolbox is shown at the right of the form. If this toolbox doesn't display, you can click on the View menu item and select "Toolbox." This will toggle the toolbox on and off. For most of your user form programming time, you'll need to work with the toolbox, so it's best to keep it visible.
To get started, drag and drop a control from the toolbox to your form. The VBE automatically creates a new button and size it accordingly. You can resize the button and move it to a place on your form that makes sense. Double-click on it to automatically create an event subroutine in the user form code window. You'll see code similar to the following.
Private Sub CommandButton1_Click()
End Sub
An event always has the Private modifier. The first part of the subroutine's name is the command button's name. Since we didn't take the time to change the button's name, it takes the default name created when you drag and drop the button from the toolbox to the user form.
In this example, the button's name is CommandButton1. If you change the name of the button now, the event name remains the same, so don't manually change the name if you change the button name after the event is created.
The Click name after the underscore indicates that the event is the Click event. As you can guess, the Click event is fired when a user clicks the button with the mouse.
You can show and hide a user form as you need it. We can take advantage of this new button and illustrate how to hide a user form. You can then show the user form using automation. For instance, you could use the new button to hide the user form, and then you could use a button in the Excel spreadsheet to show the user form. Let's take a look at the code to hide the user form using the new button.
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub
In this example, we left the default user form name as UserForm1. Since this is the name of the form, it is also the name of the class. We then use the Hide method for the user form class to hide the form from view.
When you hide a form, you do not destroy it from memory. This means that the form is hidden but still active and data on the form remains. You don't lose information from the form until you actually close it or the workbook.
We hid the form, but you probably need a way to show it. The following code shows the form on the user's screen.
UserForm1.Show
You can place this code in one of the form control available in the spreadsheet view or through some kind of automation.
Using Form Controls
We created a simple button in the previous section, but VB has several different types of controls that you can use. The toolbox contains check boxes, radio controls, text boxes, buttons, and image controls. Most user forms have at least one button and a text box. The text box takes user input in the form of a string, and the button submits the user input to your backend programming.
For this section's example, drag and drop a text box control to your form. If you deleted the previous button, drag and drop a new button from the toolbox to the form. You now have a text box control and a button control to retrieve user input.
Give your button and your text box a name. Name the text box "txtName" and name the button "cmdSubmit." Both of these names properly describe both functionalities for the controls. You want to give your controls a name that represents what they do. This type of naming scheme makes it easier to identify controls within your coding files. If you don't give controls meaningful names, it's more difficult to know what control you're working with when you have dozens of controls on the form.
Double-click the button, and VBE automatically creates the Click event that we saw in the previous section. You can also choose the control name in the first dropdown menu in the coding window, and then choose the Click event in the Declaration dropdown menu. Both of these methods create the subroutine for the click event, which is what you need to override to code functionality when the user clicks the button.
When you click a control on your form, you'll notice that a property window displays in the left project explorer panel. These properties can be edited within the window or in your code. If you have a static value that you want to set in a property, then using the window is sufficient, but you'll need to code many of the properties dynamically using your VBA code.
When you double-click the button name, you see an auto-generated block of code that represents the click event. The code you see is below.
Private Sub cmdSubmit_Click()
End Sub
With the event created, every time you click the button this code is executed. Since it has no code, nothing happens even though technically the event is overwritten. We want to get the data entered in the text box. The following code shows you how to get data from a text box.
Private Sub cmdSubmit_Click()
Dim value As String
value = txtName.Text
End Sub
In the above code, we create a variable used to contain the value from the text box we created. Notice that we used the txtName, a period and then the Text property. You reference user form controls using the name you gave the control earlier. Control properties and methods are referenced the same way we used other class methods and properties.
Each property contains a different value for your controls. You might need to look up a property's data type. You can hover over the property name and the VBE will give you a short description. If you are still unsure, look up the description at Microsoft's website. Control properties include the colors, background, layout, and size properties. Some properties you will rarely use, but others you will use with each control that you create.
Validate User Entries
When you create forms that require input, you want to ensure that the user has entered data before they can submit the form. In most cases, you capture data from user entry and then input it into a database. If you don't check that data was entered, you wind up storing empty data in your database and it becomes a useless record.
For instance, with a text box, you want to validate that the user entered some kind of data before allowing the form to submit to your database. You can use custom validation methods, or you can identify if the text box is blank. The following code is an example of how you can validate that the user entered data in the text box.
Private Sub cmdSubmit_Click()
Dim value As String
value = txtName.Text
If value = "" Then
MsgBox "Please enter a name."
Else
' Perform your database code here
End If
End Sub
We added an If statement to the Click event. We first got the value from the text box and used the If statement to identify if the input is a blank. If it's blank, a message box is shown to the user with the message "Please enter a name." If the text box is blank, then no other code executes. If the value isn't blank, then you can perform your database functionality in the Else statement.
Data Mining with Advanced Filters
When you work with large spreadsheets in Excel, it's difficult to manage the massive amount of data and make any use of it. Some spreadsheets can contain thousands of records, and you need to search for certain records to create graphs, charts and any meaningful research data. Fortunately, you can search through a large named range and extract the data. This type of approach is often called data mining, and it's useful for retrieving a data set and using it for separate sheet information.
Retrieving the Data Set
Named ranges will group the data into a block or matrix that we can then search through. You can also use the entire spreadsheet as your named range. In this example, we will use a named range, but just know that you can use a range that covers the entire spreadsheet.
Remember that large spreadsheets have several rows and columns and could contain thousands of records. The more records you search through, the more time it takes. You could loop through each record, or you can use the Find function, which is an internal function within the Excel framework.
The Find function replaces loops, which would otherwise be tedious, take too much memory resources from the computer, and could ultimately crash Excel. Since crashing the program is a poor user experience, you want to find an alternative way to loop through your records.
Let's first take a look at the variable we need for the Find function to store the range to a variable.
Dim range As Range
Notice that we created a variable with the Range data type. This variable will store the extracted data that we data mine from the Excel spreadsheet.
Let's take a look at the code to find data in the first spreadsheet in the Excel workbook.
Set range = Sheets("Sheet1").Range("WholeSheet").Find(What:="Test", _
After:= = Sheets("Sheet1").Range("WholeSheet").Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
The above code is a lot of functionality in one Find command. Let's break down each section of the search to understand how the function works.
We reference the first sheet named Sheet1 with a range named WholeSheet. In this instance, you need to have the WholeSheet named range defined. You can also use a cell defined range. For instance, the following code would also work with the cells defined as the range.
Set range = Sheets("Sheet1").Range("A1:AA100").Find(What:="Test", _
After:= = Sheets("Sheet1").Range("WholeSheet").Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Once we define the range, we then call the Find method. The parameters within the Find method are what define what will be searched by the function. The first parameter is "What." This basically tells the method what string or number to find. In this example, we want to search for the string "Test." You can search for any string, character, number or value. As long as the value is found in a cell, this cell will be returned in the Find function. The What parameter is the meat of the search function, and it's the only required parameter when you create a Find function that extracts data and assigns the results to a Range variable.
The After parameter is the cell after which you want to start the search. This is the cell directly after the currently active cell.
The next two parameters are usually the standard parameter values. LookIn is set to formulas, which basically means all of the values stored within the named range cells. The next parameter is the LookAt parameter. You typically want to leave this as the whole value, because you want the Find function to search all values for the text.
Notice that these two parameters have the xl prefix. This means that they are pre-defined value in Excel. Excel has several pre-defined variables just like the VB language that you can use as values instead of single, static integer values. We use the pre-defined variables because they offer a way to quickly identify what value we're using. Instead of using a 1 or a 0, we use a pre-defined variable that has a more reasonable reference that's easily identifiable when you read and maintain your code.
The next parameter is the SearchOrder parameter. In this example, we search by rows. You can also search by columns. Most developers search by rows, because there are more rows than columns. However, if you have more columns than rows, it's better to search by column values rather than rows.
The next parameter is the SearchDirection option. This is typically the value you want to use as well. You want to use the xlNext value to keep scrolling down the list of cells. You can also use the xlPrevious option, which will scroll up the list of values and extract information. Since this is rare, you can typically just use the xlNext value with all of your Find functions.
The final parameter is the MatchCase parameter. This parameter tells the compiler if you want to match the cast of your search. For instance, we are searching for Test with an uppercase T. If the Find function identifies a string value of "test," then it will still flag the cell since the MatchCase parameter is set to false. If we set this to true, only values with a capital T will be extracted from the data mining process.
Once we combine all of these options and run the Find function, the compiler searches through the spreadsheet and extracts the data. It then assigns the values found to the range variable, which we have defined as a Range variable.
Extracting Filtered Data to a New Sheet
We now have a range of cells that we've stored in a Range variable. This range contains all of the cells that have the "Test" string contained in them. At this point, we want to extract the data and then add the cells to a new spreadsheet. This separates the data mined data from the original spreadsheet so we can create graphs or charts to analyze the data. This data could be millions of values.
Just like looping through other cell ranges, you can now loop through your data and add it to another worksheet. The best way to handle the loop extraction is using a For Each loop. A For Each loop with go through each cell in the newly extracted range and retrieve the values contained within the cells.
Let's take a look at the code that would handle this functionality.
Dim range As Range
Dim cell As Cell
Set range = Sheets("Sheet1").Range("WholeSheet").Find(What:="Test", _
After:= = Sheets("Sheet1").Range("WholeSheet").Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
j = 0
For Each cell In range
Sheets("Sheet2").Cell(0, j) = cell.Value
j = j + 1
Next cell
In the above code we first search for all cells that contain the value of Test. Once Excel is able to find all of these cells, the range is then stored in the range variable. It can take several seconds if there are several cells to search. For the most part, the application should not crash. If there are millions of records to search, which can be the case with a large CSV file, the application might crash regardless of the streamlined function.
After the cells are extracted, we create a variable named j. This variable will be used to iterate through the new worksheet cells and use this iteration to assign the extracted values to the appropriate cell.
The next section is the For Each loop. This loop takes each cell in the extracted range and assigns it to the cell variable. In other words, for each cell in the range, the individual cell is assigned to the "cell" variable so that we can retrieve its data and use it for our logic structure. You could store the data in a database or extract it to a new worksheet, which is what we do.
The For Each loop assigns the cell's value to the first row in Sheet1. Each new value is assigned to the next column in the loop. We control this using the j variable that iterates after each loop. You could also use the j variable in the row parameter in the Cell function to add data down the same column but in different rows. The place you extract the data is up to you.
Once you've finished creating this function, you can use it on a small spreadsheet to test its functionality. You wouldn't want to test it on a large spreadsheet in case the data is accidentally manipulated, or you introduce bugs to your application. Most developers test their code on a smaller data set. This lets you review the data to ensure that your function is properly extracting the right data.
