https://github.com/walter426/VbaUtilities
Sometimes, it is needed to create an SQL expression that aggregate a set of columns in the same groups.
(e.g. 1*abc_1 + 2*abc_2 + 3*abc_3 + ...)
It will be very troublesome to aggregate them one by one by hand typing.
'To create an expression that consists of a set of vector columns aggregated in a specified pattern
Public Function CreateSqlSeg_VectorColAgg(col_pattern As String, str_agg As String, Idx_start As Integer, Idx_end As Integer, Optional wildcard As String = "#") As String
On Error GoTo Err_CreateSqlSeg_VectorColAgg
Dim SQL_Seg As String
Dim col_idx As Integer
For col_idx = Idx_start To Idx_end
SQL_Seg = SQL_Seg & Replace(col_pattern, wildcard, col_idx) & " " & str_agg & " "
Next col_idx
SQL_Seg = Left(SQL_Seg, Len(SQL_Seg) - 2)
Exit_CreateSqlSeg_VectorColAgg:
CreateSqlSeg_VectorColAgg = SQL_Seg
Exit Function
Err_CreateSqlSeg_VectorColAgg:
ShowMsgBox (Err.Description)
Resume Exit_CreateSqlSeg_VectorColAgg
End Function
