도움되는 블로그

[엑셀 VBA 예제3] 엑셀 색깔별 합계 구하기

이번 포스팅에선 엑셀 색깔별 합계를 구하는 예제를 살펴보겠습니다.

선택된 범위 안에서, 선택된 셀의 색깔로 칠해진 값의 합을 구하는 예제입니다.

사용하기 편하도록 Function 을 사용해서 사용자정의 함수를 만들어 보겠습니다.

예제를 받고 따라와주세요.

실전예제3.xlsm

사용자정의 함수란?

사용자정의 함수는 엑셀에 기본으로 탑제된 함수 이외에 필요한 기능이 있을 경우, 사용자가 직접 만드는 함수를 말합니다.

일반 함수와 같이 "=함수이름(인수1,인수2,인수3......,인수N)" 의 형식으로 엑셀에서 사용할 수 있습니다.

사용자정의 함수 코드는 모듈을 생성해서 작성해야 합니다.

위와 같이 숫자가 담긴 셀에 색깔이 채워져 있을 경우, 색깔 별로 합계를 구하려고 합니다.

※ 사용자정의 함수는 시트에 코드를 짜면 안되고 꼭 모듈을 생성해서 만들어 줘야 합니다.

코드를 작성한 후 엑셀 시트에서 일반 함수를 사용하는것처럼 값을 입력하면 색깔 별 합계를 구할 수 있습니다.

함수의 첫 번째 인수는 합할 색깔이 있는 셀을 선택하고, 두 번째 인수는 합계 범위를 지정하면 됩니다.

파란색 합계를 구하고 싶은 경우 A1를 C1으로 바꾸면 되겠죠? 

함수를 실행하면 위와 같이 값을 구할 수 있습니다.

[엑셀 VBA #14] 문자 나누고 합치기 (split, join)

이번 포스팅에서는 문자를 나누는 split문과 문자를 합치는 join문에 대해 알아보겠습니다.

예제를 받고 따라와주세요.

SPLIT

split문은 일반적으로 텍스트 파일을 엑셀로 불러올 때 많이 쓰이는 문법입니다.

엑셀 VBA 실전예제 카테고리 중 "텍스트 파일 엑셀에 입력하기" 포스팅에서 다뤘었습니다.

예를 들어, 텍스트 문서의 내용이 아래와 같습니다.

이 경우 위 텍스트 내용을 엑셀에 붙혀넣으면, 아래와 같이 출력됩니다.

하지만 각 셀에 이름이 하나씩 들어가게 하려면, 이름 문자열을 분리해야 합니다.

이럴 때 사용하는 문법이 split 문입니다.

하지만 split 문을 사용할 때에는 한 가지 조건이 붙습니다.

아래와 같이 출력됩니다. (A4셀 선택 후 실행)

JOIN

JOIN문은 배열 형식으로 저장된 값을 하나로 뭉쳐서 출력해줍니다.

SPLIT과 반대되는 개념고, 엑셀의 concatenate 함수와 비슷합니다.

위의 코드는 SPLIT 예제에서 출력되는 부분을 빼고, 주황색 부분만 더한 코드입니다.

JOIN문을 사용할때도 구분자의 설정이 필요합니다.

SPLIT과 반대로 값 사이사이에 구분자를 넣어서 출력해줍니다.

아래와 같이 출력됩니다.

마찬가지로 구분자를 바꾸면 다른 형식으로 값을 출력할 수 있습니다.

[엑셀 VBA #13] (배열#2) 배열의 크기 확인 Ubound, Lbound

이번 시간에는 엑셀 VBA에서 배열의 크기를 구하기 위해 주로 사용되는 Ubound문과 Lbound문에 대해 알아보겠습니다.

예제를 받고 시작하겠습니다.

Ubound는 배열의 마지막 위치값을, Lbound는 배열의 첫 위치값을 반영합니다.

즉, 

위와 같이 배열을 선언했을 때,

Ubound(varArray) 값은 20, Lbound(varArray) 값은 1을 반영한다는 것입니다.

"굳이 셀 필요가 있나, 1부터 시작하니까 배열의 크기는 당연히 20이겠지" 라고 생각할 수도 있습니다.

일반적으로 배열을 선언할 때, 시작을 1부터 하는것이 대부분이지만, 엑셀 VBA 기본값은 0부터 시작합니다.

그리고 굳이 1이 아니어도 모든 정수를 배열의 시작 위치값으로 설정할 수 있습니다.

위와 같이 배열을 선언했을 때,

Ubound(varArray) 값은 20, Lbound(varArray) 값은 -10이 됩니다.

실질적으로 배열의 크기는 20이 아닌 31이 되는거죠(-10부터 20까지 0을 포함한 모든 정수).

따라서 정확하게 배열의 크기를 구하려면 아래와 같이 코드를 짜야합니다.

위 식의 값이 varArray 배열의 실제 크기가 되는 것이지요.

이제 예제를 살펴보겠습니다.

이번 예제는 첫 번째 행에 입력된 데이터를 배열에 넣고, 배열의 크기를 구하는 예제입니다.

[엑셀 VBA #12] (배열응용) 특정 셀 영역 그대로 복사/ 

이동하기

이번 엑셀 VBA 강의에서는 특정 셀을 기준으로 원하는 영역을 그대로 복사 및 이동하는 법에 대해서 배워보겠습니다.

예제를 받고 따라와 주세요.

이번 예제에는 두 가지 내용이 들어가 있습니다.

먼저 2차원 배열으로 구성된 셀 영역을 복사/이동하는 엑셀 VBA 코드를 알아보겠습니다.

좌측 상단 모서리에 있는 셀을 기준으로 값이 있는 모든 영역을 파란색 셀을 기준으로 이동하는 코드입니다.

예제 상에는 4x3의 배열으로 정해져 있지만, 사용자가 임의로 배열의 크기를 조정해도 문제 없도록 동적배열을 선언합니다.

위 코드를 실행시키면 아래와 같이 출력됩니다.

배열의 크기를 바꾸면 어떻게 출력될까요?

변화한 배열의 크기에 맞춰서 배열이 재선언되고, 크기에 맞춰서 정상적으로 복사됩니다.

이제 방금 배운 배열 응용법으로 간단한 회원관리 시스템을 만들어 보겠습니다.

위와 같은 회원관리 시스템에서 회원이 탈퇴했을 경우, 회원의 이름을 선택하고 매크로를 실행하면 아래 과거회원 시트로 해당된 회원 정보를 이동시키는 엑셀 VBA 매크로입니다.

※만약 Resize 부분과 End(3)(2) 부분이 이해가 되지 않는다면 제가 이전에 올린 Resize와 End속성의 강의를 찾아보세요!

눈치가 빠르신 분은 눈치채셨을 수도 있습니다.

사실 위의 두 예제는 배열 선언이 필요 없습니다!

회원관리시스템 예제의 보라색 부분을 배열을 사용하지 않고 아래와 같이 바꿔도 똑같은 결과가 출력됩니다.

그렇다면 굳이 배열을 사용하는 이유는 뭘까요?

먼저, 출력되는 값은 같지만, 두 가지 코드는 분명히 다릅니다.

첫 번째 코드는 배열을 선언 후, 복사해야 할 값을 배열(메모리)에 저장한 후 뿌려주었습니다.

두 번째 코드는 단순히 같은 값을 다른 셀에 똑같이 넣었습니다.

배열을 선언해서 메모리에 저장하게 되면, 셀의 값은 이제 배열의 값으로 복제가 되고, 복사하려는 셀이 사라지거나 수정이 되어도 그 값은 그대로 남아있게 됩니다.

반면에, 두 번째 코드처럼 메모리에 값을 저장하는 과정을 건너뛰게 되면 복사하고자 하는 셀의 값이 사라지거나 수정되는 경우 출력에 오류가 생기게 됩니다.

간단하게 예를 들어보겠습니다.

회원관리 예제의 초록색으로 표시된 부분을 보라색 코드 사이에 넣어보겠습니다.

과거 학생 시트로 값이 입력되기 전에 복사하고자 하는 값들을 엑셀 시트에서 삭제했음에도 불구하고, 출력되는 결과는 이전과 동일합니다. 이미 메모리에 배열이 저장되었기 때문에, 엑셀 시트상의 값이 사라진다고 해도 메모리에 저장된 값에는 변동이 없기 때문입니다.

또한 메모리를 사용하는 작업이기 때문에 대량 작업의 경우 작업의 속도가 현저하게 빨라집니다.

따라서 배열을 선언하는 습관을 갖고 있는 것은 정말 중요합니다.

[엑셀 VBA #11] (배열#1) 배열 이해하기. 배열의 차원과 

정적, 동적배열

이번 시간부터는 엑셀 VBA를 배울 때 가장 까다로운 개념 중 하나인 배열에 대해 알아보겠습니다.

한 포스팅에 담기에는 벅찰 것 같아서 나눠서 진행하도록 하겠습니다.

배열은 확실히 까다로운 개념이 맞습니다.

하지만 정말 중요한 개념이고 많이 쓰이기 때문에 꼭 이해해야 하는 개념이기도 합니다.

엑셀 VBA의 배열은 수학적 개념의 배열과 동일합니다.

변수 i에 한 가지의 수, 문자를 저장할 수 있다면, 배열 i에는 한꺼번에 여러 개의 수와 문자를 저장할 수 있습니다.

하지만 안타깝게도 배열이 이렇게 단순하지만은 않습니다...

엑셀 VBA는 배열을 1차원부터 60차원까지 허용하고 있습니다.

이는 무엇을 의미하고 있는 것일까요?

N차 배열의 의미

1. 1차 배열

1차 배열은 직선형의 단순한 배열을 의미합니다.

배열 속에 있는 각각 개체들이 앞, 뒤 순서로만 배치되어 있습니다.

2. 2차 배열

2차 배열은 직사각형(정사각형) 의 배열을 의미합니다.

배열 속의 개체들이 상하좌우로 나뉘어서 배치되어 있습니다.

3. 3차 배열

3차 배열은 직육면체(정육면체) 의 배열을 의미합니다.

배열 속의 개체들이 상하좌우 및 위 아래로 배치되어 있습니다.

4. 4차 이상 배열

사실 4차 이상 배열은 잘 사용되지 않습니다. 엑셀의 범위를 넘어가기 때문이지요..

4차 배열의 경우 여러 엑셀 파일을 담고있는 폴더, 5차 배열의 경우 여러 폴더를 담고있는 하드드라이브, 6차 배열의 경우 여러 하드드라이브를 담고있는 컴퓨터, 7차 배열의 경우 여러 컴퓨터들을 담고있는 PC방에 비유할 수 있겠네요.

계속 차원을 넓혀가면 우주까지 다다를 수 있습니다. 

억지스런 비유이긴 하지만, 엑셀 VBA에 이런 고차원적인 요소는 필요 없기에,일반적으로 3차 배열까지만 사용되고 있습니다.

물론 4, 5, 6차 배열도 분명 사용할 수 있고 사용되고 있습니다. 하지만 일반적인 엑셀에는 그 만큼 넓은 배열이 필요하지 않습니다. 

자, 이해가 되시나요? 아직 좀 아리송할겁니다.

더 간단하게 비유해보겠습니다.

배열을 엑셀에 비유했을 때, 엑셀이라는 것이 점점 진화를 한다고 생각해 보겠습니다.

엑셀의 가장 작은 기본 단위는 "셀"입니다.

그렇다면 태초의 엑셀은 아래와 같다고 가정할 수 있습니다.

이 때의 엑셀에는 배열의 개념이 필요 없습니다.

셀이 하나밖에 없기 때문이죠.

하지만 엑셀이 한단계 진화한다면 말은 달라집니다.

엑셀이 한 단계 진화해서 한 개의 행을 이루었습니다.

이 때부터 배열의 개념이 필요하게 됩니다.

배열(0), 배열(1), 배열(2), ... , 배열(n)으로 구성된 1차 배열이 등장하게 됩니다.

엑셀이 한 단계 더 진화해서 시트를 구성했습니다.

배열(0, 0) 배열(0, 1) 배열(0, 2)..... 배열(n, m)으로 구성된 2차 배열이 등장하게 됩니다.

엑셀이 한 단계 더 발전해서 엑셀 파일을 구성했습니다.

배열(0, 0, sheet1) 배열(0, 0, sheet2) ..... 배열(n, m, sheetx)로 구성된 3차 배열이 등장하게 됩니다.

정적배열과 동적배열

정적배열과 동적배열의 개념은 간단합니다.

정적배열은 이미 배열의 용량을 정해준 배열이고, 동적배열은 배열의 용량을 정해주지 않은 배열입니다.

동적배열은 배열이 차지하는 크기가 확실하지 않을 때 사용합니다.

굳이 동적배열을 선언하지 않고 정적배열로 배열을 최대한 크게 만들어도 이론상으로는 상관 없습니다.

하지만 배열은 상당히 많은 메모리를 차지합니다.

배열을 불필요하게 크게 선언한다면 메모리 부족 현상이 나타나기 쉽습니다.

따라서 배열을 동적으로 선언하여 불필요한 메모리 차지를 방지해야 합니다.

정적배열의 선언

1차 정적배열

2차 정적배열

동적배열의 선언

1차 동적배열

2차 동적배열

이번 포스팅에선 배열의 기본 개념과 배열의 차원, 그리고 정적, 동적 배열에 대해 알아봤습니다.

다음 포스팅은 배열에 대한 좀 더 심화된 내용으로 찾아오겠습니다.

[엑셀 VBA #10] Find~Findnext 문 활용해서 검색하기

이번 주제는 바로 실무에 활용할 수 있는 Find~Findnext 문입니다.

Find~Findnext 문은 엑셀 VBA에서 활용도가 정말 높은 문법입니다.

Find문은 컨트롤 F 키를 눌러서 검색하는 것과 비슷한 기능을 할 수 있습니다.

검색된 부분을 다른 셀에 집어넣거나, 검색된 부분만 따로 떼어내는 등 여러 방면으로 응용할 수 있습니다.

예제를 받고 따라와주세요.

숨겨진 시트를 숨김 해제하면 이전 내용들도 수록되어 있습니다.

이번 예제는 버튼을 누르면 'E1'셀에 있는 내용을 좌측 데이터베이스 '이름' 항목에서 검색 후, 우측 칸에 입력하는 예제입니다.

위 예제는 이름 전체가 일치해야 검색되는 예제이지만, 상황에 따라 부분검색으로 변환할 수도 있습니다.

정말 간단합니다.

모두 이전 엑셀 VBA 강의에서 배웠던 내용들입니다.

값을 복사하는 것 뿐만 아니라, 검색된 부분 삭제, 다른 시트로 이동 등등 여러 방면으로 응용이 가능합니다.

엑셀으로 데이터베이스의 기본적인 원리를 구현하는 것이라고 생각하면 됩니다.

사전 프로그램의 원리도 이와 비슷합니다.

[엑셀 VBA #9] 값이 있는 마지막 셀을 선택, End 속성

엑셀에서는 컨트롤 키를 사용해서 값이 있는 셀의 끝으로 이동할 수 있습니다. (컨트롤 + 방향키)

엑셀 VBA에서도 물론 가능합니다.

End 속성을 사용하면 됩니다.

자, 여기서 의문이 생기죠.

분명히 마지막 셀으로 이동한다고 했는데, 실제로 사용해보니 그렇지 않습니다.

값이 있는 셀을 선택했을 경우 공백을 만나면 멈춰버립니다.

End 속성은 선택된 셀로부터 일정 방향으로 한 칸씩 이동하면서 마지막 값을 만나면 멈춥니다.

만약 공백이 듬성듬성 있는 데이터베이스에서 진짜로 마지막에 위치한 값을 찾으려면 어떻게 해야 할까요?

답은 간단합니다. 반대로 하면 됩니다.

빈 칸을 선택 후 End 속성을 사용하면, 첫 번째 만나는 값이 있는 셀에서 멈춰버립니다.

컨트롤 + 방향키로 선택하는것과 100% 같습니다.

Rows.Count, Columns.Count 속성

Row.Count는 엑셀이 허용하는 행 개수의 최대치를 나타냅니다.

Columns.Count는 엑셀이 허용한는 열 개수의 최대치를 나타냅니다.

엑셀 2013 기준으로 최대 행 개수는 1048576개, 열 개수는 16384개입니다.

따라서 맨 끝 행, 혹은 맨 끝 열부터 셀을 한 칸씩 이동했을 때, 만나는 첫 번째 셀이 값을 지닌 마지막 셀이 되는 것입니다.

맨 아래 셀에서 한 칸씩 올라오면서(End(3)) 만나는 첫 셀을 선택

맨 오른쪽 셀에서 한 칸씩 좌측으로 이동하면서(End(1)) 만나는 첫 셀을 선택

응용하기

그렇다면 맨 마지막 셀도 아니고, 마지막 셀의 그 다음 셀을 선택하고자 한다면 어떻게 해야할까요?

저번 시간에 배운 Offset 속성을 사용하면 됩니다.

[내 컴퓨터 진단하기] 컴퓨터가 느려졌을 때

컴퓨터가 느려지는 이유는 정말 다양합니다.

내/외부적인 요인을 막론하고 컴퓨터 초보자들에게는 해결하기 힘든 문제이기도 하죠.

이번 포스팅에서는 컴퓨터가 느려졌을 때 초보자들도 쉽게 진단하는 방법에 대해 알려드리겠습니다.

컴퓨터 진단하기

1. 외부적인 요인 - 온도

컴퓨터가 갑자기 급격히 느려지고, 다운이 된다면, 이는 외부적인 원인일 가능성이 큽니다.

일반적으로 컴퓨터는 온도가 올라가게 되면 자체적으로 성능을 낮춰서 온도를 떨어뜨리는데요,

이를 스로틀링(Throttling)이라고 합니다.

스로틀링에 걸리게 되면, CPU는 클럭을 최대한 낮추게 되고, 사용자가 체감하는 컴퓨터 속도는 현저하게 떨어지게 됩니다.

이를 진단하는 방법은 아주 간단합니다.

직접 눈으로 봤을 때, 온도가 높아질 이유가 없다고 보여진다면, 컴퓨터 온도를 측정하는 프로그램을 받아봅니다.

CPUID HWmoniter를 검색하면 쉽게 찾을 수 있습니다.(무료입니다)

일반적으로 아이들(idle, 별다른 프로그램을 실행하지 않는 상황) 상태에서 컴퓨터의 온도는 50℃를 넘지 않습니다.

고사양 그래픽카드의 경우 50℃ 좌우를 왔다갔다 하는 경우도 있지만, 문제되진 않습니다.

온도 체크 프로그램으로 확인했을 때, CPU를 포함한 모든 부품의 온도(보통 CPU와 VGA만 높은 온도를 유지합니다)가 90℃를 상회한다면, 문제의 원인은 높은 온도라고 진단할 수 있습니다.

하지만 팬에는 문제가 없다면, 가장 흔한 원인은 CPU와 쿨러 사이에 도포하는 써멀그리스가 제대로 도포되지 않았을 가능성이 큽니다.

보통 CPU를 교체한지 얼마 안된 컴퓨터에서 이런 증상이 발생하게 되는데, 이전에 발랐던 써멀그리스 자국이 남아있다고 하더라도, 쿨러를 탈착후 장착할 때마다 재도포를 해줘야 합니다.

외부적인 요인(온도)으로 인해 컴퓨터가 느려졌을 경우, 먼지를 제거하고, 써멀그리스를 재도포하면 90%는 해결된다고 볼 수 있습니다. 부품 불량인 경우도 간혹 있긴 하지만 흔하진 않습니다.

2. 내부적인 요인

컴퓨터가 느려지는 대표적 내부 요인은 바이러스입니다.

컴퓨터 보안이 많이 강화되서 예전처럼 바이러스가 흔하진 않지만, 유사 바이러스인 스파이웨어, 애드웨어, 그리고 소프트웨어를 가장한 저질 프로그램들이 창궐하는 판국입니다.

(이번 포스팅에서 제가 말하는 바이러스는 스파이웨어, 애드웨어, 그리고 저질 프로그램을 모두 포함합니다.)

이러한 바이러스의 종류는 매우 다양하고 해결법도 각각 다릅니다.

여기서 모든 바이러스에 대한 해결법을 알려드릴 수는 없지만, 어떻게 대응에야 하는지 가이드라인을 제시해 보겠습니다.

1. 나도 모르는 사이에 열심히 일을 하고 있는 종자를 잡아내자.

별다른 작업을 하지 않는 상태에서 가만히 기다리면 위와 같은 그래프가 출력됩니다. 

위 사진과 같이  CPU 점유율이 낮고 변동폭이 작다면 큰 문제는 생기지 않습니다. 하지만 아래의 경우는 어떨까요?

지금 보여드리는 스크린샷은, 1초 간격으로 인터넷에 리소스를 전송할 때의 그래프입니다.

별다른 작업을 안하는 상태에서, 위와 같이 변동폭이 크고 불규칙하다면 모종의 바이러스가 침투했다고 할 수 있습니다.

무언가가 나도 모르는 사이에 열심히 무언가를 하고 있다는 의미입니다.

그렇다면 무엇이 원인인지 어떻게 확인할 수 있을까요?

화면에 보이는 리소스 모니터를 열어보겠습니다.

바로 아래 리소스 모니터는, 정상적인 시스템의 리소스 모니터입니다.

아무 작업도 하지 않을 때, CPU 사용량이 20%안팎을 유지합니다.

CPU마다 수치의 차이는 존재합니다. 제 시스템 (i5-2500)을 기준으로 하위 시스템은 조금 더 높은 수치를, 상위 시스템은 조금 더 낮은 수치를 확인할 수 있습니다.

이제 바이러스에 감염된 시스템의 리소스 모니터를 확인해 보겠습니다.

위 스크린 샷은 엑셀 매크로로 간단한 바이러스를 만들어서 실행시킨 결과입니다.

엑셀은 일종의 예시일 뿐이지만, 뭔지 모르는 프로그램이 별다른 작업을 하지 않는 상황에서 지나치게 많은 CPU 사용량을 유지하고 있다면, 바이러스로 의심할 수 있습니다.

3. 마지막 보루 - 포멧

제가 제시한 위 두 가지 방법으로도 진단이 불가능하다면, 전문가에게 도움을 요청하거나, 

최후의 보루로 포멧이 남아있습니다.

일반적으로 포멧을 하게 되면 컴퓨터가 느려지는 소프트웨어적인 원인은 제거할 수 있습니다.

포멧 이후에도 똑같이 느리다면, 컴퓨터 부품 자체에 고장이 났을 가능성이 높습니다.

하지만 일반적으로 미세한 공정이 적용되는 컴퓨터 부품의 특성 상, 문제가 생기면 아예 작동을 안하지 작동은 하되 느려지거나 하는 경우는 흔하지 않습니다.

[엑셀 VBA 예제2] 여러 시트에 있는 값을 한 시트로 옮기기

이번 엑셀 VBA 예제는 여러 시트에 있는 값을 조건에 맞춰서 한 시트로 취합하는 예제입니다.

With문, For~Next문, Find문, If문, Offset문, Instr문이 사용됩니다.

예제를 받아주세요.

이번 엑셀 VBA 예제의 내용은 아래와 같습니다.

위 두 시트의 자산 총계를 아래 시트의 자산 란에 연도별로 넣고자 합니다.

2015, 2016 두 가지의 자료만 넣는 예제이지만, 시트를 추가하면 2000년부터 2099년까지 활용할 수 있는 예제입니다.

엑셀 VBA를 본격적으로 작성하기 전에 어떻게 만들것인지 구상이 필요합니다.

제 구상은 아래와 같습니다:

1.  시트1(2015년 재무재표) ~시트n(2099년 재무재표) 이름의 연도값(20xx)을 인식 및 변수로 추출

2.  년도별로 해당하는 시트에서 "자산총계" 문자가 있는 셀을 찾고, 바로 아래의 값을 변수로 추출

3.  "비교" 시트의 년도값(20xx)의 행값과 "자산"의 열값을 변수로 추출 (n, 2)

4.  (행값, 열값)셀에 각 시트의 자산총계 바로 아래에 있는 값을 넣는다

5.  마지막 시트까지 반복

이제 엑셀 VBA 코드를 보겠습니다.

COMP는 이름이 "비교"인 시트 값을 뜻합니다.

조건1. 시트의 이름에는 20을 포함하는 년도값이 한번 포함되야 합니다.

조건2. 데이타를 합하는 시트의 시트 값은 "COMP"로 설정되어 있어야 합니다.

조건3. COMP 시트를 제외한 모든 시트 값은 기본값인 Sheet1~Sheetn으로 되어있어야 합니다.

조건4. 코드는 COMP 시트에서 실행하면 안됩니다.

파란색으로 표시한 Instr 문은 string(문자열) 내에서 해당 문자가 시작되는 위치를 찾아줍니다.

따라서 문자가 존재하지 않는 경우 0의 값을 돌려주게 됩니다.

이를 응용해서 년도값을 추출할 때에는 mid문을 사용해서 instr과 조합해서 사용할 수 있습니다.

stryear = Mid(wks.Name, InStr(wks.Name, "20"), 4)

위 코드는 시트 이름에서 20이라는 문자의 위치를 찾고, 그 위치부터 오른쪽으로 4 자리의 숫자를 추출하는 코드입니다.

2020년의 경우에도 알맞은 값을 돌려줍니다. (처음 만나는 20을 찾기 때문)

[엑셀 VBA #8] Resize, Offset 문을 이용한 셀 범위 설정

엑셀 VBA를 사용하다 보면 특정 셀 하나가 아닌 범위를 참조해야 하는 경우가 많습니다.

이러한 경우에 쓰이는 문법이 Resize문과 Offset 문입니다.

Resize

Resize문은 셀을 잡고 드래그하는 것과 같은 효과를 줍니다.

위 사진과 같은 엑셀에 위 엑셀 VBA를 실행시키면 아래와 같이 선택됩니다.

Resize 괄호 안의 첫 번째 값이 커지면 아래로, 두 번째 값이 커지면 우측으로 드래그가 됩니다.

그렇다면 음수 값(마이너스 값)을 넣는다면 어떻게 될까요?

오류가 나게 됩니다.

가끔은 선택된 셀 좌측과 상단으로 범위를 설정해야 하는 경우도 생기게 되는데, 이 경우에 필요한 것이 Offset 문입니다.

Offset

※수정사항 : Offset문을 사용할 때에는 괄호 안 우측 값을 공백으로 두면 안됩니다.

   제가 공개한 스크린샷에는 우측 값이 공백으로 되있는데, 공백의 경우 0 값을 줘야 합니다.

Offset문은 선택된 셀을 기준으로 설정한 만큼 떨어진 곳에 위치한 셀을 선택합니다.

위와 같은 코드를 실행시키면

위와 같이 선택됩니다.

Resize문과 달리 음수 선택이 가능합니다.

그렇다면 선택한 셀의 좌측 혹은 상단으로 블록을 잡으려면 어떻게 해야 할까요?

Offset 문에서 사용한 엑셀을 재활용하겠습니다.

위 코드를 실행시키면...

이렇게 됩니다.

먼저 Offset으로 좌측의 셀을 지정한 후, Resize로 우측 한칸을 같이 블록지정 하게 됩니다.

Resize문과 Offset문은 엑셀 VBA에서 정말 많이 사용됩니다.

추후 엑셀 VBA 강의에서 계속~ 사용될 예정이니 꼭 확실히 이해하고 넘어가 주세요.