Using The TypeName and TypeOf Methods To Determine Data Type
When it is necessary to test the type of variable passed to a subroutine, there are two methods: TypeName and TypeOf. Both perform a similar function, but the TypeOf uses Intellisense and makes it easy to correctly identify the type of variable. The examples below are functionally the same, but illustrate the use of both methods.
Program Code
' **************************************************************************************
' Illustrate The "TypeName" Command Which Tests The Data Type of a Variable
' **************************************************************************************
Private Sub LoadComboOrListBox(ProfileWorksheet As Worksheet, ColumnNumber As Long, BeginningRow As Long, EndingRow As Long, TargetComboOrListBox As MsForms.Control)
Dim i As Long
If TypeName(TargetComboOrListBox) = "ListBox" Or TypeName(TargetComboOrListBox) = "ComboBox" Then
TargetComboOrListBox.Clear
For i = BeginningRow To EndingRow
TargetComboOrListBox.AddItem (ProfileWorksheet.Cells(i, ColumnNumber))
Next i
Else
MsgBox ("MsForms Type Must Be List or Combo Box")
End If
End Sub
' **************************************************************************************
' Illustrate The "TypeOf" Command Which Tests The Data Type of a Variable
' **************************************************************************************
Private Sub LoadComboOrListBox(ProfileWorksheet As Worksheet, ColumnNumber As Long, BeginningRow As Long, EndingRow As Long, TargetComboOrListBox As MSForms.Control)
Dim i As Long
If TypeOf TargetComboOrListBox Is MSForms.ListBox Or TypeOf TargetComboOrListBox Is MSForms.ComboBox Then
TargetComboOrListBox.Clear
For i = BeginningRow To EndingRow
TargetComboOrListBox.AddItem (ProfileWorksheet.Cells(i, ColumnNumber))
Next i
Else
MsgBox ("MsForms Type Must Be List or Combo Box")
End If
End Sub
