MS Excel용 고급 VBA 가이드

VBA를 막 시작했다면 초보자를 위한 VBA 가이드 공부를 시작하고 싶을 것입니다. 그러나 노련한 VBA 전문가이고 Excel에서 VBA로 수행할 수 있는 고급 작업을 찾고 있다면 계속 읽으십시오.

Excel에서 VBA 코딩을 사용하는 기능은 자동화의 세계를 열어줍니다. Excel에서 계산을 자동화하고 버튼을 누르고 이메일을 보낼 수도 있습니다. VBA로 일상 업무를 자동화할 수 있는 가능성은 생각보다 많습니다.

Microsoft Excel용 고급 VBA 가이드

Excel에서 VBA 코드를 작성하는 주요 목표는 스프레드시트에서 정보를 추출하고 이에 대해 다양한 계산을 수행한 다음 결과를 스프레드시트에 다시 쓸 수 있도록 하는 것입니다.

다음은 Excel에서 가장 일반적으로 사용되는 VBA입니다.

데이터 가져오기 및 계산 수행
사용자가 버튼을 누르면 결과 계산
계산 결과를 다른 사람에게 이메일로 보내기

이 세 가지 예를 통해 다양한 고급 Excel VBA 코드를 작성할 수 있을 것입니다.

데이터 가져오기 및 계산 수행

사람들이 Excel을 사용하는 가장 일반적인 작업 중 하나는 Excel 외부에 있는 데이터에 대한 계산을 수행하는 것입니다. VBA를 사용하지 않는 경우 데이터를 수동으로 가져와서 계산을 실행하고 해당 값을 다른 시트나 보고서로 출력해야 합니다.

VBA를 사용하면 전체 프로세스를 자동화할 수 있습니다. 예를 들어 매주 월요일 컴퓨터의 디렉토리에 새 CSV 파일을 다운로드한 경우 화요일 아침에 스프레드시트를 처음 열 때 VBA 코드가 실행되도록 구성할 수 있습니다.

다음 가져오기 코드가 실행되고 CSV 파일을 Excel 스프레드시트로 가져옵니다.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:\temp\purchases.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으로 설정됩니다. , 시트 지우기, strFile로 정의한 파일 경로를 사용하여 파일에 연결 변수를 선택한 다음 With 루프는 파일의 모든 줄을 순환하고 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")

'Find Last Row and Column
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) = "taxes"

For Each cell In rng
     fltTax = cell.Value * 0.05
     Cells(rowCounter, 5) = fltTax
     rowCounter = rowCounter + 1
Next cell

이 코드는 데이터 시트에서 마지막 행을 찾은 다음 데이터의 첫 번째 및 마지막 행에 따라 셀 범위(판매 가격이 있는 열)를 설정합니다. 그런 다음 코드는 각 셀을 순환하고 세금 계산을 수행하고 결과를 새 열(열 5)에 씁니다.

위의 VBA 코드를 이전 코드 아래에 붙여넣고 스크립트를 실행합니다. E열에 결과가 표시됩니다.

이제 Excel 워크시트를 열 때마다 자동으로 실행되어 CSV 파일에서 최신 데이터 복사본을 가져옵니다. 그런 다음 계산을 수행하고 결과를 시트에 기록합니다. 더 이상 수동으로 아무것도 할 필요가 없습니다!

버튼 누름으로 결과 계산

시트가 열릴 때 자동으로 실행되는 것보다 계산이 실행되는 시기를 더 직접적으로 제어하려면 대신 제어 버튼을 사용할 수 있습니다.

제어 버튼은 사용되는 계산을 제어하려는 경우에 유용합니다. 예를 들어 위와 같은 경우에 한 지역에 대해 5% 세율을 사용하고 다른 지역에 7% 세율을 사용하려면 어떻게 합니까?

동일한 CSV 가져오기 코드가 자동으로 실행되도록 할 수 있지만 적절한 버튼을 누를 때 세금 계산 코드가 실행되도록 둡니다.

위와 동일한 스프레드시트를 사용하여 개발자를 선택합니다. 탭에서 삽입을 선택합니다. 컨트롤에서 리본의 그룹입니다. 푸시 버튼 선택 드롭다운 메뉴에서 ActiveX 컨트롤.

데이터가 들어갈 위치에서 멀리 떨어진 시트 부분에 푸시버튼을 그립니다.

푸시 버튼을 마우스 오른쪽 버튼으로 클릭하고 속성을 선택합니다. . 속성 창에서 캡션을 사용자에게 표시할 내용으로 변경합니다. 이 경우 5% 세금 계산이 될 수 있습니다. .

푸시 버튼 자체에 이 텍스트가 반영된 것을 볼 수 있습니다. 속성 닫기 창을 클릭하고 푸시 버튼 자체를 두 번 클릭합니다. 이렇게 하면 코드 편집기 창이 열리고 사용자가 푸시 버튼을 눌렀을 때 실행될 함수 안에 커서가 있습니다.

위 섹션의 세금 계산 코드를 이 함수에 붙여넣고 세율 승수를 0.05로 유지합니다. 활성 시트를 정의하려면 다음 2줄을 포함하는 것을 잊지 마십시오.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

이제 프로세스를 다시 반복하여 두 번째 푸시 버튼을 만듭니다. 캡션을 7% 세금 계산으로 만듭니다. .

해당 버튼을 두 번 클릭하고 동일한 코드를 붙여넣되 세금 배율을 0.07로 설정합니다.

이제 어떤 버튼을 누르느냐에 따라 세금 열이 그에 따라 계산됩니다.

완료되면 시트에 두 개의 푸시 버튼이 있습니다. 그들 각각은 다른 세금 계산을 시작하고 다른 결과를 결과 열에 기록합니다.

문자 메시지를 보내려면 개발자를 선택하세요. 메뉴에서 디자인 모드를 선택합니다. 리본에서 컨트롤 그룹을 구성하여 디자인 모드를 비활성화합니다. . 그러면 푸시 버튼이 활성화됩니다.

각 푸시 버튼을 선택하여 "세금" 결과 열이 어떻게 변경되는지 확인하십시오.

계산 결과를 누군가에게 이메일로 보내기

스프레드시트의 결과를 이메일로 다른 사람에게 보내려면 어떻게 해야 합니까?

Email Sheet to Boss라는 다른 버튼을 만들 수 있습니다. 위의 동일한 절차를 사용합니다. 이 버튼의 코드에는 Excel CDO 개체를 사용하여 SMTP 이메일 설정을 구성하고 사용자가 읽을 수 있는 형식으로 결과를 이메일로 보내는 작업이 포함됩니다.

이 기능을 사용하려면 도구 및 참조를 선택해야 합니다. . Windows 2000 라이브러리용 Microsoft CDO까지 아래로 스크롤합니다. , 활성화하고 확인을 선택합니다. .

이메일을 보내고 스프레드시트 결과를 포함하기 위해 생성해야 하는 코드에는 세 가지 주요 섹션이 있습니다.

첫 번째는 제목, 받는 사람 및 보낸 사람 주소, 이메일 본문을 담을 변수를 설정하는 것입니다.

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

Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "MyEmail@gmail.com"
strTo = "BossEmail@gmail.com"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

물론 본문은 시트에 어떤 결과가 있는지에 따라 동적이어야 하므로 여기에서 범위를 통과하고 데이터를 추출하고 본문에 한 번에 한 줄씩 쓰는 루프를 추가해야 합니다.

Set StartCell = Range("A1")

'Find Last Row and Column
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
strBody = strBody & 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)입니다.

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
.Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

email@website.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

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

참고 :이 코드를 실행하려고 할 때 전송 오류가 표시되면 Google 계정이 "보안 수준이 낮은 앱" 실행을 차단하고 있기 때문일 수 있습니다. 보안 수준이 낮은 앱 설정 페이지를 방문하여 이 기능을 켜야 합니다.

활성화되면 이메일이 전송됩니다. 자동으로 생성된 결과 이메일을 받는 사람에게 다음과 같이 표시됩니다.

보시다시피 Excel VBA를 사용하여 실제로 자동화할 수 있는 항목이 많이 있습니다. 이 기사에서 배운 코드 조각을 가지고 놀고 고유한 VBA 자동화를 만들어 보세요.