Help with array

[Karen53]

Hi,

I seem to be missing something. I see examples where a variable is used to
define an array. When I try it (below) I get a message it has to be a
constant. I would appreciate the guidance. I obviously don't understand
something.

Sub FindDupCurrent()

Dim Lusedrow As Long
Dim iCtr As Long
Dim ArrayCounter As Long
Dim ArrayLength As Long
Dim HasDups As Boolean
Dim Dupcount As Long

Lusedrow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row
ArrayCounter = 0
ArrayLength = MainPagepg.Range("I11").Value
Dim CurrArray(1 To ArrayLength) As Long

With MainPagepg
For iCtr = 14 To Lusedrow 'Tenants begin on row 14
If .Range("B" & iCtr).Value = "Current" Then
If .Range("D" & iCtr).Value > 0 Then
ArrayCounter = ArrayCounter + 1
CurrArray(ArrayCounter) = MainPagepg.Range("D" &
iCtr).Value
End If
End If
Next
End With

ArrayCounter = 1
iCtr = 0
HasDups = False
Dupcount = 0

For iCtr = 1 To ArrayLength
For ArrayCounter = 1 To ArrayLength
If CurrArray(iCtr) = CurrArray(ArrayCounter) Then
Dupcount = Dupcount + 1
End If
If Dupcount > 1 Then
HasDups = True
End If
Next
Next

If HasDups = True Then
With MainPagepg.Range("D11")
.WrapText = True
.Font.ColorIndex = 3
.Font.Bold = True
.Value = "Duplicate Current Unit"
End With
End If

Erase CurrArray

End Sub

 

[Chip Pearson]

Karen,

You cannot declare a static array (an array with the size in the Dim
statement) using a variable. You must use a constant. However, you can
declare a dynamic array (an array with no sizing in the Dim statement) and
then use a variable to allocate and resize the array. For example,

Dim L As Long
L = 10
Dim Arr1(1 To L) As Long ' <<< ILLLEGAL because L is a variable. You need a
constant.
Dim Arr1(1 To 10) As Long ' <<< LEGAL because you are using constants.

Dim L As Long
Dim Arr2() As Long ' <<< Dynamic array, no sizing in the Dim statement
L = 10
ReDim Arr2(1 To L) '<<< LEGAL because Arr2 is dynamic.

Note that when you ReDim an array, its contents are lost unless you use the
Preserve keyword:

ReDim Preserve Arr3(1 To 10)

increases or decreases the size of Arr3 to contain 10 elements.

ReDim Preserve is a relatively expensive operation and should be used
sparingly. The best technique is to declare a dynamic array, ReDim it to a
size larger than you expect it to ever be necessary, and then use one single
ReDim Preserve at the end to shrink the array down to the actual used size.

With a dynamic array, the Erase statement destroys the contents of the array
and releases the memory used by the array, returning it to the state as if
you had used only Dim Arr() with no other operations on Arr. With a static
array, the Erase statement destroys the contents of the array (setting
elements back to their default values -- 0's or null strings or Nothings)
but does not release any memory. The static array continues to contain the
declared number of elements even after being Erased.

If an array is declared as a static array (size in the Dim statement), it
cannot be resized with ReDim. Its size is permanently fixed. You cannot
convert a static array to a dynamic array.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rick Rothstein \(MVP - VB\)]

The Dim statement is used by the compiler to carve out memory space for an
array (or variable) in advance... it can't do that if the number of elements
is a variable. To do what you want, you have to declare your array as a
dynamic array and then ReDim it the size you want.

Dim CurrArray() As Long
.....
.....
ArrayLength = MainPagepg.Range("I11").Value
......
' Anywhere within the proper scope, set the size this way
ReDim CurrArray(1 To ArrayLength)
......


Rick

 

[Karen53]

Thank you both!