God of EXCEL

소량의 데이터라면 엑셀로 처리해도 큰 문제는 없었으나 요즘 대량데이터를 가지고 놀다보니 엑셀에 한계가 있음을 느꼈다. 그래서 눈을 돌려봤더니 데이터베이스가 있었다. 그 중 엑셀과 가장 가까운 DB ACCESS를 이용해 대량 데이터 처리를 시작해보고자 한다. 액세스? 엑세스? ACCESS ! 엑셀과 액세스 준비 조회(SELECT) 삽입(INSERT INTO) 변경(UPDATE) 삭제(DELETE) 1. 엑셀과 액세스준비 우선 연습용 액세스 DB를 만들어준다. 필자는 C드라이브에 DB 폴더를 만들고 그 안에 DB 파일을 생성하겠다. 만들기 -> 테이블을 클릭하면 엑셀 시트같은 화면이 나타난다. 여기에 '추가하려면 클릭'을 눌러서 이름이나 나이 등 아무거나 데이터를 집어 넣도록 하자. 여기서 ID, 이름, 나..

단 코드 몇 줄이면 시트에 달력을 표시할 수 있다. 아주 쉽게 만들어보자. 우선, 달력을 표시할 셀을 이쁘게 꾸며보자. 실행 과정 코드(윤년 계산법) 1. 실행 과정 나의 경우 셀 C1에는 '년도', C2에는 '월' 입력 하기 위한 양식을 만들었다. 그리고 데이터 유효성 검사 기능으로 각 셀에는 입력값을 제한 하고 있다. - C1에는 1900 ~ 4000 까지 입력 가능 - C2에는 1 ~ 12 입력가능 정해진 범위가 아닌 값을 입력하면 오류가 출력되며 코드가 실행되지 않는다. 정확한 값을 넣으면 다음과 같이 정상적으로 작동하는 것을 볼 수 있다. 2021년 4월 달력을 출력해봤다. 실제 달력과 비교해본 결과 똑같았다. 년도나 월 값을 변경하면 달력도 자동으로 변경된다. 다음 그림을 보자. 윤년도 정확히..

엑셀vba에는 모듈 말고도 사용자 정의 폼을 만들어 쓸 수 있다. (word, ppt, access 등 다른 오피스 프로그램들도 마찬가지) 간단히 폼에 추가할 수 있는 컨트롤과 사용법에 대해 알아보자. 1. 폼 생성 Alt + F11을 눌러 VB 편집기를 열도록 하자. 그리고 프로젝트 창에서 마우스 우클릭하여 사용자 정의 폼을 추가한다. 만약 프로젝트 창이 안보인다면 Ctrl + R 을 누르거나 '메뉴 > 보기 > 프로젝트 탐새끼'를 누르자. 이렇게 아무것도 없는 폼이 생성되었다. 2. 폼 열기 폼을 만들었으면 열고 닫는법을 알아야 한다. 이제는 모듈을 하나 추가해서 코드 한 두줄만 작성하면 폼을 열 수 있다. 프로시저 이름을 정하고 폼 이름을 적고 온점을 누르면 다양한 속성들이 나타난다. 그 중에서 s..

간단한 방법으로 이 데이터가 소수인지 정수인지 구분해보도록 하자. 구글링을 잘못했는지 엑셀vba 소수를 검색하면 1,2,3,5,7,9,... 과 같은 1과 자기 자신 외에는 약수를 가지지 않는 수에 대해서만 나와있다. 내가 원한건 이게 아닌데... 엑셀vba에서 0.24, 0,123, 23.4234 이런 소수들을 구분하는 방법이 있는지 궁금해서 찾아봤지만, 해답은 찾을 수 없었다. 하지만 수학적 접근으로 비교적 쉽게 해결하였다. 소수 구분 식: x - 정수형 변환(x) = 소수점 아래 숫자 예시. x 값이 123.24 라고 할 때 정수부분인 123을 x값에서 빼게 되면 0.24만 남게된다. 이 결과를 보면 나머지가 존재하기 때문에 소수라고 얘기 할 수 있다. x = 4 일때 식: 4 - 정수형 변환(4)..

가끔 하나의 함수가 길어지는 경우가 발생한다. 너무 길어지면 가로 스크롤이 생기고 가독성이 떨어지게 된다. _(언더바)를 이용해서 코드를 여러 줄로 작성해보자. 1. 테스트 1 2 3 4 5 6 Sub 연습() Debug.Print ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row '1번 Debug.Print ActiveSheet.Cells(Rows.Count, "A"). _ End(xlUp).Row '2번 End Sub Colored by Color Scripter cs A열의 마지막 데이터를 찾아주는 함수로 테스트하였다. 1번의 경우 한 줄로 작성했고, 2번은 두 줄로 만들어봤다. 실행결과야 둘 다 똑같은 1의 값을 출력했다. 함수를 두 줄로 작성하려면 _(언더바..

숨겨진 엑셀 시트가 한 두개면 그냥 내가 숨기기 취소해서 보이면 간단하다. 하지만 그 수가 10개가 넘어간다면 일일히 숨기기 취소하기엔 힘들지 않을까? 그래서 한방에 숨겨져 있는 모든 시트를 세상에 드러나도록 해봤다. 1. 코드 Option Explicit Sub 숨기기일괄취소() Application.ScreenUpdating = False Dim i As Integer For i = 1 To ActiveWorkbook.Sheets.Count If Sheets(i).Visible = False Then: Sheets(i).Visible = True Next i End Sub 엑셀에서 Alt+ F11키를 눌러 VBA 편집기를 열고 프로젝트 창에서 모듈하나를 추가해주고 코드를 복붙하면 끄읕. 크게 어려운 ..

엑셀vba 편집기를 이용하다보면 도킹 기능이 상당히 불편하게 느껴질 때가 있다. 처음엔 그러려니하며 썼지만 이젠 못참겠다. 그래서 도킹을 없애봤다. 1. 도킹 기능이란? 엑셀vba 편집기에는 프로젝트, 속성, 조사식, 직접 실행 창, 코드, 지역 창 등 상당히 여러가지 보기 옵션을 선택 할 수 있다. 이 옵션 창들을 vba편집기 테두리 부분에 끌어서 옮기다 보면 갑자기 툭하고 붙는 경우가 있다. 이 기능은 vba에서 지원해주는건데 그렇게 필요한거 같진 않다.. (나의 개인적인 생각) 2. 도킹 해제하기 vba편집기의 '도구 > 옵션 > 도킹 탭'에서 쉽게 해제 할 수 있다. 체크 해제 후 확인을 눌러주면 vba 편집기에 들러붙지 않게 된다.

시트의 마지막 데이터(행 또는 열)가 몇번째 셀에 있는지 알고싶다. 쉽게 알아내보자. 1. 테스트 환경 구축 위 그림에서 셀 (10,"A"), (7,"C"), (4,"E"), (2,"H") 네 곳에 '마지막'이라는 글자를 입력하였다. 이 글자들을 활용해 위치를 찾아내보자. 2. vba 코드 123456789Sub 마지막_위치_알아내기() Dim LastRow As Integer Dim LastCol As Integer LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row LastCol = Sheets(1).Cells(2, Columns.Count).End(xlToLeft).Column End SubColored by Color Scriptercs 육안으로 ..

엑셀 매크로와 vba를 처음 접했을 때 부딪히는 문제였다. 매크로 기록하고 '나중에 또 써먹어야지 히히' 라는 생각하며 파일 저장을 했다. 분명 나는 파일 저장을 했다. 근데 다음에 파일을 열어보면 ??? 매크로 어디감??? ㅜㅜㅜ 매크로 기록하고 저장할 때 경고창이 나타남에도 잘 읽어보지 않고 닫거나 '저장 안 함'을 눌러버려서 매크로 코드가 많이 실종됬었다.. xlsm 이나 xls 확장자로 저장하면 이런 불상사가 생기지 않을 것이다. 요약. .xlsx 기본 확장자에는 vba코드가 포함되지 않는다. .xlsm vba코드를 포함한 파일 .xls 97 - 2003 통합 문서 파일인데 vba코드가 포함됨 .xlam 엑셀 추가기능 파일, vba 코드 포함

이제 막 사용자 정의 폼을 시작했을 때 '왜 색깔이 이거 밖에 없어? 겁나 꾸졌네'라고 생각했다. 하지만 다양한 컬러들을 설정 할 수 있음을 찾아내고 말았다.. 알고보니 넘나 쉬운것. 1. 폼 색상 조절 Color 글자가 들어가는 아무 속성이나 클릭하면 위 그림과 같은 색상 선택 창이 나타난다. 색상표, 시스템 두가지 탭이 존재하고 색상표에서 다양한 색들을 사용 할 수 있다. 알록달록한 예쁜색들이 보인다. 원래 맨 아래 두 줄은 흰색으로 되있었는데 내가 이것저것 색을 넣어놨다. 기본으로 제공되는 색을 변경할 수 없다. (첫번째 줄 ~ 여섯번째 줄) 맨아래 두 줄에서 아무 칸이나 마우스 오른쪽 클릭을 하면 색 정의 창이 나타난다. 여기서 RGB 값을 직접 조정해서 색을 추가해도 되고 마우스 클릭으로 해도되..

텍스트 파일 쓰기와 같은 방법으로 배치(batch) 파일을 만들어보자 배치 파일은 cmd 명령어를 이용한 프로그래밍이라 할 수 있다. 간단히 ping 보내는 파일을 만들어보겠다. 1. 파일 생성 코드 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Option Explicit Sub Create_Batch_File() Dim FILE_Path As String FILE_Path = "C:\Users\S-PC-01\Desktop\test\ping.bat" Open FILE_Path For Output As #1 Print #1, "ping -t 8.8.8.8" Close #1 End Sub Colored by Color Scripter cs 1) FILE_Path 변수에 파일확장자를 포함한 경로..

인터넷 익스플로러 컨트롤 할 때 클래스네임을 이용하는 경우 getElementsByClassName을 사용하게 된다. 그런데 형식이 일치하지 않는다며 런타임 오류가 발생하는 경우가 있다.. ㅜㅜ 네이버 로그인 화면으로 접속하고 html을 불러온다음 로그인 버튼의 클래스네임을 찾아 Object에 저장하는 코드이다. set object 부분에서 런타임 13오류가 발생했다. 그 이유는 마지막에 .item이 빠졌기 때문이다. 내가 이걸 어떻게 알게됬는지는 나도 모르겠다... 검색하다 알게된건가..

엑셀vba에서는 변수 선언을 하지 않아도 바로바로 쓸 수 있어서 편리했다. 하지만 코드가 길어지고 많은 변수를 사용 할 때 조금 헷갈리는 경우 발생하기도 한다. 그래서 언제부터인지 Option Explicit를 선언하여 변수 선언을 강제하고 있다. 변수선언을 하지 않고도 위 그림처럼 사용해도 아무런 문제는 없다. 짧은 코드에서는 이게 더 편함 위 코드를 실행하면 A=3, B = 7, C = 10이라는 값이 들어가게된다. 아무런 문제가 없지만 장기적으로 계속 프로그래밍을 하려면 변수 선언을 강제화하는게 좋은 습관일거라 판단하였고 편리한 부분도 있어서 Option Explicit 선언을 사용하고 있다. Option Explicit 선언을 하면 사용하는 모든 변수에 대해 선언해야만 한다. 각기 다른 데이터 타..

코린이는 궁금증이 생겼다. private sub, sub 의 차이가 뭘까? 영어 뜻만본다면 대충 어떤건지 감이 오긴하지만 직접 알고 싶어서 테스트를 해봤다. 1 2 3 4 5 Sub 더하기_함수(ByVal A As Integer) Debug.Print A + A End Sub cs 모듈1 1 2 3 4 5 6 Option Explicit Sub 결과() 더하기_함수 (A) End Sub

vba 사용중 코드의 결과가 어떻게 나오는지 궁금할 때 사용하면 유용하다. 암산으로도 가능한 코드를 작성하고 실행해본다. 결과는 어디서 확인???? 직적 실행 창에서 할 수 있다. 'ctrl + g'를 누르거나 '메뉴>편집>직접 실행 창'으로 열 수 있다. 확인 해보면 4라는 숫자가 적혀있음을 볼 수 있다. 정말 쉽군 그리고 직접 실행창은 아무거나 입력할 수 있다. 굳이 쓸일은 별로 없겠지만 ...

이미 열려있는 익스플로러를 컨트롤하고 싶다. 그래서 구글링을 통해 배워봤다. 1. 준비 VB편집기에서 Microsoft Internet Controls를 참조 추가하면 준비 끝 2. 코드 1234567891011121314151617 Dim IE As SHDocVw.ShellWindows Dim wanna_IE As SHDocVw.InternetExplorer Set IE = New SHDocVw.ShellWindows For Each wanna_IE In IE If wanna_IE.LocationName = "Naver" Then MsgBox "네이벼 열려있음", vbInformation, "확인" Exit Sub End If Next wanna_IE End SubColored by Color Scri..

시트를 숨기면 숨기는거지 완전히 숨기는건 또 뭐여 vba를 이용해 한 번 숨겨놓으면 숨기기 취소에서 보이지 않는다 ㅎㅎ 일반적으로 시트 숨기기 하면 시트 우클릭하여 숨기기 버튼만 누르면 되는데 문제는 '숨기기'해도 '숨기기 취소'에 떡 하니 나타나 있다는 것이다. 이럴거면 왜 숨기는겨... 그래서 만들어 놓은건진 몰겠지만 완전히 숨기기 취소 목록에 나타나지 않도록 만들 수 있다 어떤건지 살펴보자 123Sub Sheet_Hidden() Sheets(1).Visible = xlSheetVeryHiddenEnd SubColored by Color Scriptercs 시트 표시여부를 설정하는 visible 속성의 xlsheetveryHidden 옵션을 선택하면 완전히 숨길 수 있다. Visible 속성에는 3가..

xlsx 확장자는 모든 엑셀 버전에서 잘 열릴줄 알았다. 내 생각은 경기도 오산이었다... 엑셀 2016으로 저장한 TEST.xlsx 파일을 엑셀 2007이 깔린 컴퓨터에서 열었더니 엑셀은 켜졌지만 내용은 없은 회색 빈 창만 보였다.. 말그대로 엑셀만 켜진 상태 이 때만해도 왜 안열리는지 알 수 없는 상태였다. 구글에 엑셀 빈화면, 엑셀 회색 창 이런거 검색해도 나에겐 도움이 되지 않았다. .ㅜㅜ 혹시나 엑셀 버전이 달라서 그런건 아닌가 생각해서 엑셀 2016이 설치된 컴퓨터에서 저장할 때 엑셀 97 - 2003 통합문서 (.xls)로 저장하고 이 파일을 엑셀 2007이 설치된 컴퓨터에서 열었더니 아주 잘 되었다. 원인은 버전 문제였다. 문제 해결. 요약. 문제. 엑셀 2016으로 저장한 xlsx 파일이..

엑셀 vba를 이용해 셀에 함수 수식을 넣어보자. 1. 예시 B3 ~ E3의 합을 A3 셀에 입력하는 vba 코드를 작성하고 실행하면 10이 입력된다. 하지만 C3 값이 변경된다면 A3도 변할까??? ㄴㄴ 변하지 않는다. A3에는 값이 들어갔기때문... 그래서 값대신 엑셀 함수 =SUM(range)를 입력해보자. 2. 사용법 Sub SUM() Cells(3, "A") = "=SUM(B3:E3)" End Sub ;; 그냥 문자열 입력하듯이 하면 끝 넘 쉽군.. 근데 범위가 변경되거나 변수를 사용해야 된다면??? Sub SUM() Dim x As Integer Dim y As Integer x = 3 y = 3 Cells(3, "A") = "=SUM(B" & x & ":E" & y & ")" End Sub ..

엑셀vba에서 통합문서를 저장하는 방법에는 두 가지가 있다. ThisWorkbook.Save - 현재 문서에 저장 ThisWorkbook.SaveAs - 다른이름으로 저장 ThisWorkbook 자리에는 ActiveWorkbook, Workbooks("파일이름") 등 통합문서와 관련된 다양한 놈들이 들어 갈 수 있다. .Save는 그대로 사용하면 되는 반면에 .SaveAs에는 설정 할 수 있는 파라미터가 12개나 존재한다. 살펴보자 1. .SaveAs 메소드 원형 SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, Te..