Adding controls to a VBA Collection

[Nunzio]

I am trying to add specific form controls to a VBA collection, so that
I can pass the entire collection to another function, and access the
individual controls later. I have tried several different syntax
variations. In each case, the receiving function produces the error
'Object Required' when I try to access any property of a control in
the collection. If I reference a collection item in the debugger
using its ordinal position, I see the value of the control. So, on
the surface at least, it appears that I am only adding the control's
'value' to the collection, not the control itself; or maybe my syntax
for retrieving the control's properties is at fault. Any help would
be greatly appreciated.

Dim colControls As Collection
Dim ctl As Control

Set colControls = New Collection

'I've tried this
Set ctl = Me.txtDosage
colControls.Add (ctl)

'And this
colControls.Add Me.controls("txtLabel")

'And this
colControls.Add Me.txtLabel
'The code to receive the collection look like this
Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, _
colControls As Collection) As Boolean

Dim ctl As Control

'Error occurs on this line. If I look at ctl(1) I can see the
control's
'value, but I can't access any of it's properties
For Each ctl In colControls

If ctl.Text <ctl.OldValue Then...

[Neil]

"Nunzio" <[e-mail] in message
news:c9**********************************@n20g2000 hsh.googlegroups.com...

>I am trying to add specific form controls to a VBA collection, so that
I can pass the entire collection to another function, and access the
individual controls later. I have tried several different syntax
variations. In each case, the receiving function produces the error
'Object Required' when I try to access any property of a control in
the collection. If I reference a collection item in the debugger
using its ordinal position, I see the value of the control. So, on
the surface at least, it appears that I am only adding the control's
'value' to the collection, not the control itself; or maybe my syntax
for retrieving the control's properties is at fault. Any help would
be greatly appreciated.

Dim colControls As Collection
Dim ctl As Control

Set colControls = New Collection

'I've tried this
Set ctl = Me.txtDosage
colControls.Add (ctl)

'And this
colControls.Add Me.controls("txtLabel")

'And this
colControls.Add Me.txtLabel
'The code to receive the collection look like this
Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, _
colControls As Collection) As Boolean

Dim ctl As Control

'Error occurs on this line. If I look at ctl(1) I can see the
control's
'value, but I can't access any of it's properties
For Each ctl In colControls

If ctl.Text <ctl.OldValue Then...

Just use an array. Define an array of controls, as follows:

Dim arrControls(10) As Control

Replace 10 with whatever number, or leave blank for a dynamic array.

Then, in your function, define the parameter using a variant type, as
follows:

Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, arrControls As Variant) As Boolean

Use the UBound() function to get the upper bound of the array if you use a
dynamic array. And you're done!

Neil

[Nunzio]

On Nov 29, 8:40 pm, "Neil" <[e-mail]:

"Nunzio" <[e-mail] in message

news:c9**********************************@n20g2000 hsh.googlegroups.com...

I am trying to add specific form controls to a VBA collection, so that
I can pass the entire collection to another function, and access the
individual controls later. I have tried several different syntax
variations. In each case, the receiving function produces the error
'Object Required' when I try to access any property of a control in
the collection. If I reference a collection item in the debugger
using its ordinal position, I see the value of the control. So, on
the surface at least, it appears that I am only adding the control's
'value' to the collection, not the control itself; or maybe my syntax
for retrieving the control's properties is at fault. Any help would
be greatly appreciated.

Dim colControls As Collection
Dim ctl As Control

Set colControls = New Collection

'I've tried this
Set ctl = Me.txtDosage
colControls.Add (ctl)

'And this
colControls.Add Me.controls("txtLabel")

'And this
colControls.Add Me.txtLabel

'The code to receive the collection look like this
Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, _
colControls As Collection) As Boolean

Dim ctl As Control

'Error occurs on this line. If I look at ctl(1) I can see the
control's
'value, but I can't access any of it's properties
For Each ctl In colControls

If ctl.Text <ctl.OldValue Then...

Just use an array. Define an array of controls, as follows:

Dim arrControls(10) As Control

Replace 10 with whatever number, or leave blank for a dynamic array.

Then, in your function, define the parameter using a variant type, as
follows:

Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, arrControls As Variant) As Boolean

Use the UBound() function to get the upper bound of the array if you use a
dynamic array. And you're done!

Neil

Thanks Neil. That hadn't occurred to me.

I've been trying different approaches, and I've found that by using a
scripting dictionary object instead of a collection, the technique
that I've been using now works. I like your idea better though.

-Dean

[Neil]

"Nunzio" <[e-mail] in message
news:f1**********************************@s19g2000 prg.googlegroups.com...

On Nov 29, 8:40 pm, "Neil" <[e-mail]:

>"Nunzio" <[e-mail] in message

news:c9**********************************@n20g200 0hsh.googlegroups.com...

>I am trying to add specific form controls to a VBA collection, so that
I can pass the entire collection to another function, and access the
individual controls later. I have tried several different syntax
variations. In each case, the receiving function produces the error
'Object Required' when I try to access any property of a control in
the collection. If I reference a collection item in the debugger
using its ordinal position, I see the value of the control. So, on
the surface at least, it appears that I am only adding the control's
'value' to the collection, not the control itself; or maybe my syntax
for retrieving the control's properties is at fault. Any help would
be greatly appreciated.

Dim colControls As Collection
Dim ctl As Control

Set colControls = New Collection

'I've tried this
Set ctl = Me.txtDosage
colControls.Add (ctl)

'And this
colControls.Add Me.controls("txtLabel")

'And this
colControls.Add Me.txtLabel

'The code to receive the collection look like this
Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, _
colControls As Collection) As Boolean

Dim ctl As Control

'Error occurs on this line. If I look at ctl(1) I can see the
control's
'value, but I can't access any of it's properties
For Each ctl In colControls

If ctl.Text <ctl.OldValue Then...

Just use an array. Define an array of controls, as follows:

Dim arrControls(10) As Control

Replace 10 with whatever number, or leave blank for a dynamic array.

Then, in your function, define the parameter using a variant type, as
follows:

Public Function fcnLogAudit(lngRecordType As enuRecordType,
lngRecordKey As Long, arrControls As Variant) As Boolean

Use the UBound() function to get the upper bound of the array if you use
a
dynamic array. And you're done!

Neil

Thanks Neil. That hadn't occurred to me.

I've been trying different approaches, and I've found that by using a
scripting dictionary object instead of a collection, the technique
that I've been using now works. I like your idea better though.

-Dean

Great! Glad it worked for you.

Neil