VBA를 처음 사용하는 경우, 먼저 학습부터 시작해야 합니다. VBA 초보자 가이드하지만 만약 당신이 VBA 전문가이고 엑셀에서 VBA를 이용해 더 고급 기능을 구현하고 싶다면 계속 읽어보세요.
엑셀에서 VBA 마크업을 사용할 수 있게 되면서 자동화의 세계가 활짝 열렸습니다. 엑셀 계산, 버튼, 심지어 이메일 전송까지 자동화할 수 있습니다. VBA를 활용하면 생각보다 훨씬 더 많은 방식으로 일상 업무를 자동화할 수 있습니다.
Microsoft Excel용 고급 VBA 가이드
엑셀에서 VBA 코드를 작성하는 주된 목적은 스프레드시트에서 정보를 추출하고, 다양한 계산을 수행한 다음, 그 결과를 다시 스프레드시트에 저장하는 것입니다.
다음은 엑셀에서 VBA를 가장 일반적으로 사용하는 예입니다.
- 데이터 가져오기 및 계산 수행
- 사용자 버튼 클릭을 기반으로 결과 계산
- 계산 결과를 이메일로 누군가에게 보내는 것
이 세 가지 예제를 활용하면 다양한 고급 Excel VBA 코드를 직접 작성할 수 있을 것입니다.
데이터 가져오기 및 계산 수행
엑셀을 가장 많이 사용하는 용도 중 하나는 엑셀 외부의 데이터에 대한 계산을 수행하는 것입니다. VBA를 사용하지 않는다면, 데이터를 수동으로 가져와 계산을 실행하고 그 결과를 다른 시트나 보고서로 출력해야 합니다.
VBA를 사용하면 전체 프로세스를 자동화할 수 있습니다. 예를 들어, 매주 월요일에 새 CSV 파일이 컴퓨터의 특정 디렉터리에 다운로드되는 경우, 화요일 아침에 스프레드시트를 처음 열 때 실행되도록 VBA 코드를 구성할 수 있습니다.
다음 가져오기 코드가 실행되어 CSV 파일이 Excel 스프레드시트로 가져와집니다.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Excel VBA 편집기를 열고 Sheet1 개체를 선택합니다. 개체 및 메서드 드롭다운 목록 상자에서 다음을 선택합니다. 워크 시트 그리고 활성화이렇게 하면 스프레드시트를 열 때마다 코드가 실행됩니다.
이렇게 하면 함수가 생성됩니다. Sub Worksheet_Activate()위 코드를 이 함수 안에 붙여넣으세요.
이렇게 하면 활성 워크시트가 설정됩니다. Sheet1용지를 스캔하고 변수에 지정한 파일 경로를 사용하여 파일에 연결합니다. str파일그런 다음 루프가 시작됩니다. 와 파일의 각 줄을 차례로 살펴보고 A1 셀부터 데이터를 시트에 입력합니다.
이 코드를 실행하면 CSV 파일의 데이터가 빈 스프레드시트로 가져와지는 것을 확인할 수 있습니다. Sheet1.
가져오기는 첫 번째 단계일 뿐입니다. 다음으로, 계산 결과를 포함할 새 열 머리글을 만들어야 합니다. 이 예에서는 각 품목 판매에 부과되는 5% 세금을 계산한다고 가정해 보겠습니다.
코드에서 수행해야 할 작업 순서는 다음과 같습니다.
- 새로운 결과 열을 만드세요. 세금.
- 열을 따라 반복합니다. 판매 단위 그리고 판매세를 계산하세요.
- 계산 결과를 종이의 해당 행에 적으십시오.
다음 코드는 이러한 모든 단계를 수행합니다.
Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double Set StartCell = Range("A1") '마지막 행과 열 찾기 LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4)) rowCounter = 2 Cells(1, 5) = "세금" For Each cell In rng fltTax = cell.Value * 0.05 Cells(rowCounter, 5) = fltTax rowCounter = rowCounter + 1 Next cell
이 코드는 데이터 시트의 마지막 행을 찾은 다음, 데이터의 첫 번째 행과 마지막 행을 기준으로 셀 범위(판매 가격이 포함된 열)를 설정합니다. 그런 다음 코드는 이러한 각 셀을 반복하면서 세금을 계산하고 결과를 새 열(5번째 열)에 기록합니다.
위의 VBA 코드를 이전 코드 아래에 붙여넣고 스크립트를 실행하세요. 결과는 E열에 나타납니다.
이제 엑셀 워크시트를 열 때마다 프로그램이 CSV 파일에서 최신 데이터를 자동으로 가져옵니다. 그런 다음 계산을 수행하고 결과를 워크시트에 기록합니다. 더 이상 수동으로 작업할 필요가 없습니다!
버튼을 눌러 결과를 계산하세요.
워크시트를 열 때 자동으로 계산을 실행하는 대신, 계산 실행 시점을 직접 제어하고 싶다면 제어 버튼을 사용할 수 있습니다.
제어 버튼은 사용되는 계산 방식을 제어하려는 경우에 유용합니다. 예를 들어, 위에서 언급한 시나리오에서 한 지역에는 5%의 세율을 적용하고 다른 지역에는 7%의 세율을 적용하고 싶다면 어떻게 해야 할까요?
CSV 가져오기 코드는 자동으로 실행되도록 허용하되, 세금 계산 코드는 해당 버튼을 눌렀을 때 실행되도록 설정할 수 있습니다.
위에서 언급한 데이터 테이블을 사용하여 탭을 선택하세요. 개발자, 선택 끼워 넣다 그룹에서 Controls 리본 메뉴에서 컨트롤을 선택합니다. 누름 단추 드롭다운 메뉴에서 ActiveX를 선택하세요.
데이터가 입력될 부분과 떨어진 종이의 아무 곳에나 버튼을 그리세요.
버튼을 마우스 오른쪽 버튼으로 클릭하고 선택하세요. 등록속성 창에서 캡션을 사용자에게 표시할 내용으로 변경하세요. 이 경우 다음과 같을 수 있습니다. 5% 세금을 계산하세요.
버튼 자체에 이 텍스트가 표시됩니다. 창 닫기 속성같은 버튼을 두 번 클릭하세요. 그러면 코드 편집기 창이 열리고, 커서가 사용자가 버튼을 클릭했을 때 실행될 함수 안에 위치하게 됩니다.
위의 세금 계산 코드를 이 함수에 붙여넣되, 세율 승수는 0.05로 유지하세요. 활성 시트를 식별하기 위해 다음 두 줄을 포함하는 것을 잊지 마세요.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1")
이제 같은 과정을 반복하여 두 번째 버튼을 만드세요. 버튼에 레이블을 지정하세요. 7% 세금 계산.
이 버튼을 두 번 클릭하고 동일한 코드를 붙여넣되, 세금 배율을 0.07로 설정하세요.
이제 어떤 버튼을 누르느냐에 따라 세금 항목이 계산됩니다.
완료되면 시트에 두 개의 버튼이 모두 표시됩니다. 각 버튼을 클릭하면 서로 다른 세금 계산이 시작되고 결과 열에 다른 결과가 표시됩니다.
이를 테스트하려면 목록을 선택하세요. 개발자, 선택 디자인 모드 리본 메뉴의 컨트롤 그룹에서 비활성화할 항목 디자인 모드이렇게 하면 버튼이 활성화됩니다.
각 버튼을 선택하여 "세금" 결과 열이 어떻게 변하는지 확인해 보세요.
계정 결과를 누군가에게 이메일로 보내기
스프레드시트의 결과를 이메일로 다른 사람에게 보내고 싶다면 어떻게 해야 할까요?
다른 버튼을 만들 수 있습니다. 상사에게 이메일로 보내는 자료 위에서 설명한 것과 동일한 절차를 사용하여 이 버튼의 코드는 Excel CDO 개체를 활용하여 SMTP 이메일 설정을 구성하고 사용자 친화적인 형식으로 결과를 이메일로 전송하는 기능을 포함합니다.
이 기능을 활성화하려면 선택해야 합니다. 도구 및 참고 자료. 아래로 스크롤 Windows 2000용 Microsoft CDO 라이브러리활성화하고 선택하세요 OK.
이메일을 보내고 스프레드시트 결과를 첨부하려면 크게 세 가지 코드 부분을 작성해야 합니다.
첫 번째는 제목, 받는 사람 및 보내는 사람 주소, 이메일 본문을 저장하는 변수를 설정하는 것입니다.
Dim CDO_Mail As Object Dim CDO_Config As Object Dim SMTP_Config As Variant Dim strSubject As String Dim strFrom As String Dim strTo As String Dim strCc As String Dim strBcc As String Dim strBody As String Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double
ws = ActiveWorkbook.Sheets("Sheet1") strSubject = "이번 분기에 납부한 세금" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "다음은 이번 분기 매출에 대해 납부한 세금 내역입니다."
물론 논문의 결과에 따라 텍스트 내용이 동적으로 바뀌어야 하므로, 여기서는 범위를 순회하며 데이터를 추출하고 매번 텍스트에 한 줄씩 추가하는 루프를 넣어야 합니다.
StartCell = Range("A1").vbCrLf For Each cell In rng strBody = strBody & vbCrLf strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _ & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "." rowCounter = rowCounter + 1 Next cell
다음 섹션에서는 SMTP 서버를 통해 이메일을 보낼 수 있도록 SMTP를 설정하는 방법을 설명합니다. Gmail을 사용하는 경우 일반적으로 Gmail 이메일 주소, Gmail 비밀번호 및 Gmail SMTP 서버(smtp.gmail.com)를 사용합니다.
CDO_Mail 객체를 생성하고 오류 발생 시 Error_Handling으로 이동합니다. CDO_Config 객체를 생성하고 CDO_Config.Load를 실행합니다. SMTP_Config 필드에 다음 항목을 추가합니다. .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Set .Configuration = CDO_Config End With
바꾸다 이메일@웹사이트.com 비밀번호에는 계정 정보가 포함되어 있습니다.
마지막으로 이메일 전송을 시작하려면 다음 코드를 입력하십시오.
CDO_Mail.Subject = strSubject, CDO_Mail.From = strFrom, CDO_Mail.To = strTo, CDO_Mail.TextBody = strBody, CDO_Mail.CC = strCc, CDO_Mail.BCC = strBcc, CDO_Mail.Send 오류 처리: 오류 설명이 ""이 아니면 오류 메시지 상자 표시
주의이 코드를 실행할 때 전송 오류가 발생하는 경우, Google 계정에서 "보안 수준이 낮은 앱"의 실행을 차단하고 있을 가능성이 높습니다. [링크/참조]를 방문하세요. 보안 수준이 낮은 앱 설정 페이지 이 옵션을 켜세요.
이 기능을 활성화하면 이메일이 발송됩니다. 수신자가 자동으로 생성한 이메일을 보게 되는 모습은 다음과 같습니다.
보시다시피, Excel VBA를 사용하면 실제로 자동화할 수 있는 작업이 매우 많습니다. 이 글에서 배운 코드 조각들을 활용하여 자신만의 독창적인 VBA 자동화 기능을 만들어 보세요.
댓글이 닫혀있다.