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.
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.
VBA Code:
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
Last edited by a moderator: