Fixed length string vs Variable Length

[Oorang]

If you have an string array that will not exceed 64 characters, but will generally be less than say 20 chars, would it be better to fix the string length at 64 or leave it a variable length string in terms of memory and speed? (The array is dynamic and the function could be called many times in an operation.)

 

[tmcfadden]

You're thinking of efficiency in VBA?!?! For shame!!!

Just kidding.

Unless you are doing thousands of allocations, you probably wouldn't see a difference either way. If you are doing thousands, I would imagine that a fixed length array that was 64 characters wide would be faster.

You would also want to create a large array at first ( guess at how many strings are needed ). If you are going to exceed that amount, allocate a bunch more locations and not just one.

Strings are tricky things, as is memory allocation. In general, it's always better to create a large chunk of memory one time than it is to create smaller chunks lots of times.

This would be a fairly easy thing to benchmark.

-Tim

 

[Oorang]

True although, in a peice of irony I realized my little question was made irrelavant by the knowledge that vba.join will not work with fixed-length string-arrays because Join cannot coerce them into a variant(!) *sigh*. Let that be a lesson to me.