Excel VBA에서 표시 형식 지정하기

Excel VBA에서 표시 형식 지정하기

표시 형식는 Excel 워크시트에서 모든 종류의 형식으로 제공됩니다. 다양한 표시 형식을 사용할 수 있는 Excel의 팝업 창에 이미 익숙할 것입니다:

표시 형식을 사용하면 숫자를 더 쉽게 읽고 이해할 수 있습니다. 셀에 입력된 숫자의 Excel 기본값은 ‘일반’ 서식으로, 숫자가 입력한 대로 정확하게 표시됩니다.

예를 들어 4238을 입력하면 소수점이나 천 단위 구분 기호 없이 4238로 표시됩니다. 9325.89와 같은 소수가 포함된 숫자는 십진수와 소수점이 함께 표시됩니다. 즉, 십진수만 있는 숫자와 함께 열에 정렬되지 않고 매우 지저분하게 보입니다.

또한 천 단위 구분 기호가 표시되지 않으면 개별 숫자를 세지 않고는 숫자가 얼마나 큰지 알기 어렵습니다. 입력된 숫자가 수백만 단위인지, 수천만 단위인지 한눈에 알기 어렵습니다.

이 경우 사용자 입장에서는 숫자를 읽고 비교하기가 어렵습니다.

VBA에서는 Excel에서와 동일한 범위의 표시 형식을 사용할 수 있습니다. 워크시트의 셀에 입력된 값뿐만 아니라 메시지 박스, 사용자 정의 폼, 차트 및 그래프, 워크시트의 왼쪽 하단에 있는 Excel 상태 표시줄 등에도 적용됩니다.

Format 함수은 가시성 측면에서 VBA에서 매우 유용한 함수입니다. 하지만 제공되는 코드를 사용하는 측면에서는 매우 복잡합니다.

 

VBA에서 Format 함수를 사용하는 방법

메시지 박스에 숫자를 표시하는 경우 Format 함수를 직접 사용할 수 있습니다:

MsgBox Format(1234567.89, "#,##0.00")

이렇게 하면 쉼표를 사용하여 천 단위로 구분하고 소수점 이하 2자리를 표시하여 긴 자리수의 숫자를 표시합니다. 결과는 1,234,567.89가 됩니다. 해시 대신 0을 사용하면 소수점이 없는 정수의 경우 소수점이 00으로 표시되고 자리수가 표현식보다 적을 경우 해당 자리에 0이 표시됩니다.

해시태그 기호(#)는 숫자 자리 표시자를 나타내며, 해당 위치에 사용할 수 있는 숫자가 있으면 숫자를 표시하고 그렇지 않으면 아무것도 표시하지 않습니다.

Format 함수를 사용하여 개별 셀이나 셀 범위를 지정하여 서식을 변경할 수 있습니다:

Sheets("Sheet1").Range("A1:A10").NumberFormat = "#,##0.00"

이 코드는 셀 범위(A1~A10)를 쉼표로 천을 구분하고 소수점 이하 2자리를 표시하는 사용자 지정 서식으로 설정합니다.

Excel 에서 셀의 서식을 확인하면 새로운 사용자 지정 서식이 생성된 것을 확인할 수 있습니다.

Excel 창의 왼쪽 하단에 있는 Excel 상태 표시줄에서도 숫자의 서식을 지정할 수 있습니다:

Application.StatusBar = Format(1234567.89, "#,##0.00")

다음 코드를 사용하여 상태 표시줄에서 이 값을 지울 수 있습니다:

Application.StatusBar = ""

문자열 서식 만들기

이 예제에서는 각 숫자 뒤에 ‘총 판매금액’ 텍스트를 추가하고 천 단위 구분 기호를 포함합니다.

Sheets("Sheet1").Range("A1:A6").NumberFormat = "#,##0.00"" 총 판매금액"""

코드 실행 결과의 예는 아래와 같습니다:

A6 셀에는 ‘SUM’ 수식이 있으며, 여기에는 서식을 지정할 필요 없이 ‘총 판매금액’ 텍스트가 포함됩니다. 위의 코드에서와 같이 서식을 적용하면 ‘총 판매금액’의 추가 인스턴스를  A6셀에 넣지 않습니다.

이렇게 하면 셀에 문자가 표시되지만 여전히 숫자 형식으로 표시됩니다. ‘SUM’ 수식은 숫자의 표시형식 방식이 아니라 백그라운드에서 숫자 값을 사용하기 때문에 문제없이 작동합니다.

출력 포맷의 쉼표는 천 단위 구분 기호 역할을 합니다. 코드상에서 쉼표는 문자열에 한 번만 입력하면 됩니다. 숫자가 수백만 또는 수십억에 이르는 경우에도 세자리수 단위로 끊어서 숫자를 구분합니다.

출력 포맷의 0(0)은 숫자 자리 표시자입니다. 숫자가 있으면 숫자를 표시하고, 없으면 0을 표시합니다. 이 위치 지정은 서식의 균일성 측면에서 매우 중요합니다.

출력 포맷에서 해시 문자(#)는 숫자가 없으면 아무것도 표시되지 않습니다. 그러나 .8과 같은 숫자(모두 소수점)가 있는 경우 다른 숫자와 일치하도록 0.80으로 표시합니다.

출력 포맷에서 소수점 왼쪽에 0을 하나, 소수점 오른쪽에 0을 두 개 사용하면 필요한 결과(0.80)를 얻을 수 있습니다.

소수점 오른쪽에 0이 하나만 있으면 결과는 ‘0.8’이 되고 모든 값이 소수점 한 자리까지 표시됩니다.

 

출력 포맷 사용해서 정렬하기

각 숫자의 소수점 자릿수에 상관없이 모든 소수점이 서로 바로 아래에 오도록 범위의 모든 소수 숫자를 소수점에 맞춰 정렬하고 싶을 수 있습니다.

이를 위해 서식 문자열 내에 물음표(?)를 사용할 수 있습니다. ‘?’는 숫자를 사용할 수 있는 경우 숫자가 표시되거나 공백이 표시됨을 나타냅니다.

Sheets("Sheet1").Range("A1:A6").NumberFormat = "#,##0.00??"

위의 코드로 숫자는 다음과 같이 표시됩니다:

이제 모든 소수점이 서로 아래에 정렬됩니다. A5 셀에는 소수점 이하 자릿수가 세 자리이므로 일반적으로 정렬이 어긋나지만 ‘?’ 문자를 사용하면 모든 것이 완벽하게 정렬됩니다.

 

출력포맷에 리터럴 문자 사용하기

출력포맷 앞에 백슬래시(\)를 추가하여 리터럴 문자를 추가할 수 있습니다.

숫자에 현지 화폐를 기반으로 하지 않는 특정 통화 표시기를 표시하고 싶다고 가정해 보겠습니다. 문제는 통화 표시기를 사용하면 Excel이 자동으로 사용자의 국가를 참조하여 Windows 제어판에 설정된 국가에 적합한 통화 표시기로 변경한다는 것입니다. 이는 Excel 응용 프로그램이 다른 국가에 배포되는 경우 현지 화폐가 무엇이든 통화 표시기를 항상 동일하게 유지하려는 경우에 영향을 미칠 수 있습니다.

다음 예제에서 숫자가 백만 단위임을 표시할 수도 있습니다:

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00 \m"

이렇게 하면 워크시트에 다음과 같은 결과가 생성됩니다:

백슬래시를 사용하여 리터럴 문자를 표시할 때 문자열 내의 각 개별 문자에 대해 백슬래시를 사용할 필요가 없습니다. 아래와 같이 사용할 수 있습니다:

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00 \mill"

이렇게 하면 서식 지정된 범위 내의 모든 숫자 뒤에 ‘mill’이 표시됩니다.

대부분의 문자를 리터럴로 사용할 수 있지만 0, #,? 등의 예약어는 사용할 수 없습니다.

 

출력 포맷에서 쉼표의 기능

쉼표를 사용하여 큰 숫자를 구분하는 데 쉼표를 사용할 수 있다는 것을 이미 살펴보았지만 쉼표를 다른 방식으로도 사용할 수 있습니다.

쉼표를 형식 문자열의 숫자 부분 끝에 사용하면 쉼표는 수천 단위의 스케일러 역할을 합니다. 즉, 쉼표가 있을 때마다 각 숫자를 1,000으로 나눕니다.

예제 데이터에서는 수백만 단위라는 표시와 함께 표시하고 있습니다. 형식 문자열에 쉼표를 하나 삽입하면 해당 숫자를 1,000으로 나눈 값을 표시할 수 있습니다.

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00,\m"

 

이렇게 하면 원래 숫자는 여전히 셀의 배경에 있지만 숫자를 1,000으로 나눈 값이 표시됩니다.

서식 문자열에 쉼표 두 개를 넣으면 숫자가 백만 분의 1로 나뉩니다.

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00,,\m"

아래는 쉼표 하나만 사용한 결과입니다(1,000으로 나누기):

출력 포맷에 조건부 서식 만들기

Excel에서 조건부 서식을 설정할 수도 있지만, VBA 코드 내에서 조건부 서식을 설정할 수도 있습니다. 즉, 프로그래밍 방식으로 서식 문자열을 조작하여 변경할 수 있습니다.

서식 문자열 내에서 최대 4개의 섹션을 사용할 수 있습니다. 각 섹션은 세미콜론(;)으로 구분됩니다. 4개의 섹션은 양수, 음수, 0, 텍스트 입니다.

Range("A1:A7").NumberFormat = "#,##0.00;[Red]-#,##0.00;[Green] #,##0.00;[Blue]”

이 예제에서는 동일한 해시, 쉼표 및 0 문자를 사용하여 천 단위 구분 기호 및 소수점 2개를 제공하지만 각 값 유형에 대해 서로 다른 섹션이 있습니다.

첫 번째 섹션은 양수를 위한 섹션으로, 형식 면에서 앞서 살펴본 것과 다르지 않습니다.

두 번째 섹션은 음수를 위한 섹션으로 대괄호 안의 색상(빨간색)을 적용합니다. 앞에 마이너스(-) 기호가 추가되었다는 점을 제외하면 형식은 양수와 동일합니다.

세 번째 섹션은 양수와 동일한 숫자 문자열과 함께 대괄호 안의 색상(녹색)을 사용합니다.

마지막 섹션은 텍스트 값을 위한 섹션으로, 대괄호 안에 색상(파란색)만 있으면 됩니다.

이 출력 포맷을 적용한 결과입니다:

 

출력 포맷 내에 조건을 추가할 수 있습니다. 10,000을 초과하는 모든 양수는 녹색으로, 그 외의 모든 숫자는 빨간색으로 표시하려면 이 서식 문자열을 사용할 수 있습니다:

Range("A1:A7").NumberFormat = "[>=10000][Green]#,##0.00;[<10000][Red]#,##0.00"

이 형식 문자열에는 대괄호 안에 설정된 >=10000에 대한 조건이 포함되어 있으므로 숫자가 10000보다 크거나 같은 경우에만 녹색이 사용됩니다.

결과는 아래와 같습니다:

출력 포맷에 분수 사용하기

분수는 일반적으로 사람들에게 익숙한 소수와 동일하기 때문에 스프레드시트에서는 분수보다 소수가 더 많이 사용됩니다.

그러나 때로는 분수가 사용되어야 할 경우도 있습니다. 이 예에서는 달러와 센트를 표시합니다:

Range("A1:A7").NumberFormat = "#,##0 "" dollars and "" 00/100 ""  cents """

코드 실행 결과는 아래와 같습니다:

숫자가 텍스트로 표시되더라도 데이터 자체는 숫자로 인식이 되기 때문에 모든 Excel 수식을 사용할 수 있습니다.

출력 포맷에 날짜 및 시간 형식 사용하기

날짜는 실제로 숫자이며 숫자와 동일한 방식으로 날짜 서식을 사용할 수 있습니다. 날짜를 표시형식으로 서식을 지정하면 소수점 왼쪽에 큰 숫자와 소수점 이하 자릿수가 표시됩니다. 소수점 왼쪽의 숫자는 1900년 1월 1일부터 시작하는 일 수를 표시하고 소수점 이하 자릿수는 24시간을 기준으로 한 시간을 표시합니다.

MsgBox Format(Now(), "dd-mmm-yyyy")

이렇게 하면 현재 날짜의 서식이 ’12-Mar-2023’로 표시됩니다. 월에 ‘mmm’를 사용하면 월 이름의 처음 세 문자가 표시됩니다. 전체 월 이름을 표시하려면 ‘mmmm’을 사용합니다.

출력 포맷에 시간을 포함할 수 있습니다:

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM")

이렇게 하면 ’12-Mar-2023 10:30 AM’이 표시됩니다.

‘hh:mm’는 시와 분을 나타내며, AM/PM은 24시간 시계가 아닌 12시간 시계를 사용합니다.

출력 포맷에 텍스트 문자를 통합할 수 있습니다:

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM"" 오늘""")

이렇게 하면 ’12-Mar-2023 10:31 AM 오늘’이 표시됩니다.

숫자 형식 문자열과 같은 방식으로 앞에 백슬래시를 사용하여 리터럴 문자를 사용할 수도 있습니다.

 

미리 정의된 서식

Excel에는 코드에서 사용할 수 있는 숫자 및 날짜에 대한 여러 가지 기본 제공 형식이 있습니다. 이러한 서식은 주로 표시 형식에 있는  것을 반영하지만, 일부는 팝업 창에서 일반적으로 사용할 수 없는 것들도 있습니다. 또한 소수점 이하 자릿수 또는 천 단위 구분 기호 사용 여부에 대한 자유도가 없습니다.

General Number

이 형식은 숫자를 있는 그대로 표시합니다.

MsgBox Format(1234567.89, "General Number")

결과는 1234567.89 입니다.

Currency

MsgBox Format(1234567.894, "Currency")

이 형식은 숫자 앞에 통화 기호를 추가합니다(예: 국가에 따라 $, £). 또한 소수점 이하 2자리까지 숫자의 서식을 지정하고 쉼표로 천 단위를 구분합니다.

결과는 ₩1,234,567.89 입니다.

Fixed

MsgBox Format(1234567.894, "Fixed")

이 형식은 소수점 왼쪽에 한 자리 이상 표시하고 오른쪽에는 두 자리만 표시합니다.

결과는 1234567.89 입니다.

Standard

MsgBox Format(1234567.894, "Standard")

천 단위 구분 기호로 숫자를 표시하면서 소수점 이하 두 자리까지만 표시합니다.

결과는 1,234,567.89 입니다.

Percent

MsgBox Format(1234567.894, "Percent")

숫자에 100을 곱하고 숫자 끝에 백분율 기호(%)를 추가합니다. 형식은 소수점 이하 2자리까지 표시됩니다.

결과는 123456789.40% 입니다.

Scientific

MsgBox Format(1234567.894, "Scientific")

숫자를 지수 형식으로 변환합니다.

결과는 1.23E+06 입니다.

Yes/No

MsgBox Format(1234567.894, "Yes/No")

숫자가 0이면 ‘아니요’를 표시하고, 그렇지 않으면 ‘예’를 표시합니다.

결과는 ‘Yes’ 입니다.

True/False

MsgBox Format(1234567.894, "True/False")

숫자가 0이면 ‘False’를 표시하고, 그렇지 않으면 ‘True’를 표시합니다.

결과는 ‘True’ 입니다.

On/Off

MsgBox Format(1234567.894, "On/Off")

숫자가 0이면 ‘Off’를 표시하고, 그렇지 않으면 ‘On’을 표시합니다.

결과는 ‘On’ 입니다.

General Date

MsgBox Format(Now(), "General Date")

이 함수는 날짜를 오전/오후 표기법을 사용하여 날짜와 시간으로 표시합니다. 날짜가 표시되는 방식은 Windows 제어판(시계 및 국가 | 국가 또는 지역)의 설정에 따라 다릅니다. ‘mm/dd/yyyy’ 또는 ‘dd/mm/yyyy’로 표시될 수 있습니다.

결과는 ‘2023-03-12 오후 2:05:23’ 입니다.

Long Date

MsgBox Format(Now(), "Long Date")

이렇게 하면 Windows 제어판(시계 및 국가 | 국가 또는 지역)에 정의된 대로 자세한 날짜가 표시됩니다. 시간은 포함되지 않는다는 점에 유의하세요.

결과는 ‘2023년 3월 12일 일요일’ 입니다.

Medium Date

MsgBox Format(Now(), "Medium Date")

이 함수는 Windows 제어판의 국가별 간단한 날짜 설정에 정의된 대로 날짜를 표시합니다.

결과는 ’23년 3월 12일’ 입니다.

Short Date

MsgBox Format(Now(), "Short Date")

Windows 제어판(시계 및 국가 | 국가 또는 지역)에 정의된 대로 간단한 날짜를 표시합니다. 날짜가 표시되는 방식은 국가에 따라 다릅니다. ‘mm/dd/yyyy’ 또는 ‘dd/mm/yyyy’로 표시될 수 있습니다.

결과는 ‘2023-03-12’ 입니다.

Long Time

MsgBox Format(Now(), "Long Time")

Windows 제어판(시계 및 국가 | 국가 또는 지역)에 정의된 자세한 시간을 표시합니다.

결과는  오후 2:15:34′ 입니다.

Medium Time

MsgBox Format(Now(), "Medium Time")

Windows 제어판에서 국가별 간단한 시간 설정에 정의된 데로 시간을 표시합니다. 일반적으로 시, 분, 초 및 오전/오후 형식을 사용하여 12시간 형식으로 설정됩니다.

결과는 ‘오후 02:18’ 입니다.

Short Time

MsgBox Format(Now(), "Short Time")

Windows 제어판(시계 및 지역 | 지역)에 정의된 간단한 시간을 표시합니다. 일반적으로 시와 분을 포함한 24시간 형식으로 설정됩니다.

결과는 ’14:19’ 입니다.

Excel의 사전 정의된 날짜 및 시간 형식을 사용할 때의 위험성

Excel VBA에서 날짜 및 시간에 대해 사전에 정의 된 형식을 사용할 경우 Windows 제어판의 국가 설정 항목에 따라 크게 달라집니다.

사용자는 이러한 설정을 쉽게 변경할 수 있으며, 이는 Excel에서 날짜와 시간이 표시되는 방식에 영향을 미칩니다.

예를 들어, VBA 코드 내에서 사전 정의된 형식을 사용하는 Excel 애플리케이션을 개발하는 경우 사용자가 다른 국가에 있거나 다른 국가 설정을 사용한다면 개발한 의도와 다르게 동작할 수 있습니다. 열 너비가 날짜 정의에 맞지 않거나 사용자 양식에서 콤보 상자(드롭다운) 컨트롤과 같은 Active X 컨트롤의 폭이 너무 좁아 날짜와 시간이 제대로 표시되지 않을 수 있습니다.

Excel 애플리케이션을 개발할 때 대상 사용자가 어느 국가에 있는지, 어떤 국가 설정으로 윈도우를 이용중인지 고려해야 합니다.

 

숫자에 대한 사용자 지정 표시형식

출력 포맷을 정의할 때 사용할 수 있는 여러 가지 매개변수가 있습니다:

기호	설명
Null String	서식 없음
0	숫자 자리 표시자. 숫자 또는 0을 표시합니다. 해당 위치에 자릿수가 있으면 해당 자릿수를 표시하고, 그렇지 않으면 0을 표시합니다. 0보다 자릿수가 적으면 선행 또는 후행에 0이 표시됩니다. 소수점 뒤의 자릿수가 0보다 많으면 0으로 표시된 소수점 이하 자릿수에서 반올림합니다. 소수점 앞 자릿수가 0보다 많으면 정상적으로 표시됩니다.
#	숫자 자리 표시자. 숫자를 표시하거나 아무것도 표시하지 않습니다. 선행 및 후행에 0이 표시되지 않는다는 점을 제외하면 위에 설명된 0 과 동일하게 작동합니다. 예를 들어 0 자리 표시자를 사용하면 0.75가 표시되지만 # 자리 표시자를 사용하면 .75가 됩니다.
. 소수점	출력 포맷당 하나만 허용됩니다. 이 문자는 Windows 제어판의 설정에 따라 다릅니다.
%	백분율 자리 표시자. 숫자에 100을 곱하고 출력 포맷이 표시된 위치에 % 문자가 입력됩니다.
, (comma)	천 단위 구분 기호. 0 또는 # 자리 표시자를 사용하고 출력 포맷에 쉼표가 포함된 경우에 사용됩니다. 소수점 왼쪽에 있는 쉼표 하나는 가장 가까운 천 단위로 반올림함을 나타냅니다. 예: ##0, 천 단위 구분 기호 왼쪽에 인접한 쉼표 두 개는 가장 가까운 백만 단위로 반올림을 나타냅니다. 예: ##0,,
E- E+	숫자를 지수 형식으로 표시합니다.
: (콜론)	시간 구분 기호 – 시간, 분, 초를 나누기 위해 시간 서식을 지정할 때 사용합니다.
/	날짜 구분 기호 – 날짜의 서식을 지정할 때 사용됩니다.
– + £ $ ( )	리터럴 문자를 표시합니다. 여기에 나열된 문자가 아닌 다른 문자를 표시하려면 앞에 백슬래시(\)를 넣습니다.

날짜와 시간에 대한 사용자 지정 표시형식

이러한 문자는 모두 날짜 및 시간 서식을 지정할 때 출력 포맷에 사용할 수 있습니다:

기호	의미
c	날짜를 ddddd로 표시하고 시간을 ttttt로 표시합니다.
d	선행되는 0 없이 날짜를 표시합니다.
dd	선행되는  0이 있는 날짜를 표시합니다.
ddd	요일을 약어(Sun – Sat)로 표시합니다.
dddd	요일의 전체 이름을 표시합니다(Sunday – Saturday).
ddddd	제어판의 국가 설정에 있는 간단한 날짜에 따라 날짜를 표시합니다.
dddddd	제어판의 국가 설정에 있는 자세한 날짜에 따라 날짜를 표시합니다.
w	요일을 숫자로 표시합니다(1 = 일요일).
ww	주를 숫자로 표시합니다(1-53).
m	선행되는 0 없이 월을 숫자로 표시합니다.
mm	선행되는 0이 포함된 숫자로 월을 표시합니다.
mmm	월을 약어로 표시합니다 (Jan-Dec)
mmmm	월의 전체 이름을 표시합니다 (January – December)
q	연도의 분기를 숫자(1~4)로 표시합니다.
y	연도중 하루를 숫자로 표시합니다(1-366).
yy	연도를 두 자리 숫자로 표시합니다.
yyyy	연도를 4자리 숫자로 표시합니다.
h	선행되는 0 없이 시를 숫자로 표시합니다.
hh	선행되는 0이 있는 숫자로 시를 표시합니다.
n	분을 선행되는 0이 없는 숫자로 표시합니다.
nn	분을 선행되는 0이 있는 숫자로 표시합니다.
s	선행되는 0이 없는 숫자로 초를 표시합니다.
ss	선행되는 0이 있는 숫자로 초를 표시합니다.
ttttt	숫자로 표현된 시간을 완전한 시간으로 표시합니다.
AM/PM	오전/오후 12시간 시계를 사용하고 AM 또는PM을 표시하여 정오 이전 또는 이후를 표시합니다.
am/pm	오전/오후 12시간 시계를 사용하고 am또는pm을 표시하여 정오 이전 또는 이후를 표시합니다.
A/P	오전/오후 12시간 시계를 사용하고 A 또는 P를 표시하여 정오 이전 또는 이후를 표시합니다.
a/p	오전/오후 12시간 시계를 사용하고 a 또는 p를 표시하여 정오 이전 또는 이후를 표시합니다.