Looks great overall, although I'm really not a fan of banner comments (some of them would do well as @Description annotations), but I like that the commenting is very extensive. Good job!
This is dangerous though:
'Sets the weak/fake reference to an object
You seem to be misunderstanding how to use a Scripting.Dictionary.
There is no need to sort the data before processing into a dictionary.
There is also no need to construct a collection before you add to the dictionary.
Its also slightly more sensible to write the sub as a function.
As a final tweak I'd pass the array in as a parameter rather than hardwiring ...
These items won't necessarily address Excel's memory usage. I think you'd have to have a chat with MS's Excel development team to really address that. This will, however address some of the general coding issues I noticed that should help us write easier to understand and maintain code.
Danger Will Robinson, Danger!!
Debug.Print Now 'Don't know what it ...
Before considering performance...some comments from reviewing the code.
(Best Practice) Use Option Explicit at the top of the module. This forces the requirement for all variables and constants to be declared. Consequently, it can identify typos like Dim ThisWorBookName As String (found in the code) when Dim ThisWorkBookName As String was intended. ...
I have sorted it by alphabetical order
A year from now are you going to remember that the data is supposed to be presorted? Adding a comment notating it would be helpful. Better yet would be suffix it to the routines name:
Public Sub BuildTMProductDictionaryFromSortedTable()
The best approach is not to rely on the data being sorted in the first place. ...
Let me give you the following answer/advise:
As far as your first piece of code is concerned, this can be re-written as:
As far as the second piece of code is concerned (about the copying), I'd like to refer you to this very complete answer. (I might give you the answer but I ...
I would change StandardChartWorkSheet to StandardChart to avoid an ambiguity with a Chart sheet.
The Print prefix implies printing to the debug window. Add makes more sense to me (e.g AddProductValues()`.
ByVal value As Scripting.Dictionary Value should be capitalized because it is a common property and the VBE changes case of variables to match the ...
The code as you have it is not able to be executed but once I made some revisions it seems to run just fine. On my test dataset I had no problem with performance, but if you continue to have performance issues after making edits, I would look into something called "Advanced Filter" for Excel, as it has the ability to set a destination at the onset ...
Try to avoid using Select. You could try the following code:
Dim ws_data As Worksheet, ws_analysis As Worksheet
Dim lRow As Long, lCol As Long
Set ws_data = ThisWorkbook.Worksheets("DataExport")
Set ws_analysis = ThisWorkbook.Worksheets("Analysis")
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
positionInitCaseNom1enY and positionCaseNomEnX should be declared as Const or in an Enum because they are default values that never change.
Const positionInitCaseNom1enY As Long = 5
Const positionCaseNomEnX As Long = 3
Do to implicit conversion no does not need to be cast to a String. The compiler automatically does it for you.
Dim nos As String
nos = ...
Remove the Passes and Arrêt parameters and declare them as Global Constants in a public Module.
Public Const nombrelignezonecomposant As Long = 25 'Number of lines available for document entry
Public Const ligneinitzonecomposant As Long = 18 'First part number entry line
Public Const nocolonnetype As Long = 22 'No of the ...
The rewritten code (below) is much easier to follow - nicely done!
Below are some general follow-up comments that you may find useful.
Use a Function when a procedure is required to return a value to the caller. From the code:
Sub WsExistsAndDelete(ByVal Name As String, ByVal OptionErase As Long, ByRef Result As Boolean)
The procedure is expected to modify ...