How to define VBA functions using loops in matrix..

[anne2021]

Hi again. I want to know if someone can help me with the VBA code as follows:
How can I construct a VBA code to define the function of row sums of E6, E9, E12 and E15 => E22: F6, F9, F12 and F15 => F22, and so on. There are selected cells that needs to be excluded from the total of sums. Also, I need to get the column sums defined in the worksheet as exports. Please see below image.

While I know that I can simply do this one by sum functions or formula, however, the requirement and mechanisms that I was being asked for is to define the arrays/dimensions, then construct the functions codes..

Below is an example of code that I needed to follow in constructing the sums of rows and columns. Otherwise, an alternative solutions would be very much appreciated.

Public Function Convert_4Dto2D(Array_4D)
Dim Row_BlockCount, Col_BlockCount, Row_Count, Col_Count
Dim Iteration, Size_Array_4D, Array_Dimension
ReDim Size_Array_4D(1 To 4)
    For Iteration = 1 To 4
        If Iteration < 5 Then
            Size_Array_4D(Iteration) = UBound(Array_4D, Iteration)
        End If
    Next Iteration    
        Array_Dimension = Iteration - 1
     If Array_Dimension = 4 Then
    Dim Result: ReDim Result(1 To Size_Array_4D(1) * Size_Array_4D(3), 1 To Size_Array_4D(2) * Size_Array_4D(4))
        For Row_BlockCount = 1 To Size_Array_4D(1)
            For Col_BlockCount = 1 To Size_Array_4D(2)
                For Row_Count = 1 To Size_Array_4D(3)
                    For Col_Count = 1 To Size_Array_4D(4)
                        Result _
                        ((Row_BlockCount - 1) * Size_Array_4D(3) + Row_Count, (Col_BlockCount - 1) * Size_Array_4D(4) + Col_Count) _
                            = _
                                Array_4D(Row_BlockCount, Col_BlockCount, Row_Count, Col_Count)
                    Next Col_Count
                Next Row_Count
            Next Col_BlockCount
        Next Row_BlockCount
    Else
        MsgBox "Not a 4D array"
    End If
Convert_4Dto2D = Result
End Function

Public Function Convert_2Dto4D(Array_2D, N_Row_Block, N_Col_Block, N_Row, N_Col)
Dim Row_BlockCount, Col_BlockCount, Row_Count, Col_Count
Dim Iteration, Size_Array_2D, Array_Dimension
ReDim Size_Array_2D(1 To 2)
    For Iteration = 1 To 2
        If Iteration < 3 Then
            Size_Array_2D(Iteration) = UBound(Array_2D, Iteration)
        End If
    Next Iteration
        Array_Dimension = Iteration - 1
    If (N_Row_Block * N_Row = Size_Array_2D(1)) And (N_Col_Block * N_Col = Size_Array_2D(2)) Then
        If Array_Dimension = 2 Then
        Dim Result: ReDim Result(1 To N_Row_Block, 1 To N_Col_Block, 1 To N_Row, 1 To N_Col)
            For Row_BlockCount = 1 To N_Row_Block
                For Col_BlockCount = 1 To N_Col_Block
                    For Row_Count = 1 To N_Row
                        For Col_Count = 1 To N_Col
                            Result(Row_BlockCount, Col_BlockCount, Row_Count, Col_Count) _
                                = Array_2D((Row_BlockCount - 1) * N_Row + Row_Count, (Col_BlockCount - 1) * N_Col + Col_Count)
                        Next Col_Count
                    Next Row_Count
                Next Col_BlockCount
            Next Row_BlockCount
        Else
            MsgBox "Not a 2D array"
        End If
    Else
        MsgBox "Not Compatible"
    End If
    
Convert_2Dto4D = Result

End Function

Public Function Vertical_BlockDiagonals_2D(Array_2D, N_Row_Block, N_Col_Block, N_Row, N_Col)

Dim Row_BlockCount, Col_BlockCount, Row_Count, Col_Count
Dim Iteration, Size_Array_2D, Array_Dimension
ReDim Size_Array_2D(1 To 2)

    For Iteration = 1 To 2
        If Iteration < 3 Then
            Size_Array_2D(Iteration) = UBound(Array_2D, Iteration)
        End If
    Next Iteration

        Array_Dimension = Iteration - 1
    
    If Array_Dimension = 2 And (N_Row_Block * N_Row = Size_Array_2D(1)) And (N_Col_Block * N_Col = Size_Array_2D(2)) Then
        
        Dim Result: ReDim Result(1 To N_Row_Block * N_Row, 1 To N_Col)
            For Row_BlockCount = 1 To N_Row_Block
                For Row_Count = 1 To N_Row
'                    For Col_BlockCount = 1 To N_Col_Block
                        For Col_Count = 1 To N_Col
                            Result((Row_BlockCount - 1) * N_Row + Row_Count, Col_Count) _
                                = Array_2D((Row_BlockCount - 1) * N_Row + Row_Count, (Row_BlockCount - 1) * N_Col + Col_Count)
                        Next Col_Count
'                    Next Col_BlockCount
                Next Row_Count
            Next Row_BlockCount
        Else
            MsgBox "Not a 2D array or Not Compatible"
        End If
    
Vertical_BlockDiagonals_2D = Result

End Function

Public Function Horizontal_BlockDiagonals_2D(Array_2D, N_Row_Block, N_Col_Block, N_Row, N_Col)

Dim Row_BlockCount, Col_BlockCount, Row_Count, Col_Count
Dim Iteration, Size_Array_2D, Array_Dimension
ReDim Size_Array_2D(1 To 2)

    For Iteration = 1 To 2
        If Iteration < 3 Then
            Size_Array_2D(Iteration) = UBound(Array_2D, Iteration)
        End If
    Next Iteration

        Array_Dimension = Iteration - 1
    
    If Array_Dimension = 2 And (N_Row_Block * N_Row = Size_Array_2D(1)) And (N_Col_Block * N_Col = Size_Array_2D(2)) Then
        
        Dim Result: ReDim Result(1 To N_Row, 1 To N_Col_Block * N_Col)
            For Col_BlockCount = 1 To N_Col_Block
                For Row_Count = 1 To N_Row
'                    For Col_BlockCount = 1 To N_Col_Block
                        For Col_Count = 1 To N_Col
                            Result(Row_Count, (Col_BlockCount - 1) * N_Col + Col_Count) _
                                = Array_2D((Col_BlockCount - 1) * N_Row + Row_Count, (Col_BlockCount - 1) * N_Col + Col_Count)
                        Next Col_Count
'                    Next Col_BlockCount
                Next Row_Count
            Next Col_BlockCount
        Else
            MsgBox "Not a 2D array or Not Compatible"
        End If
    
Horizontal_BlockDiagonals_2D = Result

End Function

Public Function Vertical_OffBlockDiagonals_2D(Array_2D, N_Row_Block, N_Col_Block, N_Row, N_Col)

Dim Row_BlockCount, Col_BlockCount, Row_Count, Col_Count
Dim Temp
Dim Iteration, Size_Array_2D, Array_Dimension
ReDim Size_Array_2D(1 To 2)

    For Iteration = 1 To 2
        If Iteration < 3 Then
            Size_Array_2D(Iteration) = UBound(Array_2D, Iteration)
        End If
    Next Iteration

        Array_Dimension = Iteration - 1
    
    If Array_Dimension = 2 Then
        
        Dim Result: ReDim Result(1 To N_Row_Block * N_Row, 1 To N_Col)
        Temp = 0
            For Row_BlockCount = 1 To N_Row_Block
                For Row_Count = 1 To N_Row
                    For Col_Count = 1 To N_Col
                        For Col_BlockCount = 1 To N_Col_Block
                            If Row_BlockCount = Col_BlockCount Then
                                Temp = Temp
                            Else
                                Temp = Temp + Array_2D((Row_BlockCount - 1) * N_Row + Row_Count, (Col_BlockCount - 1) * N_Col + Col_Count)
                            End If
                        Next Col_BlockCount
                            Result((Row_BlockCount - 1) * N_Row + Row_Count, Col_Count) = Temp
                            Temp = 0
                    Next Col_Count
                Next Row_Count
            Next Row_BlockCount
        Else
            MsgBox "Not a 2D array or Not Compatible"
        End If
    
Vertical_OffBlockDiagonals_2D = Result

End Function

Public Function Horizontal_OffBlockDiagonals_2D(Array_2D, N_Row_Block, N_Col_Block, N_Row, N_Col)

Dim Row_BlockCount, Col_BlockCount, Row_Count, Col_Count
Dim Iteration, Size_Array_2D, Array_Dimension
ReDim Size_Array_2D(1 To 2)

    For Iteration = 1 To 2
        If Iteration < 3 Then
            Size_Array_2D(Iteration) = UBound(Array_2D, Iteration)
        End If
    Next Iteration

        Array_Dimension = Iteration - 1
    
    If Array_Dimension = 2 Then
        
        Dim Result: ReDim Result(1 To N_Row, 1 To N_Col_Block * N_Col)
            For Col_BlockCount = 1 To N_Col_Block
                For Row_Count = 1 To N_Row
                    For Col_Count = 1 To N_Col
                        For Row_BlockCount = 1 To N_Col_Block
                            If Row_BlockCount = Col_BlockCount Then
                                Temp = Temp
                            Else
                                Temp = Temp + Array_2D((Row_BlockCount - 1) * N_Row + Row_Count, (Col_BlockCount - 1) * N_Col + Col_Count)
                            End If
                        Next Row_BlockCount
                            Result(Row_Count, (Col_BlockCount - 1) * N_Col + Col_Count) = Temp
                            Temp = 0
                    Next Col_Count
                Next Row_Count
            Next Col_BlockCount
        Else
            MsgBox "Not a 2D array or Not Compatible"
        End If
    
Horizontal_OffBlockDiagonals_2D = Result

End Function


Function X()

'Dim Temp1, Temp2
'Dim Z_Domestic, F_Domestic, Z_Import, F_Import, Z_Export, F_Export
'ReDim Z_Domestic(1 To N_Country, 1 To 1, 1 To N_Industry, 1 To N_Industry)
'ReDim Z_Export(1 To N_Country, 1 To 1, 1 To N_Industry, 1 To N_Industry)
'ReDim Z_Import(1 To 1, 1 To N_Country, 1 To N_Industry, 1 To N_Industry)
'
'ReDim F_Domestic(1 To N_Country, 1 To 1, 1 To N_Industry, 1 To N_FD)
'ReDim F_Export(1 To N_Country, 1 To 1, 1 To N_Industry, 1 To N_FD)
'ReDim F_Import(1 To 1, 1 To N_Country, 1 To N_Industry, 1 To N_FD)
'
'
'
'    For Row_Country_Count = 1 To N_Country
'        For Row_Count = 1 To N_Industry
'            For Col_Count = 1 To N_Industry
'                For Col_Country_Count = 1 To N_Country
'                    If Row_Country_Count = co_country_count Then
'                        Z_Domestic(Row_Country_Count, 1, Row_Count, Col_Count) = Array4D(Row_Country_Count, Col_Country_Count, Row_Count, Col_Count)
'                    Else
'                        Temp1 = Temp1 + Array4D(Row_Country_Count, Col_Country_Count, Row_Count, Col_Count)
'                        Temp2 = Temp2 + Array4D(Col_Country_Count, Row_Country_Count, Col_Count, Row_Count)
'                    End If
'                Next Col_Country_Count
'                    Z_Export(Row_Country_Count, 1, Row_Count, Col_Count) = Temp1: Temp1 = 0
'                Z_Import(1, Row_Country_Count, Col_Count, Row_Count) = Temp2: Temp2 = 0
'            Next Col_Count
'        Next Row_Count
'    Next Row_Country_Count
'

End Function