사용자정의 숫자 서식 좀 편하게 설정하기
사용자정의 표시형식
기존에는 몇가지 서식에 대하여, 매크로 버튼을 만들어 사용하고 있었다.
불행하게도 이는 TEXT 함수 안에 적용하기가 어려워… 같은 셀 안에 두개의 서식을 사용하기가 어렵다.
+114
(+2%)
TEXT 함수를 써서 간단하게 해결할 수 있기는 하지만, 매번 표시형식을 직접 적어주는 건 매우 귀찮은 일이다.
이 문제를 해결하기 위해 몇 가지 방법을 고민해 보았다.
#1 VBA로 MDF 만들기
자주 쓰는 서식을 몇가지 MDF 로 저장해두고, 문자 값으로 반환하도록 하자.
숫자를 천단위나, 백만단위 등으로 표기하거나, 퍼센트를 기재해주는 방식이다. 양수나 음수를 구분할수 있도록 적어뒀다.
sample.bas
Function myfmt(fmtName As String) As String
Select Case UCase$(Trim(fmtName))
Case "일"
myfmt = "#,##0;[Red]-#,##0;-"
Case "천"
myfmt = "#,##0,;[Red]-#,##0,;-"
Case "백만"
myfmt = "#,##0,;[Red]-#,##0,;-"
Case "일_회"
myfmt = "#,##0_-;[Red]-#,##0_-;-_-"
Case "천_회"
myfmt = "#,##0,_-;[Red]-#,##0,_-;-_-"
Case "백만_회"
myfmt = "#,##0,,_-;[Red]-#,##0,,_-;-_-"
Case "일_회_기"
myfmt = "+#,##0_-;[Red]-#,##0_-;-_-"
Case "천_회_기"
myfmt = "+#,##0,_-;[Red]-#,##0,_-;-_-"
Case "백만_회_기"
myfmt = "+#,##0,,_-;[Red]-#,##0,,_-;-_-"
Case "퍼"
myfmt = "0%;[red]0%;-"
Case "퍼_기"
myfmt = "+0%;[red]-0%;-"
Case Else
myfmt = "@"
End Select
End Function
그런데 .. MDF를 사용자 함수를 XLAM 파일로 사용하더라도 내가 파일을 다른사람에게 전달을 하면 함수가 무용지물이 된다.
다른 방법을 찾아야 한다.
#2 이름정의로 서식 지정하기
이름정의는 다재다능하다. 특히 불필요한 참조시트나 테이블을 생성하고 싶지 않을 때 유용한데.. 서식을 반환하는 이름정의를 걸어두면 찾기도 편하고, 불필요한 함수를 호출할 필요도 없다.
사용하기는 무지 간단한데, 대상영역에 서식을 넣어주기만 하면 된다.
문제는 위와 비슷하지만 다른데, 같은 파일을 다른 사람에게 넘겨줄때는 문제가 아니지만, 내가 새로운 파일을 사용할 떄는 또 이름정의를 해야하는 수고로움이 있다.
간단히 매크로를 짜서 필요한 경우, 서식을 이름정의로 옮겨주면 된다. 셋을 한번만 짜두면 두고두고 사용이 가능하다는 장점이 있다.
내가 지정한 사용자 서식은 숫자나 백분율에 관한 부분이다.
sample.bas
Option Explicit
' 1) 여기에서 "사용자 서식 리스트"만 관리하면 됩니다.
' 형식: Array( Array("이름정의명", "서식문자열"), ... )
Private Function USER_FORMAT_LIST() As Variant
USER_FORMAT_LIST = Array( _
Array("_서식_숫자_일", "#,##0;[Red]-#,##0;-"), _
Array("_서식_숫자_천", "#,##0,;[Red]-#,##0,;-"), _
Array("_서식_숫자_백만", "#,##0,,;[Red]-#,##0,,;-"), _
Array("_서식_숫자_일_회계", "#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서식_숫자_천_회계", "#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서식_숫자_백만_회계", "#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서식_숫자_일_회계_기호", "+#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서식_숫자_천_회계_기호", "+#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서식_숫자_백만_회계_기호", "+#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서식_백분율", "0%;[red]0%;-"), _
Array("_서식_백분율_기호", "+0%;[red]0%;-") _
)
End Function
' 2) 실행하면 ActiveWorkbook에 이름정의로 등록(중복이면 덮어쓰기)
Public Sub Register_UserFormats_ToNames()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim items As Variant, it As Variant
Dim nm As String, fmt As String, refersToFormula As String
items = USER_FORMAT_LIST()
For Each it In items
nm = CStr(it(0))
fmt = CStr(it(1))
' 이름정의의 RefersTo는 "수식"이어야 하므로 텍스트는 ="..." 형태로 저장
' 큰따옴표(")가 서식문자열에 들어갈 경우를 대비해 이스케이프 처리
fmt = Replace(fmt, """", """""")
refersToFormula = "=""" & fmt & """"
' 중복이면 무조건 덮어쓰기: 있으면 삭제 후 재등록
On Error Resume Next
wb.Names(nm).Delete
On Error GoTo 0
wb.Names.Add Name:=nm, RefersTo:=refersToFormula
Next it
MsgBox "사용자 서식 리스트를 이름정의로 등록 완료", vbInformation
End Sub
프로시저를 실행하면 다음과 같이 사전 설정한 사용자서식이 이름정의에 등록된다.
TEXT 함수 사용시, 리스트형태로 사전 설정한 사용자 서식이 보이기 때문에, 헤깔릴 위험도 줄어든다.
적용된 결과는 다음과 같다.
TEXT 함수이기 때문에 CHAR(10)과 함께 두줄을 표기하고, 각 줄에 다른 서식을 적용하는 것도 가능하다.
#3 이름정의의 한계, 어쩔수 없이 사용해야하는 셀스타일
이름정의는 TEXT 함수를 쓰기 위함이었는데, TEXT 함수를 사용하니, 문제가 있다. 바로, TEXT 함수의 반환값이 TEXT가 되버린다는 것이다.
즉, 1000000 을 백만단위 표기로 바꾸면 1이 되는데, 표시형식 뿐 아니라 값까지 1이 되어버린다는 것이다. 즉, 연산을 하게 되면, 1백만이 아니라 1이 연산이 된다.
그래서…
맨 위의 예시처럼 두줄에 각각의 연산을 하는 상황이 아니라면 셀스타일에 등록을 하는 것이 낫다.
이 또한 귀찮으니, VBA에 추가를 하면 다음과 같다.
최종 프로시저는 Register_UserFormats_ToNames 가 된다.
이름이 길면 셀스타일에 나오지 않기 때문에 “서….” 로 시작하도록 축약해서 적었다.
sample.bas
Option Explicit
' 1) 여기에서 "사용자 서식 리스트"만 관리하면 됩니다.
' 형식: Array( Array("이름정의명", "서식문자열"), ... )
Private Function USER_FORMAT_LIST() As Variant
USER_FORMAT_LIST = Array( _
Array("_서_일", "#,##0;[Red]-#,##0;-"), _
Array("_서_천", "#,##0,;[Red]-#,##0,;-"), _
Array("_서_백만", "#,##0,,;[Red]-#,##0,,;-"), _
Array("_서_일_회", "#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서_천_회", "#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서_백만_회", "#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서_일_회_기", "+#,##0_-;[Red]-#,##0_-;-_-"), _
Array("_서_천_회_기", "+#,##0,_-;[Red]-#,##0,_-;-_-"), _
Array("_서_백만_회_기", "+#,##0,,_-;[Red]-#,##0,,_-;-_-"), _
Array("_서_날", "YYYY-MM-DD"), _
Array("_서_날_요", "YYYY-MM-DD(aaa)"), _
Array("_서_율", "0%;[red]0%;-"), _
Array("_서_율_기", "+0%;[red]0%;-") _
)
End Function
' 2) 실행하면 ActiveWorkbook에 이름정의로 등록(중복이면 덮어쓰기)
Public Sub Register_UserFormats_ToNames()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim items As Variant, it As Variant
Dim nm As String, fmt As String, refersToFormula As String
items = USER_FORMAT_LIST()
For Each it In items
nm = CStr(it(0))
fmt = CStr(it(1))
' 이름정의의 RefersTo는 "수식"이어야 하므로 텍스트는 ="..." 형태로 저장
' 큰따옴표(")가 서식문자열에 들어갈 경우를 대비해 이스케이프 처리
fmt = Replace(fmt, """", """""")
refersToFormula = "=""" & fmt & """"
' 중복이면 무조건 덮어쓰기: 있으면 삭제 후 재등록
On Error Resume Next
wb.Names(nm).Delete
On Error GoTo 0
wb.Names.Add Name:=nm, refersTo:=refersToFormula
Next it
MsgBox "사용자 서식 리스트를 이름정의로 등록 완료", vbInformation
Call Create_NumberOnly_Styles_From_Names
End Sub
Private Function BaseName(ByVal fullName As String) As String
If InStr(fullName, "!") > 0 Then
BaseName = Split(fullName, "!")(1)
Else
BaseName = fullName
End If
End Function
Private Function ExtractFormatString(ByVal refersTo As String) As String
Dim s As String
s = refersTo ' 예: ="#,##0,,"
If Left$(s, 2) = "=""" And Right$(s, 1) = """" Then
s = Mid$(s, 3, Len(s) - 3)
s = Replace(s, """""", """")
ExtractFormatString = s
Else
ExtractFormatString = ""
End If
End Function
Public Sub Create_NumberOnly_Styles_From_Names()
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim n As Name
Dim styleName As String, fmt As String
Dim st As Style
For Each n In wb.Names
styleName = BaseName(n.Name)
If Left$(styleName, 3) = "_서_" Then
fmt = ExtractFormatString(n.refersTo)
If fmt = "" Then GoTo NextName
' 기존 스타일 삭제
On Error Resume Next
wb.Styles(styleName).Delete
On Error GoTo 0
' 스타일 생성
Set st = wb.Styles.Add(Name:=styleName)
' ?? 포함 항목 제어 (핵심)
With st
.IncludeAlignment = False
.IncludeFont = False
.IncludeBorder = False
.IncludePatterns = False ' Fill
.IncludeProtection = False
.IncludeNumber = True ' ← 이것만 포함
.NumberFormat = fmt
End With
End If
NextName:
Next n
MsgBox "Number만 포함된 스타일 생성 완료", vbInformation
End Sub
다음과 같이 셀 스타일에 등록이 된 것을 확인할 수 있다. 이제 이름정의로 TEXT 함수를 사용하거나, Custom 셀스타일을 적용할 수 있다.
사용을 위한 프로세스는 다음과 같다.
✶ 엑셀 호출시 불러들일 xlam 파일을 xlstart 폴더에 저장 (이름 자유)
✶ 이름이 macro.xlam 이면, 해당 파일에 위에 적은 코드를 신규 프로시저로 복사 후 저장, 리본메뉴에 버튼 등록.
✶ 해당 파일이 엑셀 시작될때마다, 뜰 것이고, 필요한 workbook 활성화 상태에서 리본메뉴에서 버튼을 클릭하면 이름정의로 저장
conclusion
다른 사람과 함께 해야하는 작업이라면, 엑셀에서 제공하는 기본기능을 충실하게 사용하는 것이 가장 좋은 방법일 수 있다.
이상 끝.