Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Array Initialization (VBA / VB / any)

    How do you initialize an array of constants so that the code is readable? This old Fortran programmer wants a Data statement! <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Initialization (VBA / VB / any)

    I usually do it like this:

    <pre>Dim MenuItems As Variant
    MenuItems = Array(18, 23, 106, 3, 748, 831, 752, 19, 22, 755, _
    30020, 1849, 793, 303, 298, 830, 984, 983, 124, 3022, 936, 927)
    </pre>

    Legare Coleman

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

    Re: Array Initialization (VBA / VB / any)

    Did Legare's post answer your question, Sam? I'm still trying to figure out what an array of constants might be used for. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Array Initialization (VBA / VB / any)

    > I'm still trying to figure out what an array of constants might be used for.

    I use it for storing different language equivalents that I use for captions in dialog boxes.

    StuartR

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Initialization (VBA / VB / any)

    Here is some more of the code that I posted earlier to show how I was using it:

    <pre>Dim WsMenu As CommandBar, MenuItem As Object, MenuControl As Object
    Dim MenuItems As Variant, Caption As Variant, KeepID As Variant
    Dim DeleteMenuItem As Boolean
    Set WsMenu = CommandBars("Worksheet Menu Bar")

    MenuItems = Array("&File", "&Edit", "&Tools", "&Window", "&Help")
    For Each MenuItem In WsMenu.Controls
    DeleteMenuItem = True
    For Each Caption In MenuItems
    If MenuItem.Caption = Caption Then
    DeleteMenuItem = False
    Exit For
    End If
    Next Caption
    If DeleteMenuItem Then MenuItem.Delete
    Next MenuItem

    MenuItems = Array(18, 23, 106, 3, 748, 831, 752, 19, 22, 755, _
    30020, 1849, 793, 303, 298, 830, 984, 983, 124, 3022, 936, 927)
    For Each MenuItem In WsMenu.Controls
    For Each MenuControl In MenuItem.Controls
    DeleteMenuItem = True
    For Each KeepID In MenuItems
    If MenuControl.ID = KeepID Then
    DeleteMenuItem = False
    Exit For
    End If
    Next KeepID
    If DeleteMenuItem Then MenuControl.Delete
    Next MenuControl
    Next MenuItem
    </pre>

    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Array Initialization (VBA / VB / any)

    Guess I should have asked if anyone had a good alternative to using the array function. The way Legare did the examples is the way I normally do it, but it seems to me to be very ugly for two reasons: (1) you have to use a variant datatype; (2) it is not obvious which index refers to which item in the array.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Initialization (VBA / VB / any)

    This fixes both of those objections:

    <pre>Dim iMenuItems(22) As Integer, iItem As Variant, I As Integer
    I = 0
    For Each iItem In Array(18, 23, 106, 3, 748, 831, 752, 19, 22, 755, _
    30020, 1849, 793, 303, 298, 830, 984, 983, 124, 3022, 936, 927)
    iMenuItems(I) = iItem
    I = I + 1
    Next iItem
    </pre>

    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Array Initialization (VBA / VB / any)

    Both? Well, the variant at least: I like the for each loop, but it is still not obvious that iMenuItems(6) = 752.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Initialization (VBA / VB / any)

    The only way I know to make it more obvious is like this:

    <pre>Dim iMenuItems(10) As Integer
    iMenuItems(0) = 18
    iMenuItems(1) = 23
    iMenuItems(2) = 106
    iMenuItems(3) = 3
    iMenuItems(4) = 748
    iMenuItems(5) = 831
    iMenuItems(6) = 752
    iMenuItems(7) = 19
    iMenuItems(8) = 22
    iMenuItems(9) = 755
    iMenuItems(10) = 30020
    </pre>

    Legare Coleman

Posting Permissions

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