Variant vs. Control type in "For Each..." statement

midfde
midfde used Ask the Experts™
on
Please see the attached image and explain the results - the first "Print" request succeds, and the second "For Each..." (not "Debug.Print") raises error.
-.bmp
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Environment: Windows XP + MS Office 2003

db11.mdb
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>Dim ctlC as Contrl, ctl
In the above line, ctl will be Dimmed as a Variant, since it's not explicitly stated.

Also, since this is a form, if you are trying to iterate through all controls, then here's how you do it.

Dim ctl as Control

For Each ctl in Me.Controls
    debug.print "ctl: " & ctl.Name
    ctl.DoYourStuffHere
Next

In your code, I don't see ctlC set to anything, which would explain the error.

Author

Commented:
I do not want to iterate through all 40 controls but rather
    With Me
        For Each ctl In Array(.ctl1, .ctl2,.ctl15, .ctl37)
           ctl.visible = false
        Next ctl
    End With
The above code works when ctl is a variant, and it raises error at "for Each..." statement when ctl is of Control type.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Not sure if the Array is correcty set up as a container, which contains a collection of controls.

If all you are working with is four controls, then...

With Me
  .ctl1.Visible = False
  .ctl2.Visible = False
  .ctl15.Visible = False
  .ctl37.Visible = False
End With

Author

Commented:
JimHorn, thanks for your attention.
I do not want to repeat "Visible" and "False" 20 - 25 times in many places of my code, but rather tell: "Make invisible the follwing guys:....".
"Not sure if the Array..." - this is what my question is all about: Why is the construction working when ctl is a variant and is not when it is of type Control? Is there a hint in MS Access documentation that might explain the difference?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
midfde,

From what I can remember from school, VBA  does not support Control Arrays.
(VB6, Yes , but not VBA)

So in your case, by using a Variant you are literally forcing VB to use a control as a Variant.
So your "controls" were not really controls at all, the were "Variants"
(You basically just kept the name "control", but you actually changed them into Variants)
And we all know that a variant can change it's data type.
Variants can be a: String, Long, Date, Currency, ...ect, all of which can be inserted into and array,   ...except Controls.

This is why you get the Error when you try to use a DataType of Control,... it can't be done.
;-)

I did some Google-ing, and the following links all seem to support this claim:

http://www.vbforums.com/archive/index.php/t-275864.html
http://www.tek-tips.com/faqs.cfm?fid=4085
http://www.vbforums.com/showthread.php?t=235463
http://proofficedev.com/blog/2007/02/26/control-arrays-in-vba/

If you are really concerned about speed, then consider the follow two code snippets.
The first snippet loops through the controls in the standard way for VBA, and it is 160SLOC
Your example does the exact same thing and is 227SLOC
Your code will also grow with every element you add to the array.
The standard code will always remain the same.
Even if you add conditions like: If ctl.BackColor=vbred, IF ctl.value=12, If ctl.ControlType=acCombobox...
... It is possible for the standard code to still be shorter.
This example uses you "simulated" control array


Private Sub Command0_Click()
'Standard VBA Control Loop
Dim ctl As Control

For Each ctl In Me
    If ctl.ControlType = acOptionButton Then
        ctl.Visible = False
    End If
Next ctl
End Sub

Private Sub Command21_Click()
'Simulated  Control Array
Dim ctl As Variant
   
    With Me
        For Each ctl In Array(.ctl1, .ctl2, .ctl3, .ctl4, .ctl5, .ctl6, .ctl7, .ctl8, .ctl9, .ctl10)
           ctl.Visible = False
        Next ctl
    End With

End Sub

So if speed is your major concern, why not use the VBA method?

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...And it seems that Control Arrays are not supported in VB.net either
http://msdn.microsoft.com/en-us/library/kxt4418a(VS.80).aspx

JeffCoachman

Author

Commented:
Sounds earnestly, but...
Please see the results of my slightly modified code. Is it consistent with "does not support array of controls" please?
-.bmp
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
midfde

To avoid confusion it would be much cleaner if you dimensioned all of your variable explictly.

Dim arrCtls As InsertExplicitDataTypeHere
Dim ctlC      As Control
Dim ctl        As InsertExplicitDataTypeHere

But, again, all I can say is that from memory and the documentation I have seen, inserting explict Control Datatypes into Arrays in VBA is not possible.

Is there a reason why you have to use arrays specifically?

Thanks

JeffCoachman


Author

Commented:
Documentation is quit clear about the following two features of VBA:
1) Array(..) function:
Returns a Variant containing an array.
2)Dim statement:
If you don't specify a data type or object type, and there is no Deftype statement in the module, the variable is Variant by default.

So what kind of "confusion" one may expect from a few line of code with three variables defined?

I prefer this code
  With Me
    For Each ctl In Array(.a, .b, .c, .d, .e, .f, .g..., .q)
         ctl.Visible = (X Or (Y And Z)) And Not W
    Next ctl
  End With
to its trivial alternative.
Thanks for your patience, Jeff.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
midfde,

From what I have always seen and read, not explictly defining you datatypes is generaly frowned upon.
Defining your datatypes is easier to read for someone who is used to seeing declared.
Lastly a lot of developers wrongly assume that if you do this:
    Dim MyVariable1, MyVariable2, MyVariable3 As Double
...that they are all Doubles, when in fact only MyVariable3 is a double, all the rest are Variants.
I personally always try to code for clarity and I seek to avoid abiguity.

I understand that a lot of developers who have been doing this for a while like yourself understand it, but at some point, you may not be the only person looking at your code.

Lastly in VB.Net (when you are forced to upgrade ;->  ), you can't get away with this.
(But you can reseach that on your own)...

But back to the original question...

"Why is the construction working when ctl is a variant and is not when it is of type Control? Is there a hint in MS Access documentation that might explain the difference?"

I believe I have provided enough documentation to show that this may not be possible.

So,...
Question answered?
;-)

JeffCoachman

Author

Commented:
Alse see the attached image please.
-.bmp
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Just a thought here....

Sometime in the future, after you ditch your present employer and move on to a bigger paycheck, is the poor schlep that inherits your code going to understand what you're trying to do with this control array?

(1)  Code comments.  Now.
(2)  Explicitly declare EVERY variable (covered by boag)
(3)  Naming variables (X Or (Y And Z)) And Not W?  
      Unless this is air code, that's about as intuitive as JoanCrawford, AtomicTangerine, NeonBanana, and LizardsRUs
(4)  Form1 and Command0?  See above comment.  Better would be frmOrder, frmInvoice, cmdCustomer, cmdLocation, etc.

Hugs and kisses.
Jimbo

Author

Commented:
Oh, this is a blueprint of real code. It is for illustrative purpose only, to highlight the subject of discussion.
For instance instead of X in real code something like
(oProject.IsLoaded And oProject.IsDedicated)
might have appeared.

Author

Commented:
to: JeffCoachman
>> Lastly in VB.Net (when you are forced to upgrade)...
My language is
    string a,b /* may not be 0*/,c.d;
and
    x = (d == 0 ? 0 : 5 / d);
not
   Dim a as String, b as String ...
and
   if d = 0 then
     x = 0
   else
    x = 5 / d
  end if
Question is not answered, but I'd like to award you with the points. How do I do that, please?
MIS Liason
Most Valuable Expert 2012
Commented:
midfde

I thought the question was answered?

But I will simply repost for re-inforcement.

Q:
 "this is what my question is all about: Why is the construction working when ctl is a variant and is not when it is of type Control? "

A:
 VBA  does not support Control Arrays.
So in your case, by using a Variant you are literally forcing VB to use a control as a Variant.
So your "controls" were not really controls at all, they were "Variants"
(You basically just kept the name "control", but you actually changed them into Variants)
And we all know that a variant can change it's data type.
Variants can be a: String, Long, Date, Currency, ...ect, all of which can be inserted into and array,   ...except Controls.
(In VBA)

This is why you get the Error when you try to use a DataType of Control,... it can't be done.
;-)

I did some Google-ing, and the following links all support this claim:

http://www.vbforums.com/archive/index.php/t-275864.html
http://www.tek-tips.com/faqs.cfm?fid=4085
http://www.vbforums.com/showthread.php?t=235463
http://proofficedev.com/blog/2007/02/26/control-arrays-in-vba/


If you wish to Accept one of my posts simply click the "Accept" button for the appropriate post.

Thanks
;-)

JeffCoachman

Author

Commented:
>> VBA  does not support Control Arrays.

Did you notice 6-elemens array of CONTROLs arrCtl above?


>>VB to use a control as a Variant

???

This is why you...

???
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
midfde,

(What do the question marks in you last post signify?, I am having trouble deciphering?)

Final notes:

"Did you notice 6-elemens array of CONTROLs arrCtl above?"
Well, ...actually I see where you Declared arrCtl as CONTROL.
(But whether it actually IS a "Control Array" is the real question)
Because I could technically to this:
Dim arrJeff(7) As DAO.Database
(Which makes absolutely no sense, but at the same time, it will not throw an error)
;-)

So let's move on...

1. Going back through your logic, arrCtl(1) has been assigned (Set) to Command0, which is why it works fine.
2. As far as I can tell, you never "Set" arrCtl(2) to anything (but a member of the original "array".)
3. arrCtl(3) works fine because, again,  it is ultimately assigned to command0 as well.

Besides arrCtl(2) Prints, "Nothing" which is obviously true, because you never set it to anything, so I don't really know what that proves?
In my example using arrJeff(7), ...Debug.Print returned "Nothing" as well.

You can "Declare" an Array as any DataType you like:
Dim Array1(5) as Control
Dim Array2(5) as CommandButtom
Dim Array3(5) as DAO.Database
Dim Array4(5) as  MoonBurger
Dim Array5(5) as Currency

But you can't always *Use* that array or the elements therein, AS an array.
That is the point.

Even in your "debug.Print" code, you are not really "Doing" anything with the Array elements.
And even when you tried:
    With Me
        For Each ctl In Array(.ctl1, .ctl2,.ctl15, .ctl37)
           ctl.visible = false
        Next ctl
    End With
...You got an error!

Which again, lends proof that it can't be done.

If your question is "Why can't you use a Control Array in VBA?", you will have to search the Microsoft site. I found no explanation.
:-(
Again, you can use them in VB6 but not in VBA.
Go figure.
;-)

What *does* make sense is a statement here:
https://forums.microsoft.com/msdn/ShowPost.aspx?PostID=2305004&SiteID=1
    "You can create an array of controls but you can't create a control array."

Lastly, can you direct me to any VBA code that says this *Can* work?

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
FWIW,

Here is not just a screenshot, but my actual database I used in my above post.

JeffCoachman
Access-EEQ23593296VBAControlArra.mdb

Author

Commented:
Makes no sense?
Absolutely?

Option Compare Database
Option Explicit
Sub test()
Dim arrJeff(7) As DAO.Database, i As Integer, rs As Recordset
'(Which makes absolutely no sense, but at the same time, it will not throw an error)
';-)
For i = 1 To 5
    Set arrJeff(i - 1) = OpenDatabase("db" & i & ".mdb")
    Set rs = arrJeff(i - 1).OpenRecordset("select count(*)from msysobjects")
    Debug.Print i & ")" & rs(0)
    rs.Close
    Set rs = Nothing
Next i
'five databases are open
For i = 1 To 5
    arrJeff(i - 1).Close
Next i
Debug.Print arrJeff(0) Is Nothing, arrJeff(1) Is Nothing, arrJeff(6) Is Nothing
End Sub
#If False Then
1)36
2)17
3)17
4)23
5)19
False         False         True
#End If
I put question marks in my earlier message in places whose relevance seems questionable to me as well as the above example though....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial