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.

 

[joel]

Ooh! I didn't realize last week you were getting a files in a Folde
which does have the limit of 1500 files. I don't rememer if th
scripting obectject contains the same limits. the problem is with th
operating system getting the file names and not an array size limit.
The last time I seen this problem was probably 5 years ago. I don'
remmeber how I fixed the problem.


Private Function AllFiles(ByVal FullPath As String) _
As String()
'***************************************************
'PURPOSE: Returns all files in a folder using
'the FileSystemObject

'PARAMETER: FullPath = FullPath to folder for
'which you want all files

'RETURN VALUE: An array containing a list of
'all file names in FullPath, or a 1-element
'array with an empty string if FullPath
'does not exist or it has no files

'REQUIRES: Reference to Micrsoft Scripting
' Runtime

'EXAMPLE:

'Dim sFiles() as string
'dim lCtr as long
'sFiles = AllFiles("C:\Windows\System")
'For lCtr = 0 to Ubound(sFiles)
' Debug.Print sfiles(lctr)
'Next

'REMARKS: The FileSystemObject does not
'Allow for the use of wild cards (e.g.,
'*.txt.) If this is what you need, see
'http://wwww.freevbcode.com/ShowCode.asp?ID=1331
'************************************************

Dim oFs As New FileSystemObject
Dim sAns() As String
Dim oFolder As Folder
Dim oFile As File
Dim lElement As Long

ReDim sAns(0) As String
If oFs.FolderExists(FullPath) Then
Set oFolder = oFs.GetFolder(FullPath)

For Each oFile In oFolder.Files
lElement = IIf(sAns(0) = "", 0, lElement + 1)
ReDim Preserve sAns(lElement) As String
sAns(lElement) = oFile.Name
Next
End If

AllFiles = sAns
ErrHandler:
Set oFs = Nothing
Set oFolder = Nothing
Set oFile = Nothing
End Function

 

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