excelvbafunction

Populating a ComboBox.List inside a Public Function returns "Subscript out of range"

I need to popuplate a ComboBox.List and until now I am doing it with the following code, which is located inside a Private Sub and it is working properly:

Private Sub CommandButton_Click()

Dim MyTable As ListObject
Dim myArray As Variant

'Call AutoCompile_List(myComboBox, "Table", "PowerQuery")

Set MyTable = ThisWorkbook.Sheets("Table").ListObjects("PowerQuery")
myArray = MyTable.DataBodyRange
myComboBox.List = myArray

End Sub

Since I need to perform this action multiple times, I wanted to move it inside the following Public Function:

Public Function AutoCompile_List(ComboBox As Object, ExcelSheetName As String, ExcelQueryName As String)

Dim MyTable As ListObject
Dim myArray As Variant

'Automatically populate ComboBox.List
Set MyTable = Application.ThisWorkbook.Sheets("'" & ExcelSheetName & "'").ListObjects("'" & ExcelQueryName & " '")
myArray = MyTable.DataBodyRange
ComboBox.List = myArray

End Function

However, when I run the code I get the Run-time error '9': "Subscript out of range" and I cannot understand why. Could you please help me to fix it?

Thank you in advance for you time and support!

Solution

  • When you use a name (like a worksheet name) in an Excel formula and this name contains a blank, you need to put single quotes around the name. This is just so that the formula parser knows that the name doesn't end at the space (a space is usually a separator between different parts of a formula).

    A fixed string in VBA code is enclosed with double quotes ("Table"). This is to tell the VBA parser that the piece within quotes is a string and not code (like a variable or a function).

    But if you have the string already put into a variable (or parameter), the VBA parser don't need such help. It will simply use the content of the variable and, in your case, use this as index into the Sheets collection resp. the ListObjects collections. So additionally quotes makes no sense, they will be seen as part of the name: Sheets("'" & ExcelSheetName & "'") will not look for the sheet Table but for the sheet 'Table' which doesn't exist.

    So simply write

    Set MyTable = Application.ThisWorkbook.Sheets(ExcelSheetName).ListObjects(ExcelQueryName)