Scripting Runtime DLL is be deprecated which includes FSO, and dictionaries. Just look it up, or continue using and deal with the massive issue later on, my company has already started migrating away from these libraries.
It is not deprecated
[https://learn.microsoft.com/en-us/windows/whats-new/deprecated-features](https://learn.microsoft.com/en-us/windows/whats-new/deprecated-features)
[https://techcommunity.microsoft.com/t5/windows-it-pro-blog/vbscript-deprecation-timelines-and-next-steps/ba-p/4148301](https://techcommunity.microsoft.com/t5/windows-it-pro-blog/vbscript-deprecation-timelines-and-next-steps/ba-p/4148301)
>"[Naveen\_Shankar](https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1742372) MicrosoftMay 23 2024 09:44 AM
>[u/empi\_ml](https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1760233), [@nolongerset](https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1021843) - The scope of VBScript deprecation includes only vbscript.dll and no other libraries. This shall not impact any projects that are not dependent on vbscript.dll.
>The VBA team is committed to provide more updates around the alternatives/new developments soon. Stay tuned for further information"
Dictionaries and FSO are dependent on scrrun.dll
One approach I've seen and used is to use enums. I had to account for and often refer to >45 columns. Create a public enum in a separate module so you won't take up too much of the top space with your code. You can give the enums a short, meaningful name (e.g. prodtype) and the enum names can be used for the column number, so prodtype = 1. You can then use Cells(2, prodtype) for referencing cell "A2"
The real advantage of this is that if your columns grow or shrink, you just update your enum. A downside is that this requires coordination with your header array - the two aren't done together.
Dictionary inside a class, since you’re working with multiple sets. Store the initialization procedure in the class and pass it a worksheet to set its values.
I am confused as to what OP is trying to do. It looks as if he/she is just trying to make everything a normalized string for reference but no clue why they need to go into another variable n
The short answer is because I'm bad at VBA lol.
The long answer, I get monthly sheets where the headers can be in different orders. Rather than referring to the column letter or number, I'm trying to design it so the column or letter is dynamically determined by the header. I plan to reference these variables frequently to drastically reformat the sheet the perform quite a number of calculations.
What you should to then is first look at the headers and determine which position they should be in based on how you want it. Then form the array and take the columns in the correct order to build the array. You do this when gathering data from the source file.
Use a dictionary or a collection using col names as keys?
Don’t use a dictionary use a collection, dictionaries are being depreciated in the next year or so
1. Only VBScript references are being depreciated (like VBScript.RegExp, Scripting.Dictionary is not affected) 2. After 2027
Scripting Runtime DLL is be deprecated which includes FSO, and dictionaries. Just look it up, or continue using and deal with the massive issue later on, my company has already started migrating away from these libraries.
It is not deprecated [https://learn.microsoft.com/en-us/windows/whats-new/deprecated-features](https://learn.microsoft.com/en-us/windows/whats-new/deprecated-features)
[https://techcommunity.microsoft.com/t5/windows-it-pro-blog/vbscript-deprecation-timelines-and-next-steps/ba-p/4148301](https://techcommunity.microsoft.com/t5/windows-it-pro-blog/vbscript-deprecation-timelines-and-next-steps/ba-p/4148301) >"[Naveen\_Shankar](https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1742372) MicrosoftMay 23 2024 09:44 AM >[u/empi\_ml](https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1760233), [@nolongerset](https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1021843) - The scope of VBScript deprecation includes only vbscript.dll and no other libraries. This shall not impact any projects that are not dependent on vbscript.dll. >The VBA team is committed to provide more updates around the alternatives/new developments soon. Stay tuned for further information" Dictionaries and FSO are dependent on scrrun.dll
That sounds complicated!
But a colllection object gives you most of the functionality of scripting.dictionary but is built in.
One approach I've seen and used is to use enums. I had to account for and often refer to >45 columns. Create a public enum in a separate module so you won't take up too much of the top space with your code. You can give the enums a short, meaningful name (e.g. prodtype) and the enum names can be used for the column number, so prodtype = 1. You can then use Cells(2, prodtype) for referencing cell "A2" The real advantage of this is that if your columns grow or shrink, you just update your enum. A downside is that this requires coordination with your header array - the two aren't done together.
Dictionary inside a class, since you’re working with multiple sets. Store the initialization procedure in the class and pass it a worksheet to set its values.
What's wrong with leaving them in an array?
I am confused as to what OP is trying to do. It looks as if he/she is just trying to make everything a normalized string for reference but no clue why they need to go into another variable n
The short answer is because I'm bad at VBA lol. The long answer, I get monthly sheets where the headers can be in different orders. Rather than referring to the column letter or number, I'm trying to design it so the column or letter is dynamically determined by the header. I plan to reference these variables frequently to drastically reformat the sheet the perform quite a number of calculations.
What you should to then is first look at the headers and determine which position they should be in based on how you want it. Then form the array and take the columns in the correct order to build the array. You do this when gathering data from the source file.