How to Preserve a Dynamic Array When the Array is Created

Hi All,

I am trying to create an arrary from the column headers names in row 1. That step works ok.
Once the array is declared and fills with the column header names I do not want the array to change. I want the values preserved as this will go into a loop and perform some actions that cause the column headers names to change, but I need the arrary to stay the same as when it was declared.

Here is my coding for this part:

Please Login or Register  to view this content.

The problem line is the ReDim Preserve..... Excel states "Subscript out of range".

A side question:
1 to 24 represents all the columns names or range in the arrary.
When I declared my arrary in the locals window I noticed it in type field as Variant (1 to 1, 1 to 24). Why is Excel viewing this as 2 arrays? I thought it would just be Variant ( 1 to 24).


Thanks in advance for the assistance.

It's not, it's a 2 dimensional array with one row and many columns.

You can't do what you are trying to, you can only change the last dimension of a dynamic array, not both as you are trying to - I suspect you're making things more complicated than they need to be. What are you actually trying to do?>

Yes, I can use different methods to work around this, but I thought maybe I was seeing something incorrect. My general question would then be if I create a dynamic arrary and once the values are applied to the array how can I preserve the values in that array to stay the same for the entire module?

I think you may have misunderstood what preserve does. It doesn't stop the values changing, it just doesn't delete them when you make the array larger

Thanks for the sample code and explanations.

Basically what I am trying to accomplish is to create an array from the column headers names then use the column headers names in the array and loop through all of them and use a select case that executes code based on the column name. The code sometimes will delete or move a column and this could change the array, which I don't want to happen.

I guess I could transfer the array to a string array, but that seems to be significant coding.

Here is the module. I want the array/string to hold the column headers names. I do not want to hard code the values in case the reporting output changes. At this point I am only wanting to rearrange the columns in a certain order then more coding to come after.

Please Login or Register  to view this content.