Results 1 to 7 of 7

Thread: Newbie Question: Array traversal fails in VBA under MS Word

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Newbie Question: Array traversal fails in VBA under MS Word

    Hello

    I have to preface this to say that I am a newbie at VBA, but not in programming.

    I have a form which I fill out, but needs initialized values.

    I have multiple text boxes deployed on the form, say: tb1, tb2, tb3, tb4

    I would like to set their text values to show zero (0).

    When I attempt to run the code below, I get an error ("Object required") on the For Each line. Won't vba assign an existing text box to an empty text box variable? Is there another way to do this and save myself a ton of repeated code?

    Code:
       Dim tb
       tb = Array(tb1, tb2, tb3, tb4)
       Dim t As TextBox
       
       For Each t In tb
          t.Value = "0"
       Next t
    The same error on the For Each linealso happens if I make the array an anonymous one:
    Code:
       Dim tb As TextBox
       
       For Each tb In Array(tb1, tb2, tb3, tb4)
          tb.Value = "0"
       Next tb
    Thanks. If there is any help you can give, it would be appreciated.

    Paul
    Last edited by paul.e.king; Jun 14th, 2014 at 04:07 PM.

  2. #2

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Array traversal fails in VBA under MS Word

    I had no edit button until I got onto Firefox. The typo has been fixed in the original post.
    Last edited by paul.e.king; Jun 14th, 2014 at 04:06 PM.

  3. #3
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Newbie Question: Array traversal fails in VBA under MS Word

    maybe this ?
    Code:
    Private Sub CommandButton1_Click()
       Dim ctrl As Control
       For Each ctrl In Me.Controls
          If TypeName(ctrl) = "TextBox" Then
             ctrl.Text = "0"
          End If
       Next
    End Sub

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Newbie Question: Array traversal fails in VBA under MS Word

    OR

    Code:
    Dim tb
       tb = Array("tb1", "tb2", "tb3", "tb4")
       
       For Each t In tb
          me.controls(t).Value = "0"
       Next t
    Last edited by westconn1; Jun 14th, 2014 at 05:20 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Newbie Question: Array traversal fails in VBA under MS Word

    Quote Originally Posted by paul.e.king View Post
    ....When I attempt to run the code below, I get an error ("Object required") on the For Each line. Won't vba assign an existing text box to an empty text box variable? Is there another way to do this and save myself a ton of repeated code? ...
    You made a logical mistake, but a mistake it is.

    Quote Originally Posted by From the Documentation
    For Each...Next Statement

    Repeats a group of statements for each element in an array or collection.

    Syntax

    For Each element In group
    [statements]
    [Exit For]
    [statements]

    Next [element]

    The For...Each...Next statement syntax has these parts:

    Part Description
    element Required. Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.

    group Required. Name of an object collection or array (except an array of user-defined types).
    statements Optional. One or more statements that are executed on each item in group.

    Try:
    Code:
    Dim t As Variant

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Newbie Question: Array traversal fails in VBA under MS Word

    IkkeEnGij, this is fine, except that I have other text boxes that do not need to be initialised that way. I have seen that on other sites.

    Thanks anyway!
    Last edited by paul.e.king; Jun 14th, 2014 at 07:45 PM.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2014
    Posts
    7

    Re: Newbie Question: Array traversal fails in VBA under MS Word

    This one looks better. I will try it. Thanks, Westconn1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width