Option
Explicit
Private
Type MY_WORKSHEETS
objWorksheet1
As
Worksheet
objWorksheet2
As
Worksheet
objWorksheet3
As
Worksheet
End
Type
Public
Sub
test1(
ByRef
probjWorksheets
As
Sheets)
Dim
objWorksheet
As
Worksheet
For
Each
objWorksheet
In
probjWorksheets
MsgBox objWorksheet.Name
Next
End
Sub
Public
Sub
test2(
ByVal
pvavntArray
As
Variant
)
Dim
ialngIndex
As
Long
For
ialngIndex = 0
To
UBound(pvavntArray)
MsgBox Worksheets(pvavntArray(ialngIndex)).Name
Next
End
Sub
Public
Sub
test3(
ByVal
pvavntArray
As
Variant
)
Dim
ialngIndex
As
Long
For
ialngIndex = 1
To
Worksheets(pvavntArray).Count
MsgBox Worksheets(pvavntArray)(ialngIndex).Name
Next
End
Sub
Public
Sub
test4(
ParamArray
ppavntArray()
As
Variant
)
Dim
ialngIndex
As
Long
For
ialngIndex = 0
To
UBound(ppavntArray)
MsgBox Worksheets(ppavntArray(ialngIndex)).Name
Next
End
Sub
Public
Sub
test4b(
ParamArray
ppavntArray()
As
Variant
)
Dim
ialngIndex
As
Long
For
ialngIndex = 0
To
UBound(ppavntArray)
MsgBox ppavntArray(ialngIndex).Name
Next
End
Sub
Public
Sub
test5(
ByRef
prudtWorksheets
As
MY_WORKSHEETS)
With
prudtWorksheets
MsgBox .objWorksheet1.Name & vbCr & _
.objWorksheet2.Name & vbCr & .objWorksheet3.Name
End
With
End
Sub
Public
Sub
test6(
ByRef
prcolWorksheets
As
Collection)
Dim
lngIndex
As
Long
For
lngIndex = 1
To
prcolWorksheets.Count
MsgBox prcolWorksheets(lngIndex).Name
Next
End
Sub
Public
Sub
test7(
ByRef
praobjWorksheets()
As
Worksheet)
Dim
ialngIndex
As
Long
For
ialngIndex = 0
To
UBound(praobjWorksheets)
MsgBox praobjWorksheets(ialngIndex).Name
Next
End
Sub
Public
Sub
aufrufe1()
Call
test1(Worksheets(Array(
"Tabelle1"
,
"Tabelle2"
,
"Tabelle3"
)))
Call
test2(Array(
"Tabelle1"
,
"Tabelle2"
,
"Tabelle3"
))
Call
test3(Array(
"Tabelle1"
,
"Tabelle2"
,
"Tabelle3"
))
Call
test4(
"Tabelle1"
,
"Tabelle2"
,
"Tabelle3"
)
Call
test4b(Tabelle1, Tabelle2, Tabelle3)
End
Sub
Public
Sub
aufrufe2()
Dim
colWorksheets
As
Collection
Dim
udtWorksheets
As
MY_WORKSHEETS
Dim
aobjTempSheets(2)
As
Worksheet
With
udtWorksheets
Set
.objWorksheet1 = Tabelle1
Set
.objWorksheet2 = Tabelle2
Set
.objWorksheet3 = Tabelle3
End
With
Call
test5(udtWorksheets)
Set
colWorksheets =
New
Collection
With
colWorksheets
Call
.Add(Item:=Tabelle1)
Call
.Add(Item:=Tabelle2)
Call
.Add(Item:=Tabelle3)
End
With
Call
test6(colWorksheets)
Set
colWorksheets =
Nothing
Set
aobjTempSheets(0) = Tabelle1
Set
aobjTempSheets(1) = Tabelle2
Set
aobjTempSheets(2) = Tabelle3
Call
test7(aobjTempSheets())
Erase
aobjTempSheets
End
Sub