excel - 为什么返回的值是，当我通过一个循环运行？

Question

我目前正在从事一个VBA/Excel项目。

为此，我得到了一个代码，它应该可以正常工作。在测试它时，我在某个循环中遇到了问题。如果我让代码在这个循环中运行，它内部和外部的值都设置为 0。

如果我只是注释掉循环（运行一次代码），则代码将按预期工作。当试图改变循环本身时（例如重命名计数器或只让它运行两次，而不是更高的数量），它不起作用。因此，我认为循环很可能有问题，在下面的代码片段中的 Blockquotes 中进行了标记。

Sub Init_Sheets()
    
    Dim LTP_country() As Variant
    ReDim LTP_country(1 To Le, 1 To 6)
    Dim LTP_country_sorted() As Variant
    ReDim LTP_country_sorted(1 To Le, 1 To 6)
    Dim LCOF_country_sorted() As Variant
    ReDim LCOF_country_sorted(1 To Le, 1 To 6)
    Dim LCOF_WA() As Variant
    ReDim LCOF_WA(1 To La, 1 To 2)
    Dim TSP_WA() As Variant
    ReDim TSP_WA(1 To La, 1 To 2)
    
>For a = 1 To La 'loop over countries**

        For e = 1 To Le 'Loop over classes
            For j = 1 To 6 'loop over columns
                LTP_country(e, j) = LTP_cond(e + (a - 1) * Le, j)
            Next j
        Next e
        LCOF_country_sorted = Application.WorksheetFunction.Sort(LTP_country, 2, 1)
        TSP_country_sorted = Application.WorksheetFunction.Sort(LTP_country, 3, 1)
        For e = 1 To Le 'Loop over classes
        'col 5 is potential allocated, col 6 is potential cummulated sum
            'LCOF sorting and potential allocation-----------
            If LCOF_country_sorted(e, 4) <= m_map - LCOF_country_sorted(e - 1, 6) Then 'if Potential of class <= minimum available potential - cumsum allocated potential
                LCOF_country_sorted(e, 5) = LCOF_country_sorted(e, 4)
            Else
                LCOF_country_sorted(e, 5) = m_map - LCOF_country_sorted(e - 1, 6)
            End If
            LCOF_country_sorted(e, 6) = LCOF_country_sorted(e - 1, 6) + LCOF_country_sorted(e, 5) 'cumsum potenial
    
            'TSP sorting and potential allocation-----------
            If TSP_country_sorted(e, 4) <= m_map - TSP_country_sorted(e - 1, 6) Then 'if Potential of class <= minimum available potential - cumsum allocated potential
                TSP_country_sorted(e, 5) = TSP_country_sorted(e, 4)
            Else
                TSP_country_sorted(e, 5) = m_map - TSP_country_sorted(e - 1, 6)
            End If
            TSP_country_sorted(e, 6) = TSP_country_sorted(e - 1, 6) + TSP_country_sorted(e, 5) 'cumsum potenial
    
        Next e
        If LCOF_country_sorted(9, 6) < m_map Then 'set allocated potential = 0 if the cumsum of all RES potentials is smaller than minimum available potential
            For e = 1 To Le
                LCOF_country_sorted(e, 5) = 0
            Next e
        End If
        If TSP_country_sorted(9, 6) < m_map Then  'set allocated potential = 0 if the cumsum of all RES potentials is smaller than minimum available potential
            For e = 1 To Le
                TSP_country_sorted(e, 5) = 0
            Next e
        End If
        With Application.WorksheetFunction
        LCOF_WA(a, 1) = Left(LCOF_country_sorted(1, 1), 2) 'extract alpha 2 country code from save code
        LCOF_WA(a, 2) = (.SumProduct(.Index(LCOF_country_sorted, 0, 2), .Index(LCOF_country_sorted, 0, 5)) / .Sum(.Index(LCOF_country_sorted, 0, 5))) * 1000 'calculate weighted average of LCOF convert from $/kWh to $/MWh
        
        TSP_WA(a, 1) = Left(TSP_country_sorted(1, 1), 2) 'extract alpha 2 country code from save code
        TSP_WA(a, 2) = (.SumProduct(.Index(TSP_country_sorted, 0, 3), .Index(TSP_country_sorted, 0, 5)) / .Sum(.Index(TSP_country_sorted, 0, 5))) * 1000 'calculate weighted average of LCOF and convert from $/kWh to $/MWh
        End With

>Next a
    
    PrintArray TSP_WA, Geo.[W12]
    PrintArray Application.WorksheetFunction.Index(LCOF_WA, 0, 2), Geo.[Y12]
    
End Sub

我希望，提供的代码包含足够的信息来识别问题，因为有些部分被剪掉了。

提前致谢！

标签： excelvbaloops