Announcement

Collapse
No announcement yet.

use of ReDim Preserve statement

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • use of ReDim Preserve statement

    I am trying to create a macro in MS Excel using VB 6.0 but I keep getting the error message "Invalid Redim" with the entire line "ReDim Preserve intArrEvenVlans(UBound(intArrEvenVlans) +1)" highlighted. Could anyone help me in resolving the problem:

    Public Sub GenerateConfig()
    ' This subroutine will accumulate data from the various worksheets contained within the request
    ' And use these to create a configuration script for the Core Switches.
    ' Two configuration scripts will be generated, one for each core switch.

    ' Variables
    Dim strSwitchName As String
    Dim intRowCounter As Integer
    Dim strTargetRange As String
    Dim intFrontEndVlan As Integer
    Dim intBackEndVlan As Integer
    Dim intManagementVlan As Integer
    Dim intArrOddVlans(0) As Integer
    Dim intArrEvenVlans(0) As Integer

    ' Switch Name

    ' Collect from B47 on Worksheet 5

    Worksheets(5).Activate
    Range("B47").Activate

    strSwitchName = ActiveCell.Value


    ' Add switch Name to config
    Worksheets(6).Activate
    intRowCounter = 1
    strTargetRange = "A" & intRowCounter
    Range(strTargetRange).Activate
    ActiveCell.Value = strSwitchName

    'Move counter
    intRowCounter = intRowCounter + 3

    ' Spanning Trees
    ' This should collect the front end vlan, management and the back end vlan, then create a spanning tree with the priority
    ' based on whether the VLAN is odd or Even, Odd VLANs will have a spanning tree priority of 8192 and Odd will be 16384.

    ' Collect VLANs
    Worksheets(4).Activate

    Range("E5").Activate
    intFrontEndVlan = ActiveCell.Value
    ActiveCell.Offset(1, 0).Activate
    intBackEndVlan = ActiveCell.Value
    Range("G5").Activate
    intManagementVlan = ActiveCell.Value

    ' For each of the collected vlans, establish if odd or even and then add to correct array...

    'Front End
    AddToVlanArray (intFrontEndVlan)
    'Back End
    AddToVlanArray (intBackEndVlan)
    'Management
    AddToVlanArray (intManagementVlan)

    ' Create spanning tree lines
    Dim strSpanTreeOdd As String
    strSpanTreeOdd = "spanning-tree vlan "

    For i = 0 To UBound(intArrOddVlans)
    If intArrOddVlans(i) <> 0 Then
    strSpanTreeOdd = strSpanTreeOdd & CStr(intArrOddVlans(i)) & ","
    End If
    Next i

    strSpanTreeOdd = Left(strSpanTreeOdd, (Len(strSpanTreeOdd) - 1))

    strSpanTreeOdd = strSpanTreeOdd & " priority 8192"

    MsgBox (strSpanTreeOdd)


    End Sub


    Public Function OddOrEven(ByVal NumToCheck As Integer) As Integer
    If NumToCheck Mod 2 <> 0 Then
    OddOrEven = 1
    Else
    OddOrEven = 0
    End If
    End Function

    Public Sub AddToVlanArray(ByVal Vlan As Integer)
    If OddOrEven(Vlan) = 0 Then
    intArrEvenVlans(UBound(intArrEvenVlans, 1)) = Vlan
    ReDim Preserve intArrEvenVlans(UBound(intArrEvenVlans) + 1) As Integer
    Else
    intArrOddVlans(UBound(intArrOddVlans, 1)) = Vlan
    ReDim Preserve intArrOddVlans(UBound(intArrOddVlans) + 1) As Integer
    End If

    End Sub

  • #2
    This is your problem:
    Dim intArrOddVlans(0) As Integer
    Dim intArrEvenVlans(0) As Integer

    You cannot redim an array that has already been declared with an upper bound, instead:
    Dim intArrOddVlans() As Integer
    Dim intArrEvenVlans() As Integer
    And work from there.
    Furcadia, an interesting online MMORPG in which you can create and program your own content.

    Comment


    • #3
      Hey Colin,

      Thanks for the tip... I just effected that change but unfortunately, the same error still exist.

      You can try it out yourself. Open an excel workbook with 6 worksheets and try to run that macro on the 6th worksheet and you should be able to see the error yourself.

      Cheers.

      Emeka.

      Comment


      • #4
        Colin had half the answer by suggesting you DIM the arrays to:

        Code:
        Dim intArrOddVlans() As Integer
        Dim intArrEvenVlans() As Integer
        But... VB cannot REDIM PRESERVE an array until it has already been REDIMed without the PRESERVE. Insert:

        Code:
        ReDim Preserve intArrOddVlans(0) As Integer
        ReDim Preserve intArrEvenVlans(0) As Integer
        as initialization code after DIMing, but before REDIMing PRESERVE.

        Ron

        Comment


        • #5
          Hey Ronald,

          Thanks for taking time out to look at my posting.

          When I did as you advised i.e.

          Public Sub AddToVlanArray(ByVal Vlan As Integer)
          If OddOrEven(Vlan) = 0 Then
          intArrEvenVlans(UBound(intArrEvenVlans, 1)) = Vlan
          ReDim intArrEvenVlans(0) As Integer
          ReDim Preserve intArrEvenVlans(UBound(intArrEvenVlans) + 1) As Integer
          Else
          intArrOddVlans(UBound(intArrOddVlans, 1)) = Vlan
          ReDim intArrOddVlans(0) As Integer
          ReDim Preserve intArrOddVlans(UBound(intArrOddVlans) + 1) As Integer
          End If

          the problem with that section seemed to have been solved and it then gave a new error "Run-time error '9': Subscript out of range" and highlighted this line of the program:

          For i = 0 To UBound(intArrOddVlans)

          By the time I got home from work, I decided to run the macro again and it gave me the previous error i.e. "Invalid ReDim". I'd be very grateful if you can assist me with both errors.

          Cheers.

          Comment


          • #6
            Your problem is one of scope.

            You have declared the arrays in one routine and are trying to redim them in another. Thry moving the declatations to the general declarations section and use private instead of dim.

            Comment


            • #7
              Originally posted by Emeka Ibeto View Post
              I am trying to create a macro in MS Excel using VB 6.0 but I keep getting the error message "Invalid Redim" with the entire line "ReDim Preserve intArrEvenVlans(UBound(intArrEvenVlans) +1)" highlighted. Could anyone help me in resolving the problem:
              Not to be smart (well maybe) but you could try using PBWin to create the macro. If nothing else, you'd probably get more help here.

              ===========================================
              "Anyone who considers arithmetical methods
              of producing random digits is,
              of course,
              in a state of sin."
              John von Neumann (1903-1957)
              ===========================================
              It's a pretty day. I hope you enjoy it.

              Gösta

              JWAM: (Quit Smoking): http://www.SwedesDock.com/smoking
              LDN - A Miracle Drug: http://www.SwedesDock.com/LDN/

              Comment


              • #8
                Originally posted by Gösta H. Lovgren-2 View Post
                you could try using PBWin to create the macro.
                Maybe PowerBasic Inc should set up a VB forum - might do them some good when performance comparisons are made...

                Comment


                • #9
                  I'm not much of a COM guy, but couldn't this be done with "Pure PB code" using the Excel COM interface?
                  Michael Mattias
                  Tal Systems Inc. (retired)
                  Racine WI USA
                  [email protected]
                  http://www.talsystems.com

                  Comment


                  • #10
                    Hey,

                    I sincerely appreciate everyone's input. Unfortunately, I am still quite new here and hopefully with time I will get to try out the PB code. On this occasion, I am working on an official task and won't be able to use just any software I choose to.

                    Trevor, I removed all the Dim statements out of the 'Public Sub GenerateConfig()' subroutine and placed them above it (I believe that should be the general declaration section). I am not exactly sure which of the 'Dim' command you want me to change to 'Private', so I just went ahead to run the program and once again I got the error "Run-time error '9' ; subscript out of range" with "intArrOddVlans(UBound(intArrOddVlans, 1)) = Vlan, highlighted.

                    Comment


                    • #11
                      with time I will get to try out the PB code. On this occasion, I am working on an official task and won't be able to use just any software I choose to.
                      Well, then, to meet your production deadline, I think you might get more and maybe better answers in a forum dedicated to VBA/Excel users, as everyone here DOES choose to use a particular kind of software which is neither VBA nor Excel.

                      Not that you will necessarily NOT get responses, but you should know this forum is devoted to the use of the PowerBASIC Inc. compilers and add-on products.

                      FWIW, I am the resident hardass so don't take it personally.

                      MCM
                      Michael Mattias
                      Tal Systems Inc. (retired)
                      Racine WI USA
                      [email protected]
                      http://www.talsystems.com

                      Comment


                      • #12
                        And I am the resident cuddly, and you may take it personally {wink wink}.

                        ===================================================
                        "If you were plowing a field,
                        which would you rather use?
                        Two strong oxen or 1024 chickens?"
                        Seymour Cray (1925-1996), father of supercomputing
                        ===================================================
                        It's a pretty day. I hope you enjoy it.

                        Gösta

                        JWAM: (Quit Smoking): http://www.SwedesDock.com/smoking
                        LDN - A Miracle Drug: http://www.SwedesDock.com/LDN/

                        Comment


                        • #13
                          And I am...

                          ...said the resident philosopher!

                          Comment


                          • #14
                            Originally posted by Gösta H. Lovgren-2 View Post
                            And I am the resident cuddly, and you may take it personally {wink wink}.
                            OK Gösta he's gone. Job done.

                            Comment


                            • #15
                              Emeka,

                              You had to follow everyone elses advice as well.
                              Colin was right when he said you have to change array(0) to array() in the declaration.

                              You also have to redim(0) before you redim preserve as Ronald said.

                              The following code has had those changes made and is tested in Excel 2003.

                              Code:
                              Private intArrOddVlans() As Integer
                              Private intArrEvenVlans() As Integer
                              
                              Public Sub GenerateConfig()
                              ' This subroutine will accumulate data from the various worksheets contained within the request
                              ' And use these to create a configuration script for the Core Switches.
                              ' Two configuration scripts will be generated, one for each core switch.
                              
                              ' Variables
                              Dim strSwitchName As String
                              Dim intRowCounter As Integer
                              Dim strTargetRange As String
                              Dim intFrontEndVlan As Integer
                              Dim intBackEndVlan As Integer
                              Dim intManagementVlan As Integer
                              
                              
                              ReDim intArrEvenVlans(0)
                              ReDim intArrOddVlans(0)
                              ' Switch Name
                              
                              ' Collect from B47 on Worksheet 5
                              
                              Worksheets(5).Activate
                              Range("B47").Activate
                              
                              strSwitchName = ActiveCell.Value
                              
                              
                              ' Add switch Name to config
                              Worksheets(6).Activate
                              intRowCounter = 1
                              strTargetRange = "A" & intRowCounter
                              Range(strTargetRange).Activate
                              ActiveCell.Value = strSwitchName
                              
                              'Move counter
                              intRowCounter = intRowCounter + 3
                              
                              ' Spanning Trees
                              ' This should collect the front end vlan, management and the back end vlan, then create a spanning tree with the priority
                              ' based on whether the VLAN is odd or Even, Odd VLANs will have a spanning tree priority of 8192 and Odd will be 16384.
                              
                              ' Collect VLANs
                              Worksheets(4).Activate
                              
                              Range("E5").Activate
                              intFrontEndVlan = ActiveCell.Value
                              ActiveCell.Offset(1, 0).Activate
                              intBackEndVlan = ActiveCell.Value
                              Range("G5").Activate
                              intManagementVlan = ActiveCell.Value
                              
                              ' For each of the collected vlans, establish if odd or even and then add to correct array...
                              
                              'Front End
                              AddToVlanArray (intFrontEndVlan)
                              'Back End
                              AddToVlanArray (intBackEndVlan)
                              'Management
                              AddToVlanArray (intManagementVlan)
                              
                              ' Create spanning tree lines
                              Dim strSpanTreeOdd As String
                              strSpanTreeOdd = "spanning-tree vlan "
                              
                              For i = 0 To UBound(intArrOddVlans)
                              If intArrOddVlans(i) <> 0 Then
                              strSpanTreeOdd = strSpanTreeOdd & CStr(intArrOddVlans(i)) & ","
                              End If
                              Next i
                              
                              strSpanTreeOdd = Left(strSpanTreeOdd, (Len(strSpanTreeOdd) - 1))
                              
                              strSpanTreeOdd = strSpanTreeOdd & " priority 8192"
                              
                              MsgBox (strSpanTreeOdd)
                              
                              
                              End Sub
                              
                              
                              Public Function OddOrEven(ByVal NumToCheck As Integer) As Integer
                              If NumToCheck Mod 2 <> 0 Then
                              OddOrEven = 1
                              Else
                              OddOrEven = 0
                              End If
                              End Function
                              
                              Public Sub AddToVlanArray(ByVal Vlan As Integer)
                              If OddOrEven(Vlan) = 0 Then
                              intArrEvenVlans(UBound(intArrEvenVlans, 1)) = Vlan
                              ReDim Preserve intArrEvenVlans(UBound(intArrEvenVlans) + 1)
                              Else
                              intArrOddVlans(UBound(intArrOddVlans, 1)) = Vlan
                              ReDim Preserve intArrOddVlans(UBound(intArrOddVlans) + 1)
                              End If
                              
                              End Sub

                              Comment


                              • #16
                                Hi guys,

                                Comments noted.

                                Trevor, your r right, it now works. I should be able to take it from here now.

                                In trying to get this to work, I have learnt a lot especially different suggestions... and I have you guys to thank for that.

                                Have a nice Valentine weekend.

                                Cheers.

                                Emeka.

                                Comment


                                • #17
                                  >Have a nice Valentine weekend.

                                  I think that was directed at Gosta. {wink, wink}
                                  Michael Mattias
                                  Tal Systems Inc. (retired)
                                  Racine WI USA
                                  [email protected]
                                  http://www.talsystems.com

                                  Comment


                                  • #18
                                    Originally posted by Michael Mattias View Post
                                    >Have a nice Valentine weekend.

                                    I think that was directed at Gosta. {wink, wink}
                                    {sigh} My heart doth skip a beat. {sigh}

                                    =============================================
                                    "Black holes are where God divided by zero."
                                    Steven Wright
                                    =============================================
                                    It's a pretty day. I hope you enjoy it.

                                    Gösta

                                    JWAM: (Quit Smoking): http://www.SwedesDock.com/smoking
                                    LDN - A Miracle Drug: http://www.SwedesDock.com/LDN/

                                    Comment

                                    Working...
                                    X