use of ReDim Preserve statement

[Gösta H. Lovgren-2]

>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
=============================================

[Michael Mattias]

>Have a nice Valentine weekend.

I think that was directed at Gosta. {wink, wink}

[Emeka Ibeto]

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.

[Trevor Lane]

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.

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

[Chris Holbrook]

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

OK Gösta he's gone. Job done.

[John Montenigro]

And I am...

...said the resident philosopher!

[Gösta H. Lovgren-2]

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
===================================================

[Michael Mattias]

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

[Emeka Ibeto]

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.

[Michael Mattias]

I'm not much of a COM guy, but couldn't this be done with "Pure PB code" using the Excel COM interface?

[Chris Holbrook]

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...

[Gösta H. Lovgren-2]

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)
===========================================

[Trevor Lane]

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.

[Emeka Ibeto]

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.

[Ronald Zutz]

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

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:

ReDim Preserve intArrOddVlans(0) As Integer
ReDim Preserve intArrEvenVlans(0) As Integer

as initialization code after DIMing, but before REDIMing PRESERVE.

Ron

[Emeka Ibeto]

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.

[colin glenn]

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.

[Emeka Ibeto]

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