Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to Add or Edit Chart Title Using VBA

Typically, If you have titles in your data, Excel will automatically add a relevant title to the chart. But if no titles present in the data or if titles are not selected when inserting the chart, then the created chart will not have a title. And sometimes you might need to add a different title to the chart than present in the data. So in this lesson you will learn how to add or edit a chart title using VBA. Let’s consider this sample chart.

Sample chart

This chart was created from the data below.

Sample data

If you look at the above Excel line chart, you will notice that the chart was created with the default title text called “Chart Title”. So in this lesson first let’s look at how to edit an existing title like that.

Assume you want to edit a title of an active chart. You can do it very easily as shown below.

Sub EditActiveChartTitle()

     With ActiveChart
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

However, for this macro to be worked, your chart should be in an active state(Selected). Otherwise the VBA program will throw an error like this.

Program will throw an error if the chart is not selected
Error line

But if you know the name of the chart then you can develop a VBA code to edit the title of the chart independent of whether it is active or not. For example, the subroutine below will change the chart title to “Monthly Sales” of a chart called “Chart 2”.

Sub EditTitleFromChartName()

     Dim MyChart As Chart

     Set MyChart = ActiveSheet.Shapes("Chart 2").Chart
     With MyChart
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Don’t know how to find the name of a chart? Check this post to learn how.
Find the name of a chart in Excel

Sometimes you might need to add a chart title to a chart which doesn’t have any title. Here is an example. This chart doesn’t have any title. As mentioned above this can happen if no titles present in the data or if the user doesn’t select the titles when inserting the chart. So let’s see how we can add a title to it.

Chart without a title

To add a chart title, we need to do a very simple change to the above VBA code we used to edit the title. The only change needed is we should make the HasTitle property from False to True. So this is how you can add a chart title to an active chart by changing the HasTitle property.

Sub AddChartTitle()

     With ActiveChart
         .HasTitle = True
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Also If you know the name of the chart you want to add the title then you can change the above VBA code like this.

Sub AddTitleFromChartName()

     Dim MyChart As Chart

     Set MyChart = ActiveSheet.Shapes("Chart 2").Chart
     With MyChart
         .HasTitle = True
         .ChartTitle.Text = "Monthly Sales"
     End With

End Sub

Here is the result of the above macros.

VBA Macro will add the title to the chart

Also Read
How to create a line chart automatically - Excel VBA
Swap Axis of an Excel Chart Without Changing Excel Sheet Data
Formatting Excel Line Charts Using VBA

How to Insert a Pie Chart Using VBA

In this lesson you will learn how to insert a pie chart automatically using VBA. Pie charts are very useful in data visualization. Because from a pie chart, users can get a clear picture about the data at a glance. So let's use the below sample data to see how to create a pie chart automatically using VBA. This table shows the profits of different branches of a company.

sample data sheet

Assume name of the worksheet is "Sheet1". As we have data in the range A1:B6, we can insert the pie chart automatically using VBA as follows.

Sub Create3DPieChart()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(-1, xl3DPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub

Here is the result of the above macro.

VBA Program will insert the default pie chart

This is the syntax of the AddChart2 method.

Syntax of the AddChart2 method

Note that all the parameters are optional for the AddChart2 method. In the above VBA code we have set “-1” as the style and the “xl3DPie” as the xlChartType. Since we have used “-1” Excel will create the default style of the xl3DPie chart type.

Check this post if you want to learn more about the AddChart2 method.

AddChart2 Method

Next let’s see how we can insert a 2D Pie chart by modifying the above VBA code. To do that we need to only change the second parameter of the AddChart2 method.

Sub Create2DPieChart()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(-1, xlPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub

If you run the above macro, a 2D Pie chart will be created like this.

2D Pie chart inserted by the VBA macro

Sometimes you may want to insert a pie chart with a different style. For an example, suppose you want to create a pie chart with a dark background. This is how you can do it in Excel 2013.

Sub Create3DPieChart_DarkBackground()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Shapes.AddChart2(262, xl3DPie).Select
     ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$6")

End Sub
Pie chart with dark background

Note that we only changed the style number to create this new chart.

However not all the chart styles are available for every Excel version. Because newer Excel versions have more features than previous versions. So how do we find the exact style number of our prefered chart? You can find that by using the Record Macro function.

Follow these simple steps to find the style number of your preferred chart.

Go to the developer tab and click on Record Macro.

Go to developer tab and click record macro

Give the macro a suitable name and click OK. Also you can decide where you want to store the VBA macro by selecting an option from the “Store macro in” dropdown.

Give the macro a name and click OK

Then select the data.

Select data

Go to the Insert tab and select the chart you want to create.

Select the chart you want to insert

Now select a chart design from the list. I will select the 3rd design from the top row for this example.

Select a chart design from the list

Next, go to the “Developer” tab again and click “Stop Recording”.

Stop recording

Click on the “Visual Basic” to view the code.

Click on the visual basic to view the VBA code

Now you can incorporate these circled values to your code to create a chart automatically with similar style.

Generated VBA code

Check this post if you want to learn more about recording a macro in Excel.

Record a Macro in Excel

VBA - Get Unique Values From Column Into Array

When developing Excel VBA programs, sometimes we need to get unique values from a column into an array. Particularly when creating dynamic lists for dropdowns or generating reports. For an example assume you have a dropdown in your VBA program. Suppose that it needs to be updated with the data entered by the user. If the related data is stored in a column, then we can use that column to populate the dropdown. But what, if values are repeated in the column? We don’t show duplicate values in a dropdown list. So then you need to get only unique values to the dropdown. To do that first we can add unique values from column to an array. Then we can easily create the list of the dropdown using that array. So in this lesson you will learn how to populate an array from unique values of a given column. Also I’m going to develop a VBA function for this. So you can readily use it in your VBA programs.

Below is the list I’m going to use for this lesson. It is a list of asset ids. And they are listed in column A.

List of asset ids

This is not a list of unique values. Because some asset ids are repeated in the column. So now let’s see how we can add only unique values from this column to a VBA array. I’m going to create a custom VBA function for this.

Function GetUniqueValuesFromColumnIntoArray(WS As Worksheet, ColumnName As String) As String()

End Function

This VBA function has two parameters. WS and ColumnName. I added these two parameters to input the worksheet name and the column. So you can reuse this for your worksheets easily. Also note that the return type of the VBA function is String(). This is because the function needs to return an array. Want to learn more about returning an array from a VBA function? Check this post.

How to Return an Array From VBA Function

Now we have declared the function name with parameters. Next we need to declare a few variables.

Dim WS_ColumnName_LastRow As Long
Dim i As Long
Dim j As Long
Dim Counter As Long
Dim AllValues() As String
Dim UniqueValues() As String
Dim ValueFound As Boolean

AllValues() array will hold all the values from the column. The UniqueValues() variable will hold only the unique values from the column.

Next, find the last row of the list.

With WS
     WS_ColumnName_LastRow = .Cells(.Rows.Count, ColumnName).End(xlUp).Row
End With

Size the “AllValues” dynamic array that has already been formally declared.

ReDim AllValues(1 To WS_ColumnName_LastRow)

Populate the AllValues array using a For Next statement.

For i = 1 To WS_ColumnName_LastRow
     AllValues(i) = WS.Range(ColumnName & i).Value
Next i

Then we need to size the UniqueValues array. Here we size the UniqueValues array to the same size of the AllValues array. Because at this moment we don’t know how many unique values we will have in the array. Once all the unique values are populated then we can resize the array to appropriate size.

ReDim UniqueValues(1 To WS_ColumnName_LastRow)

Add the first element from AllValues to the UniqueValues array.

UniqueValues(1) = AllValues(1)

Next we use a nested For Next statement to find unique values.

Counter = 1
For i = 1 To WS_ColumnName_LastRow
     ValueFound = False
     For j = 1 To Counter
          If StrComp(AllValues(i), UniqueValues(j), vbTextCompare) = 0 Then
              ValueFound = True
              Exit For
          End If
     Next j
     If ValueFound = False Then
          Counter = Counter + 1
          UniqueValues(Counter) = AllValues(i)
     End If
Next i

In the above code, the outer For Next statement is used to iterate through the elements of the AllValues array.

For i = 1 To WS_ColumnName_LastRow

Next i

Then this inner For Next statement is used to iterate through existing(Newly adding) elements of the UniqueValues array.

For j = 1 To Counter

Next j

StrComp function returns 0 if the elements of the two arrays are matching.

StrComp(AllValues(i), UniqueValues(j), vbTextCompare)

Here the variable ValueFound is used as a flag.

If the value is not found among the elements of the UniqueValues array, then this new value is added as the next element.

If ValueFound = False Then
     Counter = Counter + 1
     UniqueValues(Counter) = AllValues(i)
End If

Once all the unique values are collected to the UniqueValues array, we can resize the UniqueValues array as follows. Use the Preserve keyword to keep the existing values while resizing the array. If not, all the values will be erased.

ReDim Preserve UniqueValues(1 To Counter)

Finally, the function returns the UniqueValues array as the output.

GetUniqueValuesFromColumnIntoArray = UniqueValues

And Below is the full code of the function.

Function GetUniqueValuesFromColumnIntoArray(WS As Worksheet, ColumnName As String) As String()

     Dim WS_ColumnName_LastRow As Long
     Dim i As Long
     Dim j As Long
     Dim Counter As Long
     Dim AllValues() As String
     Dim UniqueValues() As String
     Dim ValueFound As Boolean

     With WS
          WS_ColumnName_LastRow = .Cells(.Rows.Count, ColumnName).End(xlUp).Row
     End With

     ReDim AllValues(1 To WS_ColumnName_LastRow)

     For i = 1 To WS_ColumnName_LastRow
          AllValues(i) = WS.Range(ColumnName & i).Value
     Next i

     ReDim UniqueValues(1 To WS_ColumnName_LastRow)
     UniqueValues(1) = AllValues(1)
     Counter = 1
     For i = 1 To WS_ColumnName_LastRow
          ValueFound = False
          For j = 1 To Counter
               If StrComp(AllValues(i), UniqueValues(j), vbTextCompare) = 0 Then
                    ValueFound = True
                    Exit For
               End If
          Next j
          If ValueFound = False Then
               Counter = Counter + 1
               UniqueValues(Counter) = AllValues(i)
          End If
     Next i

     ReDim Preserve UniqueValues(1 To Counter)
     GetUniqueValuesFromColumnIntoArray = UniqueValues

End Function

You can use this function inside a subroutine like this. Assume the name of the worksheet is “Data”.

Sub Test()

     Dim WS As Worksheet
     Dim UniqueIds() As String

     Set WS = Worksheets("Data")

     UniqueIds = GetUniqueValuesFromColumnIntoArray(WS, "A")

End Sub

Add a breakpoint at “End Sub” and run the program. Then view the UniqueIds array in the “Locals” window.

View the array with unique values

The example worksheet we considered above has a header in row 1. Therefore we have the header “Asset ID” also in the result array. But sometimes you might need to populate unique values into an array without the header. There are few different ways to achieve this. Also you can do it by changing the function or changing the subroutine. In here I will show you how to modify the subroutine to get unique values without the header.

Here is how we can modify the subroutine. Once we get the unique values to the array, we can iterate through all the elements using a For Next statement. While loop through the elements we can decrement the index of each and every element by 1. Then the first element which is the header will be removed. Finally we can resize the array to one less than the original size. To keep the current values use the Preserve keyword when resizing.

Sub Test_2()

     Dim WS As Worksheet
     Dim UniqueIds() As String
     Dim i As Long

     Set WS = Worksheets("Data")

     UniqueIds = GetUniqueValuesFromColumnIntoArray(WS, "A")

     For i = 1 To UBound(UniqueIds) - 1
          UniqueIds(i) = UniqueIds(i + 1)
     Next i

     ReDim Preserve UniqueIds(1 To UBound(UniqueIds) - 1)

End Sub

Below is the outcome of the above subroutine.

Adding unique values from a column into an array

Also Read
Fill a Listbox From an Array
Transposing an Array in VBA
Re-size Dynamic Arrays
Quickly Write Multidimensional Array to Excel Range

Different Ways to Delete Rows in Excel Sheets

When you use Excel for your personal, office or business work, you will often need to delete entire rows from the worksheets. In this lesson you will learn how to delete rows in Excel. There are few different ways to delete rows manually. I will explain them one by one. Let’s consider this sample Excel sheet.

Sample Excel sheet

Here is the first method to delete rows manually.

Method 1

Right click on a cell of the row you wish to delete. Then select “Delete” from the drop down menu.

Right click and click delete

Then the “Delete” window will appear like this. Select the “Entire row” option button and click OK.

Select Entire row and click OK

You can also use this method to delete multiple rows as well. First select atleast one cell from each row you want to delete. In this example Excel sheet, I have selected rows 6, 8, 10 and 12. You can use the Ctrl key on your keyboard to select multiple cells. (Press Ctrl key while selecting cells.)

Select atleast one cell from each row you want to delete

Now you can follow the same steps you followed to delete one row.

Method 2

In this method, first you should take the mouse to the left most side of the Excel sheet. When you take the mouse on top of the row numbers, the mouse pointer will be changed to an arrow like this.

Take cursor to the top of the row number you want to delete

Now click the right mouse button. Select “Delete” from the menu. Row will be deleted instantly.

Select Delete from the dropdown menu

Likewise you can use this method to delete multiple rows in Excel sheets. First select the rows you want to delete. You can use the Ctrl key on your keyboard to select multiple rows.

Select multiple rows

Once you select the rows, you can follow the same steps followed to delete a single row.

Sometimes we may need to delete a set of adjacent rows in Excel. This video shows how to select a set of contiguous cells easily and then delete the entire rows.



Here is also an alternative way to delete a set of adjoining rows.

Also read
How to Read, Write, Delete and Move excel comments
Record a Macro in Excel
Clear Contents of Excel Sheet Except First Row Using VBA

How to Pass an Array to a VBA Function

In this lesson you will learn how to pass an array to a VBA function. You can’t pass an array to any type of function. Function should be developed in such a way that it can accept arrays as variables. Syntax is similar to functions which accept other types of variables. But array arguments should have parenthesis at the end of the name. Here is the syntax of a VBA function which accepts an array as an argument. Data type of the argument is string. So you can only pass an array of a type string to this function.

Function FunctionName(ArrayParameterName() As String)

     '-----------------------
     'Codes of the function
     '-----------------------

     FunctionName = ResultString

End Function

Above example shows the syntax of a VBA function which has one argument. It accepts only arrays as the argument. Now let’s look at how to create a function using that syntax. Let’s develop a VBA function to calculate the average marks of students. It will accept an array of type double as the argument. This is how we can do it.

Function AverageMarks(Marks() As Integer) As Double

     Dim TotalMarks As Integer
     Dim StudentCount As Integer
     Dim i As Integer

     For i = LBound(Marks) To UBound(Marks)
         TotalMarks = TotalMarks + Marks(i)
         StudentCount = StudentCount + 1
     Next i

     AverageMarks = TotalMarks / StudentCount

End Function

Function iterates through elements of the “Marks” array while calculating the total marks and the student count. Finally calculate the average marks by dividing total marks by the student count. We can call this within a subroutine as follows.

Sub FindAverageMarksOfClass()

     Dim StudentMarks(9) As Integer
     Dim ClassAvg As Double

     StudentMarks(0) = 52
     StudentMarks(1) = 92
     StudentMarks(2) = 85
     StudentMarks(3) = 45
     StudentMarks(4) = 65
     StudentMarks(5) = 32
     StudentMarks(6) = 28
     StudentMarks(7) = 62
     StudentMarks(8) = 70
     StudentMarks(9) = 68

     ClassAvg = AverageMarks(StudentMarks())

     MsgBox ClassAvg

End Sub

This is the result of the subroutine.

Return value of the function

Note that we have parentheses for the argument of the function.

Function AverageMarks(Marks() As Integer) As Double

However we can omit the parentheses when calling the function. So both the below methods will work.

ClassAvg = AverageMarks(StudentMarks())


ClassAvg = AverageMarks(StudentMarks)

What will happen if we pass non-array to a function which accepts array as the argument. Let’s check this by passing a non-array to the above function. Note that our VBA function has an argument of data type “Integer”. So we are going to pass a value of the same data type.

Sub FindAverageMarksOfOneStudent()

     Dim Student1_Marks As Integer
     Dim ClassAvg As Double

     Student1_Marks = 85

     ClassAvg = AverageMarks(Student1_Marks)

     MsgBox ClassAvg

End Sub

Above subroutine has a variable called “Student1_Marks”. It is a non-array variable of type integer. We assigned the marks of one student to that variable. Then called the function “AverageMarks” using the “Student1_Marks” as the parameter. Here is the outcome.

Get compile error if you pass non-array to a function which requires an array as argument

So you will get a compile error if you pass a non-array to a function which accepts an array as the argument.

Also you can pass an array to function only as ByRef (by reference). Some functions may analyze the array and return a result without doing any change to the array. But some VBA functions make changes to the array as well. So if the called function does any changes to the array, changes affect the original array. I will explain this using an example.

Assume we want a function which returns the third power of given numbers.

Function ThirdPower(Arr() As Long) As Long()

     Dim i As Integer

     For i = LBound(Arr) To UBound(Arr)
         Arr(i) = Arr(i) ^ 3
     Next i

     ThirdPower = Arr

End Function

We can pass numbers as an array. Then the function will return the third power of those numbers. Here is how we can call this function from a subroutine.

Sub TestMacro1()

     Dim MyArr(1 To 5) As Long
     Dim ResultArr() As Long

     MyArr(1) = 2
     MyArr(2) = 3
     MyArr(3) = 7
     MyArr(4) = 8
     MyArr(5) = 10

     ResultArr = ThirdPower(MyArr())

End Sub

Now let’s set a breakpoint at “End Sub” and run the code.

Original array is changed when passed to the function

If you view the arrays in the locals window, you will notice that the “MyArr” is also updated with the third power values. This is because the array is passed to the function by reference. You might think that you can pass the array by value using the ByVal statement. But it is impossible. You will get a syntax error if you try to pass the array by value.


Can not pass an array to a function by value

Also read
How to Return an Array From VBA Function
Fixed Size Arrays in VBA
Multidimensional Arrays in VBA
Dynamic arrays in VBA
Quickly Write Multidimensional Array to Excel Range

Add Horizontal Scrollbar to a Listbox

Today I’m going to discuss about adding a horizontal scrollbar to a listbox. You might think that you can add this from the Properties window of the VBA editor. But it is not. You can’t change a property and add a horizontal scrollbar for a VBA listbox. Here is a sample listbox and available properties in the properties window.

Sample listbox


Properties window of a listbox

As you can see there is no property associated with the horizontal scrollbars. So how do we add one to the listbox? Actually you don’t need to do anything to add a horizontal scrollbar. Scrollbar will be automatically added to the VBA listbox if the space is not enough to show all the columns. But the automatic appearance of the horizontal scrollbar is not smooth. I will show an example of malfunction and explain how to solve it at the end of this post.

Here is an example where scrollbars were added automatically to the listbox.

Private Sub UserForm_Initialize()

     Dim WS_OrderDetails As Worksheet

     Set WS_OrderDetails = Worksheets("Order Details")

     lstOrderDetails.ColumnCount = 7

     lstOrderDetails.List() = WS_OrderDetails.Range("A1:G100").Value

End Sub
Listbox with both vertical and horizontal scrollbars

But in this next example the horizontal scrollbar doesn’t appear even though it is needed. So the user can’t see some of the information.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData() As Variant

     Set WS_Data = Worksheets("Data")

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.List() = WS_Data.Range("A1:C14").Value

End Sub
Horizontal scrollbar of the listbox didn’t appear as expected.

Only the vertical scrollbar is available for the above listbox. So the user can’t see the email addresses listed in the VBA listbox. If you get into this kind of problem, you can fix it in two ways. One method is to increase the width of the listbox. For that sometimes you may need to increase the width of your VBA form as well. However if you are unable to increase width due to design aspect or any other reason then you can follow the second method. Second method is assigning column widths for listbox columns using VBA. You can do it using the ColumnWidths property as follows.

lstNameEmailList.ColumnWidths = "125;125;250"

Then here is the full VBA code of the UserForm_Initialize event.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData() As Variant

     Set WS_Data = Worksheets("Data")

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "125;125;250"

     lstNameEmailList.List() = WS_Data.Range("A1:C14").Value

End Sub

This is the result after the adding the ColumnWidths property.

Horizontal scrollbar added when use the ColumnWidths property

Also Read
Important Things You Should Know When Using Scrollbars in VBA Userforms
Fill a Listbox From an Array


Comment Block of Code in VBA

Did you know that you can comment out block of code in VBA? Yes you can. But VBA’s method is little different from other programming languages. Because other languages like Java, PHP etc. have specific syntax to comment a block of code. But in VBA you can comment and uncomment blocks of codes using two buttons. VBA doesn’t have a dedicated syntax to comment a block of code.

In VBA you can comment a single line of code using the ' (apostrophe mark).

'This is a comment

Then if you want to comment out multiple lines, you have to add apostrophe marks in front of all of those lines.

'This is a comment line 1
'This is a comment line 2
'This is a comment line 3

But other programming languages have a dedicated syntax to comment multiple lines. For example, this is how you can comment a block of code in Java.

java syntax of commenting a single line and a block of code

So if you need to comment multiple lines in your VBA program, adding apostrophes manually is not easy. Luckily as I said above Visual Basic Editor has two buttons to comment and uncomment the lines of codes. Here is how you can do it.

First check whether your Visual Basic Editor already has “Edit” tools in the Toolbar.

Check whether Edit tools are available in the Toolbar

If they are available in the editor then click here to jump to the next step. If they are not available, follow these steps to add them to the Toolbar.

Click on the “View” menu.

Click on the View menu

Take the mouse pointer on top of the “Toolbars”. Then a list of names of toolbars will appear to the right. Put a tick in front of the “Edit”.

Put tick to the Edit toolbar

Then “Edit” tools will be available in the VBA editor.

Edit tools

Once “Edit” tools are available, you can use these two buttons to comment and uncomment a block of codes.

Comment and Uncomment buttons

Now let’s look at how to comment and uncomment a block using these two buttons. First select the section you want to comment out.

Select the section you want to comment out

Then click the “Comment Block” button.

The section commented out by Block Comment button

Similarly if you want to uncomment a section, first select that section and then click on the “Uncomment Block” button.

Fill a Listbox From an Array

In this lesson you will learn how to fill a Listbox from an array. Listboxes are a very useful control type available in the VBA toolbox. You can use the listboxes for search and view functions of the VBA programs. First let’s fill the listbox using a multidimensional array. After that we can try it with a one dimensional array. This is the sample Excel sheet I’m going to use for this lesson.

Sample data in an Excel worksheet

And here is the listbox we are going to fill with the array.

Listbox which we are going to fill with array

So we have data in three columns and fourteen rows. First, we are going to add this data to an array. After that we can add the array to our listbox in the VBA form. You might wonder why we need to add the data to an array. Because data can be directly added to the listbox from the worksheet using VBA. Yes it is possible. But here our objective is to learn how to add data to a listbox from an array. Because there are situations where we need to add the data which is not available in worksheets to listboxes using VBA. For example you might add data from a worksheet to one array. Then search through the elements of that array and create another array from the search results. Now you may want to show this second array in a listbox. So then you have to fill a listbox directly from an array using VBA. There can be various examples like this. But to make this lesson easy to understand, I’m going to use this simple example.

Here what we are going to do is we are going to show the data in the listbox when the VBA form is loaded. So we can add the VBA code to the UserForm_Initialize event.

Private Sub UserForm_Initialize()


End Sub

First we need to declare a few variables. Let’s assume the name of the worksheet is “Data”.

Dim WS_Data As Worksheet
Dim AllData(1 To 14, 1 To 3) As String
Dim i As Integer
Dim j As Integer

Here I have declared an array of the type string. Because I have only string type data in the Excel range. So you need to declare your array according to the type of data you have. If the data contains different data types then you should declare the array as type variant.

Next, assign the Excel sheet to the WS_Data variable.

Set WS_Data = Worksheets("Data")

Now we can add the data from the worksheet to the array. There are a few different ways to do this. Here is one method.

For i = 1 To 14
     For j = 1 To 3
         AllData(i, j) = WS_Data.Cells(i, j).Value
     Next j
Next i

This post explains a quicker way to create an array from an Excel range.
Quick Multidimensional Array from Excel Range

Next step is to set the column count. If you don't set the column count then the listbox will have only one column. Assume the name of the listbox is “lstNameEmailList”. In the conventional naming method we add “lst” in the beginning of the listbox name.

lstNameEmailList.ColumnCount = 3
Now we can assign width for each column. This step is optional. If you skip this step all the columns will have the same width.
lstNameEmailList.ColumnWidths = "100;100;250"

Finally we can add the array to the listbox using the “List” method as follows.

lstNameEmailList.List() = AllData

Below is the full VBA code of the UserForm_Initialize event.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData(1 To 14, 1 To 3) As String
     Dim i As Integer
     Dim j As Integer

     Set WS_Data = Worksheets("Data")

     For i = 1 To 14
         For j = 1 To 3
             AllData(i, j) = WS_Data.Cells(i, j).Value
         Next j
     Next i

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "100;100;250"

     lstNameEmailList.List() = AllData

End Sub

Now data will be shown in the listbox when the userform is loaded.

Listbox is filled with the data from the array

So we learnt how to fill a listbox from a multidimensional array in VBA. Next let’s try to fill this listbox with a one dimensional array.

Assume we have a one dimensional array like this.

Dim OneEmployeeInfo(1 To 3) As String

OneEmployeeInfo(1) = "Cathrine"
OneEmployeeInfo(2) = "Wintour"
OneEmployeeInfo(2) = "cathrinewintour@example.com"
One dimensional array

We can add this array to our listbox easily inside the UserForm_Initialize event as follows.

Private Sub UserForm_Initialize()

     Dim OneEmployeeInfo(1 To 3) As String

     OneEmployeeInfo(1) = "Cathrine"
     OneEmployeeInfo(2) = "Wintour"
     OneEmployeeInfo(3) = "cathrinewintour@example.com"

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "100;100;250"

     lstNameEmailList.List() = OneEmployeeInfo

End Sub

Below is the result you will get when the VBA form is shown.

Listbox is filled with the data from a one dimensional array

Here the data is listed in one column even though I have set the column count as 3. I faced a similar problem when I developed a VBA application recently. That program had a class module function which returns an array. Sometimes it returns a multidimensional array and sometimes a one dimensional array. Then the data of this returned array was shown to the user through a listbox. But as in above, listbox showed the data in a single column when the class module returned a one dimensional array. But I wanted to show the data horizontally when there is one row.

So how can we solve this problem? How to show the data horizontally in multiple columns instead of in a one column? For that you have to convert the one dimensional array to a multidimensional array. You can follow below steps to convert a one dimensional array to a multidimensional array in VBA.

Private Sub UserForm_Initialize()

     Dim OneEmployeeInfo(1 To 3) As String

     OneEmployeeInfo(1) = "Cathrine"
     OneEmployeeInfo(2) = "Wintour"
     OneEmployeeInfo(3) = "cathrinewintour@example.com"

     Dim OneEmp_Multidimensional_Arr(1 To 1, 1 To 3) As String

     For i = 1 To 3
         OneEmp_Multidimensional_Arr(1, i) = OneEmployeeInfo(i)
     Next i

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "100;100;250"

     lstNameEmailList.List() = OneEmp_Multidimensional_Arr

End Sub
Multidimensional array

Now when we show the VBA form using the form.show method, the listbox will be filled with the data like this.

Listbox is filled with the data from the multidimensional array

Want to learn more about arrays? Then check these posts.
Fixed Size Arrays in VBA
Multidimensional Arrays in VBA
Dynamic arrays in VBA
Calculate With Arrays

Contact Form

Name

Email *

Message *