Results 1 to 8 of 8
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Arrays - passing and scope basics (VBA O97)

    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    I'm lost again, trying to teach myself arrays. In the first sub, the now commented code, when uncommented, works, but when commented out and I try to run the second sub after running the first, or the third and fourth subs to pass the parameter, I don't get what's happening. One book I have explains that array scope is local only to the procedure it's declared in; does this mean I can't have a filled module-level array available to all subs in the module? Is there a way to pass this array as a parameter?

    Option Explicit
    Option Base 1
    Public arrintVisToolBars() As Integer
    Public intCountVisTB As Integer

    Sub ComBars2Array()
    Dim oComBar As CommandBar
    Dim intCounter As Integer
    intCountVisTB = 0
    For Each oComBar In Application.CommandBars
    If oComBar.Visible = True And oComBar.Index > 3 Then
    intCountVisTB = intCountVisTB + 1
    End If
    Next
    ReDim intarrVisToolBars(intCountVisTB)
    intCountVisTB = 0
    For Each oComBar In Application.CommandBars
    If oComBar.Visible = True And oComBar.Index > 3 Then
    intCountVisTB = intCountVisTB + 1
    intarrVisToolBars(intCountVisTB) = oComBar.Index
    End If
    Next
    For intCounter = 1 To intCountVisTB
    Application.CommandBars(intarrVisToolBars(intCount er)).Visible = False
    Next intCounter
    ' MsgBox "Can you see me now?"
    ' For intCounter = 1 To intCountVisTB
    ' Application.CommandBars(intarrVisToolBars(intCount er)).Visible = True
    ' Next intCounter
    End Sub
    ' this doesn't work
    Sub ShowCombar()
    Dim intCounter As Integer
    For intCounter = 1 To intCountVisTB
    Application.CommandBars(intarrVisToolBars(intCount er)).Visible = True
    Next intCounter
    End Sub
    ' my attempt to pass the array as a parameter via this pair doesn't work either
    Sub ShowComBars2()
    CombarCall intarrVisToolBars()
    End Sub
    Sub CombarCall(intarrVisToolBars() As Integer)
    Dim intCounter As Integer
    For intCounter = 1 To intCountVisTB
    Application.CommandBars(intarrVisToolBars(intCount er)).Visible = True
    Next intCounter
    End Sub

    (Also, is there a way to achieve my objective through setting the visible Commandbars as an Object, hiding the object and later unhiding it?)
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays - passing and scope basics (VBA O97)

    I don't mean to be an anti-be-a-proper-programmer kinda guy, but you could always declare that array global and save the headache.

    Just a quick thought without reading all your code.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  4. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Arrays - passing and scope basics (VBA O97)

    You can't pass arrays ByVal as you do other variables, but you can pass an array ByRef. Functions can't return an array, but they can return a variant, and you can declare a variant variable and then assign an array to it. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Arrays - passing and scope basics (VBA O97)

    Tried the code; a couple of notes:

    1. I changed that the Public var "arrintVisToolBars()" to "intarrVisToolBars()" to conform with other uses. If I then add ShowCombar at the end of the ComBars2Array procedure, it works.

    2. The line:

    Application.CommandBars(intarrVisToolBars(intCount er)).Visible = False

    failed when

    MsgBox Application.CommandBars(intarrVisToolBars(intCount er)).Name = "Menu Bar"

    so that would be something to test for.

    Anyway, I agree with the idea of using a global variable, as in item 1, rather than trying to pass the array or some other kind of pointer to the array. It might violate some rules of programming purity, but it seems the most efficient.

  6. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Arrays - passing and scope basics (VBA O97)

    1. Dang! I didn't see that naming error; arrintVisToolBars rather than intarrVisToolBars. Thanks J. And I thought there was some arcane problem that I didn't understand. But does this mean that, in my code above, the interpreter let me Redim a dynamic array that had not already been Dim'ed (since the name was different)? I didn't think that could happen ...

    2. Umm, without testing, I think that the MenuBar index is 1 so the code was OK because it only hides indexes >3, but I'll test further.

    3. Now I have it working, yes, I'll Public the array; at this time all that code is in one module. Charlotte's explanation is a bit over my head, and a Public array is easier to handle.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  7. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Arrays - passing and scope basics (VBA O97)

    Thanks, Kevin. As JScher advised, the scope wasn't the problem, my typing with boxing gloves was. At this time the code is in one module, but when I use it, I'll Public it.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  8. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Arrays - passing and scope basics (VBA O97)

    Thanks, Charlotte. Your response is a bit over my head, so I'll be mulling it over through the weekend. Meantime JScher discovered my primary problem was very simple, and I'll probably use a Public array if this code goes into actual use.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  9. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Arrays - passing and scope basics (VBA O97)

    Actually, using a global or public variable is the simplest way of "passing" an array.

    Passing an array ByRef just means that you declare it in one routine and then pass the pointer to the array you declared into the called routine. That way, anything you do to it in the second routine gets passed back to the first because you're actually working with the array declared there.

    You can declare a variant variable (not as an array) and then populate a local array and assign it as the value of the variant. Then you can return the variant as the return value of the function. Presto, you've passed an array. The routine that gets the return value can immediately treat that as an array.
    Charlotte

Posting Permissions

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