This is the third part of the Excel Visual Basic tutorial series. It is recommended that if you are new to VBA, you begin with Excel Visual Basic #1: Getting Started. You can view all available tutorials on the Excel VBA Tutorials page.

Take a look at the image below:

The items in column “A” need to be merged together into the combo box we have on the right. We want to be able to do that with a push of a single button, the “Merge List” button in blue shown above. Additionally, we want to be able to allow for new items to be added to the list, or maybe the list shrinks. We don’t know, it is a variable length list.

Our requirements are as follows:

• Move all items in the list from column “A” into a combo box for easier selection
• The items list may change in size and the application needs to consider a variable length list
• We must save the document as a macro-enabled Excel file

1. Create your button and combo box

Creating buttons should be familiar to you by now if you have taken the earlier tutorials, but here it is again. Go to the “Developer” tab and click the “Insert” drop-down. Select the “Command Button” ActiveX Control and click anywhere within your Excel sheet to place the button.

You go through a similar process for a combo box, only you select “Combo Box” from the Active X controls.

2. Name the elements you created in step 1

On the “Developer” tab, you can move your mouse over to properties and click it.

You can click on your button, making sure you have “Design Mode” turned on, and making “ListMerge” the name of the button. Make whatever style changes you want on the button. Play with font, color, and experiment with the other options a bit. The worst you can do is mess up the element and have to delete it and remake it.

Make sure to change the combo box name to “MergedList” and play around with its settings if you want. One way to learn is to play around.

3. Create a Dynamic Function

If you recall, we worked with subroutines in earlier tutorials. We will now work with functions. Functions and subroutines have a lot of similarities and some differences. The largest differences you are concerned with:

1. functions return some sort of value, subroutines do not
2. Functions are designated with a “Functions” keyword, Subroutines use “Sub”
3. Functions work like variables in that they should have a type declared.
4. A value should be assigned to the Function so that it can be returned

We need a way to access the list items contained in column “A” and we need it to be able to work without knowing in advance how many items are in the list. It needs to work with 1 item and 1,000 items.

Open your developer panel up by going to the “Developer” tab and clicking on “Visual Basic.”

Add the following code to the developer’s pane for sheet 1:

Private Function GetListCell(rowNum As Integer) As String
      GetListCell = "A" & CStr(rowNum)
 End Function

This function is named GetListCell and it has one parameter called rowNum. A parameter is a value that you can pass to a function or subroutine. It is similar to a variable and is treated as such within the confines of the declaring function.

we assigned the function GetListCell to a type of String (using “As String”). This means the value that we want to return is a String value. The parameter rowNum is assigned to a type of Integer, which means we need to pass it a numerical value with no decimal points.

CStr(rowNum)

The section CStr(rowNum) changes the Integer value to a String so that it can be combined with the “A” of the column header, making the line “A” & CStr(rowNum) equal to the cell, or A and the integer value we pass to the function as a parameter. We set that equal to the name of our function so that when the function is called it returns a String “A + rowNum.”

4. Create the ListMerge_Click Subroutine

If you haven’t done so already, double-click on your “ListMerge” button to auto-create the ListMerge_Click subroutine. Then think about what we want to do. We want this subroutine to grab each item from the list and put it into the combo box so that we can make a selection from the combo box. We have created a function that takes an integer to return back to us a cell. It seems to me that we need a counter and a loop.

So we will create the counter first. Add the following lines to the ListMerge_Click subroutine:

Dim rowCounter As Integer 'Counter used to help find each list item
 rowCounter = 1 'First available row on the list

You will notice the apostrophes in the example I provided above. VBA allows you to add notes. Notes can be added to tell you things about sections of code. The executing application ignores anything after an apostrophe on a line. These are just for you and anyone that reads your code.

Your application should look like the following currently:

Private Function GetListCell(rowNum As Integer) As String
      GetListCell = "A" & CStr(rowNum)
 End Function

 Private Sub ListMerge_Click()
      Dim rowCounter As Integer 'Counter used to help find each list item
      rowCounter = 1 'First available row on the list
 End Sub

Now, we are going to ensure that each time we populate the combo box we start with a clean slate. This is done because if the list items change, we want to clear the old list out to add in the new items only. We will do that by taking the “MergedList” combo box and applying a pre-built VBA combo box function called “Clear.”

Below the line that contains rowCounter = 1 add the following:

 MergedList.Clear 'Clear the MergedList combo box

Below that line, we will create a loop:

While Not ThisWorkbook.Sheets("Sheet1").Range(GetListCell(rowCounter)).Value = ""

 Wend

That loop looks at “Sheet1” and gives it a cell. The cell is received from the GetListCell function we created earlier. The loop will run until it finds an empty cell.

Currently, as written above, it would be an infinite loop. The variable rowCounter is set to 1. It will stay set to 1 because we are not increasing the value of it. So this loop will not do anything and it will run forever.

Just so you know what that looks like, it will lock your Excel up. If you try to exit, it will give you some difficulty. Eventually, you can get it to crash closed.

Within that loop, we want to eliminate the possibility of an infinite loop and add each list item to the combo box. Add the following code to your loop between the “While Not” and “Wend” lines:

MergedList.AddItem ThisWorkbook.Sheets("Sheet1").Range(GetListCell(rowCounter)).Value
 rowCounter = rowCounter + 1

As you can see on the second line, we take the rowCounter variable and set it equal to the rowCounter variable plus 1. So for each increment of the loop, rowCounter has 1 added to it. This would cause the loop code to look like the following:

Iteration 1: While Not ThisWorkbook.Sheets("Sheet1").Range("A1").Value = ""
 Iteration 2: While Not ThisWorkbook.Sheets("Sheet1").Range("A2").Value = ""
 Iteration 3: While Not ThisWorkbook.Sheets("Sheet1").Range("A3").Value = ""

And it would continue until it found it’s first empty cell.

The other line, the first line, references the “MergedList” combo box and gets the value from each cell as the loop iterates.

Your total code should resemble the below code:

Private Function GetListCell(rowNum As Integer) As String
      GetListCell = "A" & CStr(rowNum)
 End Function

 Private Sub ListMerge_Click()
      Dim rowCounter As Integer 'Counter used to help find each list item
      rowCounter = 1 'First available row on the list

      MergedList.Clear 'Clear the MergedList combo box

 'Our loop to get each item in the list and put it in the combo box
      While Not ThisWorkbook.Sheets("Sheet1").Range(GetListCell(rowCounter)).Value = ""
             MergedList.AddItem ThisWorkbook.Sheets("Sheet1").Range(GetListCell(rowCounter)).Value
             rowCounter = rowCounter + 1
       Wend
 End Sub

All you have left to do is test it out. Play around with it. Make a list of different sizes. For fun, figure out how to change the column it reads from. (Hint: the GetListCell function you created and the “A” value.)

Your combo box should contain all of the items in the list. When you click on it, it should drop down to display all available items.

5. Saving a Macro-Enabled Excel File

You cannot save an Excel file with a macro the same way you would save a standard Excel file. The process is almost the same, You do what you normally do to save a file, going to “file” and “SaveAs” or however you do it. When the screen pops up for you to name it, you must change the type of Excel file. in the “Save as Type” drop-down box below the “File Name” text box, you select “Excel Macro-Enabled Workbook” and that is all there is to it.

And that is it. You have now completed this tutorial. Get prepared for tutorial 4 and make sure you understand the concepts in the first three tutorials. Tutorial 4 takes everything in these tutorials, adds a couple of additional things, and makes something pretty neat.