CODEWITHSUNDEEP
excelvba
Alex Wilding
Alex Wilding

Reputation: 167

Excel VBA. Send the datatype of a variable as a variable to a sub

I've simplified my question to make it as generic as possible.

Say I would like to create a Sub, which takes a collection, and a datatype of objects expected to be contained in that collection, and prints a specified parameter to the immediate window. The sub would need to take as inputs a collection (e.g. ThisWorkbook.Worksheets), a datatype (e.g. Worksheet), and I would think optionally a property (e.g. Name)

The sub would look something like:

Sub PrintMembers(ByVal myCol as Collection, ByVal datatype as ????, ByVal property as ????)
  dim myObj as datatype?
  for each  in myCol
    debug.print myObj.property?
  next
End Sub

Which I could then call with:

Call PrintMembers(ThisWorkbook.Worksheets, Worksheet, "Name")

or something along those lines, which would output:

Sheet1
Sheet2
Sheet3

Upvotes: 1

Views: 31

Answers (1)

Ambie
Ambie

Reputation: 4977

I'm not exactly sure what you're asking but it seems as though you might be looking for the CallByName() function:

Public Sub RunMe()
    PrintMembers ThisWorkbook.Worksheets, "Name"
End Sub

Private Sub PrintMembers(col As Object, prop As String)
    Dim item As Object

    For Each item In col
        Debug.Print CallByName(item, prop, VbGet)
    Next
End Sub

Upvotes: 1

Related Questions