VBA Combobox and Listbox common superclass
I am trying to create a function for excel-VBA. This function is used to calculate the index of a certain string in a combobo开发者_如何学Pythonx or listbox. I would like the function to have the following signature:
Public Function MyIndexOf(list As ???, str As String) As Integer
My question is if there is a common superclass to Combobox and Listbox so i can replace ??? with some class.
Maybe there is another way of doing IndexOf. Suggestions are welcome but i want it to be done in a common separate function.
Anyone have a suggestion on how to solve this?
Thanks in advance /Patrik
There is no inheritance in VBA (or VB 6), so unfortunately there's no such thing as a superclass.
You do, however, have the benefit of very loose typing. You can declare the variable as a Variant
, which is a universal data type that can hold any type of value, including a control. Generally, you should try and avoid using the Variant
type if at all possible, but in this case, you have little choice.
So you could declare your function like this:
Public Function MyIndexOf(list As Variant, str As String) As Integer
' Do work here...
End Function
Of course, remembering that a Variant
data type can really hold any type of value, and that the type is not known until run-time, a good defensive programmer would check to make sure that the specified type is not an Integer
or a String
or even a control that doesn't expose the same public members as a ComboBox
or ListBox
control (thus breaking the code you wrote expecting one that does).
You can determine the actual type of a Variant
at runtime using the TypeName
function. So you could simply modify the above function to include a guard clause to protect against anything other than a ComboBox
or ListBox
control being passed in:
Public Function MyIndexOf(list As Variant, str As String) As Integer
If (Not TypeName(list) = "ComboBox") And (Not TypeName(list) = "ListBox") Then
MsgBox "Wrong type of list variable was specified."
Else
' Do work here...
End If
End Function
精彩评论