Array again limit

[Striker3070]

using the following to get Active Directory group membership, is it possible
that once I return a value say "Bob Smith" that is NOT unique this will
stop. I return 1499 members names no matter how many people are in the
group. I've added people to the group to be sure, I still get 1499 as a
result.

I am NOT returning a domain ID which would be unique, so perhaps that's an
issue.



Sub ADGroupMembers()
Dim arrNames()
intSize = 0

'Let's clear the range first
Range("SESCREEN").ClearContents

'Get the AD Group Info for BATY-SESCREEN
Set objGroup = GetObject("LDAP://CN= BATY-SESCREEN,OU=Security
Groups,DC=baty,DC=com")

'Getting User Names and increase the array size
For Each strUser In objGroup.Member
Set objuser = GetObject("LDAP://" & strUser)
ReDim Preserve arrNames(intSize)
arrNames(intSize) = objuser.CN
intSize = intSize + 1
Next

'In order to sort, we have to convert to Uppercase.
For i = (UBound(arrNames) - 1) To 0 Step -1
For j = 0 To i
If UCase(arrNames(j)) > UCase(arrNames(j + 1)) Then
strHolder = arrNames(j + 1)
arrNames(j + 1) = arrNames(j)
arrNames(j) = strHolder
End If
Next
Next

'Fill the SESCREEN range with the array values
Range("SESCREEN").Value = WorksheetFunction.Transpose(arrNames)

End Sub

 

[Helmut Meukel]

Just for clarity:
Is intSize = 1499 at the end of the For-Each-Loop?

Helmut.

 

[Striker3070]

well as soon as the objuser.CN gets, it is 1499

Just for clarity:
Is intSize = 1499 at the end of the For-Each-Loop?

Helmut.

 

[Striker3070]

Sorry, saying that wrong. I can add a watch to objGroup.Member and it has
1499, from 0-1499 so 1500 members.

 

[Helmut Meukel]

Where - and how - do you define the range "SESCREEN"
I tested the Transpose function to see where its limitations are.
It will happily fill a given range without complaining that the provided
array contains more values than the range could hold.

So I asked if you really checked how many names your code retrieved
in the for each loop and stored in the array arrNames.

Helmut.

 

[Striker3070]

the range SESCREEN is simply a named range on a spreadsheet, plenty or room
for 4000 names.

The problem seems to be I never return more or lass than 1500 names. I can
add a watch to objGroup.Member and it has
1499, from 0-1499 so 1500 members. so thie issue seems to be a limit on
either the array can't hold more than 1500, or the LDAP query can't return
more than 1500.

 

[Helmut Meukel]

It's definitely not a problem with the array.
I usually program in VB6 and was uncertain where the limit would
be in Excel VBA, so I tested it on my system with Excel2000:
ReDim arrNames(10000000) still worked,
ReDim arrNames(100000000) caused an "Out of memory" error.
I then filled an array of one million elements with strings, always
redimming it like in your code. No problem at all.

It's a problem with your LDAP query. I never used LDAP so
I can't help you with this.

BTW, assuming you get it fixed, change your other code to make
it faster:
- Dim arrNames() as String
- initially ReDim arrNames(5000)
- when done with the loop: ReDim Preserve arrNames(intSize-1)
Continually redimming the array slows down your code dramatically.
You use a slow sorting method, use Quicksort instead.
For counters like intSize, j and i use Longs instead of Variants.

HTH.

Helmut.

 

[Striker3070]

Thanks Helmut, will have to do some research on LDAP as I'm not really too
familiar with it either.