1. 수식 개수의 제한 문제점
1편과 2편은 합계 등 수식이 4개인 것을 전제로 했기 때문에
3개가 되거나 5개가 되면 원하는 답이 나오지 않게 됩니다.
아래와 같이 Var(분산)을 지워서 3개를 만들고
calc_func매크로를 실행하면
행 수에서 4를 뺀 위치에 합계(Sum) 수식이 들어가기 때문에 Ben 자리에 =sum(b3:b11)이 들어가 있고,
오른쪽은 마지막 열이 Var이 아니기 때문에 Sum부터 Var까지 수식 4개가 추가되었습니다.
2. 해결 방안
가. 원인
첫번째는 end_row_of_calc = row_num - 4과 end_col_of_calc = col_num - 4로 모두 4란 고정적인 숫자를 뺐기 때문입니다.
두번째는 마지막 열의 함수명이 Var인 것을 가정했기 때문입니다.
나. 대책
① 4란 숫자를 입력받는 것으로 바꿔야 합니다.
직접 매크로 프로그램을 수정하도록 하는 방법도 있지만 프로그램을 모르는 사람에게는 무엇을 고쳐야 하는 지도 잘 모르기 때문에 이용자 친화적으로 프로그램을 수정해야 합니다.
② 함수명의 순서를 지정해야 합니다.
이것도 미리 지정할 수도 있지만, 입력 받는 것으로 하겠습니다.
③ 13행 아래에 Sum 등 함수명은 이미 입력된 것을 가정하고 프로그램을 짰는데, 오른쪽처럼 입력된 경우와 입력되지 않은 경우를 구분해서 계산할 마지막 행을 구해야 합니다.
다. 코드 작성
가. 함수의 개수와 함수명 입력받기
'함수의 개수 지정
Dim count_of_calc As Integer
count_of_calc = Application.InputBox("계산할 함수의 개수를 입력하세요.", Type:=1)
'함수명을 저장할 배열 선언
Dim names_of_func As String
names_of_func = Application.InputBox("계산할 함수명을 ,(쉼표)로 구분하여 입력하세요.", Type:=2)
Dim name_of_func() As String
' -----------------------------
' Microsoft 365
' name_of_func = Split(names_of_func, ",")
' -----------------------------
' Microsoft 365 미만 버전
Dim ord As Integer '배열의 순번 지정
ord = 0 '배열은 순번이 0부터 시작함
ReDim name_of_func(100) '배열 초기화, 개수를 반드시 지정해야 하므로 크게 지정
Dim prev_i As Integer '이전 i의 위치 저장. 왜냐하면 쉼표 다음 문자 추출을 위해
For i = 1 To Len(names_of_func)
If InStr(i, names_of_func, ",") > 0 Then
name_of_func(ord) = Mid(names_of_func, i, InStr(i, names_of_func, ",") - i)
prev_i = InStr(i, names_of_func, ",")
i = InStr(i, names_of_func, ",") + 1
ord = ord + 1
End If
Next
i = prev_i + 1
name_of_func(ord) = Mid(names_of_func, i, Len(names_of_func) - prev_i)
ReDim Preserve name_of_func(ord)
' -----------------------------
(1) 함수의 개수 입력받기
| Dim count_of_calc As Integer count_of_calc = Application.InputBox("계산할 함수의 개수를 입력하세요.", Type:=1) |
함수의 개수를 저장할 변수명을 count_of_calc라고 지정하고 정수 타입으로 선언합니다.
그리고, Application.InputBox를 이용해 숫자 타입(Type:=1)으로 입력받은 후 count_of_calc에 저장합니다.
Application.InputBox에 대한 설명은 아래 글을 참고바랍니다.
(2) 함수명을 저장할 문자 변수와 배열 선언, 함수명 입력받기
| '함수명을 저장할 문자와 배열 선언 Dim names_of_func As String Dim name_of_func() As String names_of_func = Application.InputBox("입력되어 있다면 확인 클릭," & vbCrLf _ & "계산할 함수명을 ,(쉼표)로 구분하여 입력하세요.", Type:=2) |
함수명을 문자열로 입력받은 후 쉼표(,)로 구분해서 배열에 저장할 수 있도록
문자열 타입의 변수 names_of_func과 문자열 배열 name_of_func를 선업합니다.
또한 기존에 함수명이 입력되어 있다면 입력받을 필요가 없으므로 확인 버튼을 누르도록 합니다.
배열을 선언할 때는 일반 변수와 달리 괄호안에 배열의 개수를 입력하는데
개수는 생략하고 Dim name_of_func() As String 라고 선언할 수 있는데,
그렇더라도 배열에 값을 입력할 때는 ReDim으로 먼저 개수를 지정해야 합니다.
마찬가지로 Application.InputBox를 이용해 함수명을 입력받아 문자열 변수 names_of_func에 저장합니다.
(3) 함수명 쉼표로 구분해서 배열에 저장하기
(가) Microsoft 365 버전인 경우
Split 함수를 이용해 구분 문자를 기준으로 문자열을 분리해서 배열에 저장할 수 있습니다.
Split 함수의 구문은
Split(expression, [ delimiter, [ limit, [ compare ]]])
입니다.
expression : 구분자(delimiter)를 포함한 문자열입니다.
delimiter : 문자열을 분리하기 위한 구분자입니다. 생략하면 " "(한 칸 공백)으로 인식합니다. 구분자가 없다면 전체 문자열이 하나의 배열로 반환됩니다.
limit : 반환될 배열의 개수입니다. 생략하거나 -1이면 분리된 문자열이 모두 반환됩니다.
compare : 이진 또는 문자열 비교 등 비교 방법을 지정하는 숫자인데, 중요하지 않으므로 자세한 것을 알고 싶으면 검색해보기 바랍니다
아래와 같이 한 줄로 간단히 구할 수 있습니다.
입력받은 함수명 문자열인 names_of_func을 쉼표(,)를 기준으로 분리해서 name_of_func배열에 저장합니다.
| name_of_func = Split(names_of_func, ",") |
또 하나의 장점은 분리된 문자열의 개수만큼 배열의 크기가 자동 설정된다는 것입니다.
(나) Microsoft 365 버전 미만인 경우
Microsoft 365 버전인 경우는 Split함수 한 줄로 끝나는데, Split함수처럼 작동하게 하려면 아래와 같이 복잡한 프로그램을 만들어야 합니다.
Dim ord As Integer '배열의 순번 지정
ord = 0 '배열은 순번이 0부터 시작함
ReDim name_of_func(100) '배열 초기화, 개수를 반드시 지정해야 하므로 크게 지정
Dim prev_i As Integer '이전 i의 위치 저장. 왜냐하면 쉼표 다음 문자 추출을 위해
' 쉼표로 구분해서 함수명을 배열에 저장하기
For i = 1 To Len(names_of_func)
If InStr(i, names_of_func, ",") > 0 Then
name_of_func(ord) = Trim(Mid(names_of_func, i, InStr(i, names_of_func, ",") - i))
prev_i = InStr(i, names_of_func, ",")
i = InStr(i, names_of_func, ",")
ord = ord + 1
End If
Next
i = prev_i + 1
name_of_func(ord) = Trim(Mid(names_of_func, i, Len(names_of_func) - prev_i))
ReDim Preserve name_of_func(ord)
| Dim ord As Integer '배열의 순번 지정 ord = 0 '배열은 순번이 0부터 시작함 |
배열에 저장할 때는 index 값이 있어야 하므로 ord라는 변수를 정수 형식으로 선언하고,
배열의 경우 index가 0부터 시작하므로 0으로 초기화합니다.
| ReDim name_of_func(100) '배열 초기화, 개수를 반드시 지정해야 하므로 크게 지정 |
배열에 값을 저장하기 위해 배열의 개수를 넉넉하게 100으로 지정했습니다.
작으면 함수명이 저장되지 않을 수 있지만 큰 것은 문제없기 때문입니다.
| Dim prev_i As Integer '이전 i의 위치 저장. 왜냐하면 쉼표 다음 문자 추출을 위해 |
문자열을 분리하는데 마지막에는 쉼표(,)가 없기 때문에 이전 쉼표의 위치부터 끝까지를 배열에 저장할 수 있도록 종전 쉼표의 위치를 저장할 변수가 있어야 하므로 prev_i 라고 지정한 것입니다.
| For i = 1 To Len(names_of_func) If InStr(i, names_of_func, ",") > 0 Then name_of_func(ord) = Trim(Mid(names_of_func, i, InStr(i, names_of_func, ",") - i)) prev_i = InStr(i, names_of_func, ",") i = InStr(i, names_of_func, ",") ord = ord + 1 End If Next i = prev_i + 1 name_of_func(ord) = Trim(Mid(names_of_func, i, Len(names_of_func) - prev_i)) ReDim Preserve name_of_func(ord) |
For ~ Next 반복문을 실행해 쉼표로 구분되는 문자열을 추출할 수 있는데,
마지막 쉼표부터 끝까지는 저장할 수 없으므로 반복문이 끝난 다음 함수명 배열 name_of_func(ord)에 마지막 쉼표이후 부분을 저장하는 부분이 있어야 합니다.
If InStr(i, names_of_func, ",") > 0 Then : 함수명 문자열 names_of_func에서 쉼표의 위치를 i번째부터 찾아 그 위치가 0보다 크다면, 다시 말해 "쉼표가 있다면"이 됩니다.
name_of_func(ord) = Trim(Mid(names_of_func, i, InStr(i, names_of_func, ",") - i)) :
names_of_func에서 이전 쉼표 또는 처음부터 다음 쉼표 사이의 문자열을 Mid함수를 이용해 추출한 다음 Trim함수로 좌우 공백을 제거합니다.
prev_i = InStr(i, names_of_func, ",") : prev_i에 쉼표가 있는 위치를 저장합니다. 다시 쉼표를 찾기 때문에 이전 쉼표 위치가 됩니다.
i = InStr(i, names_of_func, ",") : i번째부터 쉼표를 찾아 그 위치를 i에 저장합니다. 그렇지만 for 반복문 실행시 1을 더하기 때문에 쉼표 다음 위치가 됩니다.
ord = ord + 1 : 배열의 인덱스를 1 증가시킵니다.
i = prev_i + 1 : 반복문이 끝난 다음 이전 쉼표의 위치 다음부터 문자열을 추출하기 위해 prev_i변수에 1을 더한 다음 변수 i에 저장합니다.
name_of_func(ord) = Trim(Mid(names_of_func, i, InStr(i, names_of_func, ",") - i))
반복문안에 있는 문자 추출구문과 같은 것입니다.
ReDim Preserve name_of_func(ord) : 함수명 배열의 크기를 100개로 지정했으므로, 값을 보존하면서(Preserve) 배열의 크기를 ord로 축소합니다.
(4) 함수의 개수와 입력한 함수의 개수가 다른 경우 처리
| If UBound(name_of_func) > 0 _ And count_of_calc <> (UBound(name_of_func) + 1) Then MsgBox "함수의 개수와 입력한 함수명의 개수가 다릅니다." & vbCrLf _ & "다시 입력해 주세요." GoTo back End If |
If UBound(name_of_func) > 0란 함수명을 저장한 배열의 크기가 크다면으로 함수명이 입력된 경우이고,
And count_of_calc <> (UBound(name_of_func) + 1) Then : "입력받은 함수의 개수(count_of_calc)와 배열의 개수[(UBound(name_of_func) + 1)]가 다르다면"이라는 의미입니다.
Ubound는 배열의 인덱스중 가장 큰 값을 구해주는 함수로, 배열의 인덱스가 0부터 시작하기 때문에 개수를 구하려면 1을 더해야 합니다.
MsgBox "함수의 개수와 입력한 함수명의 개수가 다릅니다." & vbCrLf _
& "다시 입력해 주세요." :
vbCrLf는 줄을 바꾸라는 vb상수이므로,
"함수의 개수와 입력한 함수명의 개수가 다릅니다."의 아래 줄에 "다시 입력해 주세요."가 메시지 상자에 표시됩니다.
GoTo back : 실행 순서를 back위치로 옮기는 것으로 back의 위치는 함수의 개수를 입력받는 부분입니다. 따라서, 함수의 개수를 다시 입력하는 부분부터 다시 시작됩니다.
| back: '함수의 개수 입력 Dim count_of_calc As Integer count_of_calc = Application.InputBox("계산할 함수의 개수를 입력하세요.", Type:=1) |
(5) 행 수, 열 수, 계산할 마지막 행 구하기
| ' 현재 영역의 줄 수, 열 수와 계산할 범위의 끝 행을 변수에 저장 row_num = cur_range.Rows.Count col_num = cur_range.Columns.Count '계산할 마지막 열 구하기 Dim end_col_of_calc As Integer If UBound(name_of_func) = 0 Then end_row_of_calc = row_num - count_of_calc Else end_row_of_calc = row_num End If |
행 수는 현재 영역의 행(rows)의 수(count)이고,
열 수는 현재 영역의 열(columns)의 수(count)입니다.
계산할 마지막 행은 계산식이 있는 경우, 위 조건문에서는 If UBound(name_of_func) = 0 Then라고, 함수명의 크기가 0인 경우, 다시 말해 함수명 문자열을 입력하지 않은 경우, 다시 말해 이미 함수명이 있는 경우로 지정했습니다.
함수명이 있다면 end_row_of_calc = row_num - count_of_calc, 행 수에서 함수명의 개수(count_of_calc)를 빼고
없다면 end_row_of_calc = row_num, 행 수가 계산할 마지막 행 수가 됩니다.
(6) 계산식 입력
| '이중 반복문의 counter 변수 선언 Dim j As Integer '2열부터 열 수까지 반복 실행 For i = 2 To col_num If UBound(name_of_func) = 0 Then For j = 1 To count_of_calc cur_range(end_row_of_calc + j, i) = "=" & cur_range(end_row_of_calc + j, 1) _ & "(" & Range(cur_range(2, i), cur_range(end_row_of_calc, i)).Address(0, 0) & ")" Next Else For j = 0 To UBound(name_of_func) cur_range(end_row_of_calc + j + 1, 1) = name_of_func(j) cur_range(end_row_of_calc + j + 1, i) = "=" & name_of_func(j) & "(" & Range(cur_range(2, i), _ cur_range(end_row_of_calc, i)).Address(0, 0) & ")" Next End If Next |
행을 내려가면서 함수명을 처리하고, 열방향으로 반복해야 하기 때문에
Dim j As Integer, 이중 반복문의 counter를 j, 정수형식으로 선언합니다.
| For i = 2 To col_num If UBound(name_of_func) = 0 Then For j = 1 To count_of_calc cur_range(end_row_of_calc + j, i) = "=" & cur_range(end_row_of_calc + j, 1) _ & "(" & Range(cur_range(2, i), cur_range(end_row_of_calc, i)).Address(0, 0) & ")" Next |
For i = 2 To col_num : 2열부터 열 수까지 반복 실행하는데
If UBound(name_of_func) = 0 Then : 함수명 배열의 크기가 0이라면, 다시 말해 함수명 문자열을 입력하지 않은 경우, 즉 함수명이 이미 입력된 경우가 됩니다.
For j = 1 To count_of_calc : 1부터 함수의 개수만큼 반복합니다.
cur_range(end_row_of_calc + j, i) = "=" & cur_range(end_row_of_calc + j, 1) _
& "(" & Range(cur_range(2, i), cur_range(end_row_of_calc, i)).Address(0, 0) & ")" :
end_row_of_calc + j, 계산할 마지막 행에 1부터 함수의 개수만큼 더한 행, i열에
"=" & cur_range(end_row_of_calc + j, 1) : =을 입력한 다음
cur_range(end_row_of_calc + j, 1), 현재 영역의 계산할 마지막 행에 1부터 j값을 더한 행의 첫번째 열에 있는 함수명을 가져와서 &연산자로 결합하고,
& "(" & Range(cur_range(2, i), cur_range(end_row_of_calc, i)).Address(0, 0) & ")" :
괄호를 열고, 2행 i열부터 계산할 마직막 행의 i까지를 범위로 지정하고 다시 괄호를 닫습니다.
| Else For j = 0 To UBound(name_of_func) cur_range(end_row_of_calc + j + 1, 1) = name_of_func(j) cur_range(end_row_of_calc + j + 1, i) = "=" & name_of_func(j) & "(" & Range(cur_range(2, i), _ cur_range(end_row_of_calc, i)).Address(0, 0) & ")" Next End If |
else : 함수명을 입력한 경우는
For j = 0 To UBound(name_of_func) : j값이 0부터 함수명의 인덱스 최고값까지 반복합니다.
ur_range(end_row_of_calc + j + 1, 1) = name_of_func(j) : 계산할 마지막행에 j값을 더한 행의 첫번째 열에 함수명 배열의 j번째 인덱스에 해당하는 값을 넣습니다.
cur_range(end_row_of_calc + j + 1, i) = "=" & name_of_func(j) & "(" & Range(cur_range(2, i), _
cur_range(end_row_of_calc, i)).Address(0, 0) & ")" :
if문의 수식과 비슷한데 j가 0부터 시작하기 때문에 +1을 했고,
함수명을 배열 name_of_func의 j번째 인덱스에 해당하는 값을 바꾼 것이 다릅니다.
라. 매크로 실행
복잡해져서 오른쪽 함수 부분은 삭제했습니다.
(1) 함수명이 입력되어 있는 경우
B13셀부터 F15셀까지 지운다음 개발도구 - 매크로에서 calc_func을 실행하면
계산할 함수의 개수를 입력하라고 하는데, 기존에 함수가 3개 있으므로 3이라고 입력하고 확인버튼을 누릅니다.
입력되어 있다면 확인을 클릭하고, 아니라면 함수명을 쉼표로 구분하여 입력하라고 하는데, 함수명이 입력되어 있으므로 확인버튼을 누릅니다.
그러면 13행부터의 함수명을 참고해서 수식을 입력합니다.
(2) 함수명이 입력되어 있지 않은 경우
먼저 13행에서 15행을 선택한 후 삭제합니다.
표 영역 안에 커서를 넣은 다음 개발도구 - 매크로에서 calc_func을 실행하면
마찬가지로 함수의 개수를 입력하라고 하는데 3이라고 입력하고 확인 버튼을 입력합니다.
다음으로 함수명을 입력하라는 메시지가 나오는데,
sum, average, stdev 또는 공백없이 sum,average,stdev라고 입력하고 엔터키를 누르면,
함수명 3줄이 추가되고, 오른쪽에 함수명에 해당하는 수식이 입력됩니다.
이것도 이미 함수가 입력되어 있는 상태에서 다시 함수명을 추가로 입력하면 맨 아래줄에 함수명이 추가되는 문제점은 있습니다. 수식인지 체크하는 함수가 IsFormula라고 있기는 하나, 데이터도 수식으로 들어갈 수도 있기 때문에 적합하지 않습니다.
완성된 파일은 아래와 같습니다.
'EXCEL - VBA' 카테고리의 다른 글
| 엑셀 VBA - XML Parsing(XML 6.0 기준) (3) (LoadXML - 1) (0) | 2023.02.13 |
|---|---|
| 엑셀 VBA - XML Parsing(XML 6.0 기준) (1) (0) | 2023.02.08 |
| CurrentRegion 속성을 이용한 합계 등 계산(2) (0) | 2023.02.06 |
| CurrentRegion 속성을 이용한 합계 등 계산(1) (0) | 2023.02.03 |
| CurrentRegion의 속성 알아보기 (0) | 2023.01.30 |