How to create different variables names while in a loop in VBA-CodePudding

I am using VBA to write a Macro and it is working exactly as I want, except that I would like my code to be cleaner. I am using thousands of files that are always in the same order and can't change the order of the columns in these files. Here is the messy part of my code :

...

>                 ActiveSheet.ChartObjects("Graph 1").Activate
>                     ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$L$23"
>                     ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>                     ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'!$L$24:$L$4523"
>                 ActiveSheet.ChartObjects("Graph 2").Activate
>                     ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$K$23"
>                     ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>                     ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'!$K$24:$K$4523"
>                 ActiveSheet.ChartObjects("Graph 3").Activate
>                     ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$E$23"
>                     ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>                     ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'!$E$24:$E$4523"
>                 ActiveSheet.ChartObjects("Graph 4").Activate
>                     ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$B$23"
>                     ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>                     ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'!$B$24:$B$4523"
>                 ActiveSheet.ChartObjects("Graph 5").Activate
>                     ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$U$23"
>                     ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>                     ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'!$U$24:$U$4523"
>                 ActiveSheet.ChartObjects("Graph 6").Activate
>                     ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$AB$23"
>                     ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>                     ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'!$AB$24:$AB$4523"
>                 ActiveSheet.ChartObjects("Graph 7").Activate
>                     ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$I$23"
>                     ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>                     ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'!$I$24:$I$4523"

...

I can't simply use a for-loop since the values associated to the graphs are not in order (i.e. Graph 1 values are not in A, Graph 2 values are not in B). I had worked with Python in the past and had used dictionaries, but I don't know how to do it on VBA. I've tried something but it's not working :

>     Dim Graph As Variant
>     Dim StringGraph As String
>     
>     StringGraph = CStr(Graph)
> 
>     Dim dic As Object
>     Set dic = CreateObject("Scripting.Dictionary")
>         dic.Add "1", "L"
>         dic.Add "2", "K"
>         dic.Add "3", "E"
>         dic.Add "4", "B"
>         dic.Add "5", "U"
>         dic.Add "6", "AB"
>         dic.Add "7", "I"
> 
>         For Each Graph In dic.Keys
>             ActiveSheet.ChartObjects("Graph" & StringGraph).Activate
>             ActiveChart.FullSeriesCollection(1).Name = "='BL-remove'!$ & dict(Graph) & $23"
>             ActiveChart.FullSeriesCollection(1).XValues = "='BL-remove'!$A$24:$A$4523"
>             ActiveChart.FullSeriesCollection(1).Values = "='BL-remove'! _
>                dict(Graph) & $24: $ dict(Graph)& $4523"
>         Next Graph

I know that I'm close the the answer, but maybe there is a better way to do it. I will have to do something similar in the past, and I prefer to know the best way to do it instead of writing messy code. Thanks in advance for the help,

Sincerely,

William

CodePudding user response：

Try something like this:

    Dim Graph As Variant
    Dim dic As Object, col As String
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.Add "1", "L"
    dic.Add "2", "K"
    dic.Add "3", "E"
    dic.Add "4", "B"
    dic.Add "5", "U"
    dic.Add "6", "AB"
    dic.Add "7", "I"
    
    For Each Graph In dic.Keys
        col = dic(Graph)
        With ActiveSheet.ChartObjects("Graph" & Graph).Chart.FullSeriesCollection(1)
            .Name = "='BL-remove'!$" & col & "$23"
            .XValues = "='BL-remove'!$A$24:$A$4523"
            .Values = "='BL-remove'!" & col & "$24:$" & col & "$4523"
        End With
    Next Graph