Array again limit
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 |
Array again limit
Just for clarity:
Is intSize = 1499 at the end of the For-Each-Loop? Helmut. "Striker3070" schrieb im Newsbeitrag ... 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 |
Array again limit
well as soon as the objuser.CN gets, it is 1499
"Helmut Meukel" wrote in message ... Just for clarity: Is intSize = 1499 at the end of the For-Each-Loop? Helmut. "Striker3070" schrieb im Newsbeitrag ... 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 |
Array again limit
Sorry, saying that wrong. I can add a watch to objGroup.Member and it has
1499, from 0-1499 so 1500 members. "Striker3070" wrote in message ... well as soon as the objuser.CN gets, it is 1499 "Helmut Meukel" wrote in message ... Just for clarity: Is intSize = 1499 at the end of the For-Each-Loop? Helmut. "Striker3070" schrieb im Newsbeitrag ... 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 |
Array again limit
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" schrieb im Newsbeitrag ... well as soon as the objuser.CN gets, it is 1499 "Helmut Meukel" wrote in message ... Just for clarity: Is intSize = 1499 at the end of the For-Each-Loop? Helmut. "Striker3070" schrieb im Newsbeitrag ... 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 |
Array again limit
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" wrote in message ... 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" schrieb im Newsbeitrag ... well as soon as the objuser.CN gets, it is 1499 "Helmut Meukel" wrote in message ... Just for clarity: Is intSize = 1499 at the end of the For-Each-Loop? Helmut. "Striker3070" schrieb im Newsbeitrag ... 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 |
Array again limit
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" schrieb im Newsbeitrag ... 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" wrote in message ... 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" schrieb im Newsbeitrag ... well as soon as the objuser.CN gets, it is 1499 "Helmut Meukel" wrote in message ... Just for clarity: Is intSize = 1499 at the end of the For-Each-Loop? Helmut. "Striker3070" schrieb im Newsbeitrag ... 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 |
Array again limit
Thanks Helmut, will have to do some research on LDAP as I'm not really too
familiar with it either. "Helmut Meukel" wrote in message ... 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" schrieb im Newsbeitrag ... 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" wrote in message ... 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" schrieb im Newsbeitrag ... well as soon as the objuser.CN gets, it is 1499 "Helmut Meukel" wrote in message ... Just for clarity: Is intSize = 1499 at the end of the For-Each-Loop? Helmut. "Striker3070" schrieb im Newsbeitrag ... 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 |
All times are GMT +1. The time now is 11:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com