This function is called by a sub that feeds it lines that the function needs to print for all the elements in an array.
This function retrieves a string, line_to_print
that contains a substring replace_this_keyword_0
called "replace_this_keyword_0"
(sometimes occuring twice in the same string) when it is called. It also receives a reference to the row number in which the array is listed in, in "Sheet1" in excel.
Next the number of used dimensions for that array is given by used_dimension(row_variables)
. As well as the starting index and ending index for that dimension of that array in start_index(dimension, row_variables)
and end_index(dimension, row_variables)
.
Based on these input parameters, it loops through all the elements of the array and repeatedly prints out the line with the changed array indices until it has printed the line for all the array elements.
The purpose of this function is to print lines of code, that are copied in a larger procedure.
The purpose of the sub calling this function is to code allow a too large procedure to be refactored by printing a code that passes all the variables registered in the local window of the VBA editor of excel 2016 in a collection that is then easily and completely passed to multiple smaller pieces of code that are broken off of the too large code.
Initially I could not get the extract method of Rubberduck working. This led me to write this code. I see it as a learning exercise to increase my understanding of how variables are transferred between different types of code in VBA.
My main concern is the pattern that occurs in the nested for
loops. I think it can be written more compact without sacrificing too much readability.
Function loop_through_indices(row_variables, used_dimension, start_index, end_index, line_to_print, replace_this_keyword_0, lastrow)
'0. This function absorbs the line that needs to be written with keyword: replace_this_keyword_0
'1. then it loops through all the indices of the array that the line to be printed contains, in row_variables
'2. and replaces the keyword with the indices string (E.g.: (4,3,9)
'3. then it writes the line by calling sub write_line(index_string, line_to_print, replace_this_keyword_0)
'This function requires the following data:
'a. row
'b. number of dimensions used by variable
'c. the starting index of each used dimension
'd. the ending index of each used dimension
'e. the line that needs to be printed
'f. the keyword
'a= row_variables
'b= used_dimension(row_variables)
'c= start_index(dimension_test, row_array)
'd= end_index(dimension_test, row_array)
'e= ..
'f= replace_this_keyword_0
Dim index_1 As Integer
Dim index_2 As Integer
Dim index_3 As Integer
Dim index_4 As Integer
Dim index_5 As Integer
Dim index_string As String
For index_1 = start_index(1, row_variables) To end_index(1, row_variables)
If used_dimension(row_variables) > 1 Then
For index_2 = start_index(2, row_variables) To end_index(2, row_variables)
If used_dimension(row_variables) > 2 Then
For index_3 = start_index(3, row_variables) To end_index(3, row_variables)
If used_dimension(row_variables) > 3 Then
For index_4 = start_index(4, row_variables) To end_index(4, row_variables)
If used_dimension(row_variables) > 4 Then
For index_5 = start_index(5, row_variables) To end_index(5, row_variables)
index_string = "(" & index_1 & "," & index_2 & "," & index_3 & "," & index_4 & "," & index_5 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
Next index_5
Else
index_string = "(" & index_1 & "," & index_2 & "," & index_3 & "," & index_4 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index_4
Else
index_string = "(" & index_1 & "," & index_2 & "," & index_3 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index_3
Else
index_string = "(" & index_1 & "," & index_2 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index_2
Else
index_string = "(" & index_1 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index_1
End Function
For completeness, function loop_through_indices
calls subroutine write_line
to write the line.
Sub write_line(index_string, line_to_print, replace_this_keyword_0)
Dim line_with_indices As String
line_with_indices = Replace(line_to_print, replace_this_keyword_0, index_string)
Print #1, line_with_indices
'MsgBox ("line_with_indices =" & vbNewLine & line_with_indices)
End Sub
I initially tried to replace the list of index_..
strings with an array of strings, but there was a problem with the use of that array element as a counter. I am retrying that implementation now as I noticed the following code does work:
Function loop_through_indices(row_variables, used_dimension, start_index, end_index, line_to_print, replace_this_keyword_0, lastrow)
'0. This function absorbs the line that needs to be written with keyword: replace_this_keyword_0
'1. then it loops through all the indices of the array that the line to be printed contains, in row_variables
'2. and replaces the keyword with the indices string (E.g.: (4,3,9)
'3. then it writes the line by calling sub write_line(index_string, line_to_print, replace_this_keyword_0)
'This function requires the following data:
'a. row
'b. number of dimensions used by variable
'c. the starting index of each used dimension
'd. the ending index of each used dimension
'e. the line that needs to be printed
'f. the keyword
'a= row_variables
'b= used_dimension(row_variables)
'c= start_index(dimension_test, row_array)
'd= end_index(dimension_test, row_array)
'e= ..
'f= replace_this_keyword_0
Dim index_1 As Integer
Dim index_2 As Integer
Dim index_3 As Integer
Dim index_4 As Integer
Dim index_5 As Integer
Dim index_string As String
Dim index(1) As Integer
For index(1) = start_index(1, row_variables) To end_index(1, row_variables)
If used_dimension(row_variables) > 1 Then
For index_2 = start_index(2, row_variables) To end_index(2, row_variables)
If used_dimension(row_variables) > 2 Then
For index_3 = start_index(3, row_variables) To end_index(3, row_variables)
If used_dimension(row_variables) > 3 Then
For index_4 = start_index(4, row_variables) To end_index(4, row_variables)
If used_dimension(row_variables) > 4 Then
For index_5 = start_index(5, row_variables) To end_index(5, row_variables)
index_string = "(" & index_1 & "," & index_2 & "," & index_3 & "," & index_4 & "," & index_5 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
Next index_5
Else
index_string = "(" & index_1 & "," & index_2 & "," & index_3 & "," & index_4 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index_4
Else
index_string = "(" & index_1 & "," & index_2 & "," & index_3 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index_3
Else
index_string = "(" & index_1 & "," & index_2 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index_2
Else
index_string = "(" & index_1 & ")"
Call write_line(index_string, line_to_print, replace_this_keyword_0)
End If
Next index(1)
End Function
write_line
is called by the functionloop_through_indices
. The functionloop_through_indices
in turn is called by another subroutine which is left out since it currently is not part of the focus. \$\endgroup\$ – a.t. Jun 3 '18 at 6:46