如何在VBA中的回圈中創建不同的變數名稱

我正在使用 VBA 撰寫一個宏，它完全按照我的意愿作業，只是我希望我的代碼更簡潔。我正在使用數以千計的檔案，這些檔案的順序始終相同，并且無法更改這些檔案中列的順序。這是我的代碼的混亂部分：

...

>                 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"

...

我不能簡單地使用 for 回圈，因為與圖形關聯的值不是按順序排列的（即圖形 1 的值不在 A 中，圖形 2 的值不在 B 中）。我過去曾使用過 Python 并使用過字典，但我不知道如何在 VBA 上進行操作。我試過一些東西，但它不起作用：

>     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

我知道我已經接近答案了，但也許有更好的方法來做到這一點。過去我將不得不做一些類似的事情，我更喜歡知道最好的方法而不是撰寫凌亂的代碼。在此先感謝您的幫助，

真摯地，

威廉

uj5u.com熱心網友回復：

嘗試這樣的事情：

    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

標籤：vba 循环 字典 for循环 变量

上一篇：快速更改多個后續變數

下一篇：如何在React中將字串和變數連接到另一個變數的名稱中？