How to count an Array

[djblois1]

i am using code to count an array.

If I do it this way it works:

Please Login or Register  to view this content.

However if I count the array in a separate function and pass the array to it - it does not count it correctly. Here is the code:

Please Login or Register  to view this content.

If I do it as a separate function then it only counts 1. Can someone help?

[shg]

vColumnNames is a single array.

Please Login or Register  to view this content.

[djblois1]

That still counts 1. How do I get it to count the elements within the function.

[vandan_tanna]

change your function to

Please Login or Register  to view this content.

[djblois1]

shg,

Sorry your code did work. I did not notice that you changed the function declaration. I changed that now and it worked. Thank you and everyone for the help.

[djblois1]

Now I need to add to the end of the array. This is what I have tried:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Neither one works.

[shg]

Please Login or Register  to view this content.

[djblois1]

shg,

Thank you a lot - that works. I have one more request (which I tested but am missing something). How do I get that to work with a function. Here is what I tried:

Please Login or Register  to view this content.

That doesn't work

[shg]

Please respect the time of people that assist, and post all the information needed to duplicate a problem. This, for example, works fine:

Please Login or Register  to view this content.

[djblois1]

shg,

I do respect the time of people that assist - my needs change as I program. Originally I was not going to put it in a function but it added a lot more code (that I would not like to do). So instead I decided to use a separate function.

Thank you for your help

[djblois1]

That is not working perfectly.

I need to do it multiple times. Here is all my code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If I leave the + 1 in there then there will be a blank column between the original array and the first added column. If I remove the + 1, then there is no blank column but each time something adds to the array it writes over the last thing that was written. So if I added "Assigned" and "Region" - "Assigned" would be added then it would be written over by "Region"

[shg]

Once again, this works fine:

Please Login or Register  to view this content.

[djblois1]

snb,

That is doing the same exact thing that my code did. It is adding a blank column between the original array and the first column I add.

[shg]

I'm not snb.

Did you try running the code as posted?

[djblois1]

Yes, the only line that you changed was this:

Please Login or Register  to view this content.

It does exactly the same as my code - it adds a blank column between the Original Array and the first item that I add.

Sorry for messing up your name.

[shg]

The code I posted doesn't result in any blank cells. I have no further suggestions, sorry.

[JosephP]

is all that code in the same module and do you have an option base 1 statement anywhere?

[shg]

It works with option base 1.

[JosephP]

even if the function code is in a different module without an option base 1? I haven't tried (on a cell)-I'm just trying to think of anything that might cause the claimed behavior.

[shg]

Dunno, Joseph. There are disconnected fragments of code with no context, and I'm not up for 20 Questions today.

Please feel free to sort it out.

[JosephP]

logically it would produce that effect. the redim preserve would change the lower bound to 0 on first pass while increasing the upper bound, everything shifts up and the last two slots are now empty and only the last one gets populated. hence blank column before first added item.

[shg]

Yup, that could happen, or conversely, you could overwrite the last element. That was my point about posting code out of context.

[shg]

Test1 leaves a gap, test2 overwrites an element, test3 works irrespective of option base.

Please Login or Register  to view this content.

[JosephP]

but if Extend3 were in a different module, with no option base statement, test3 would have the same problem as test1.

[shg]

I didn't test, but the solution is to not use Option Base -- it's never necessary.

[JosephP]

or at the very least use it consistently.
of course using VBA.Array instead of just Array would also cure it since it ignores the option base