MS Excel 용 고급 VBA 가이드

VBA를 시작한 경우 초보자를위한 VBA 가이드 에 대해 공부하는 것이 좋습니다. 그러나 노련한 VBA 전문가이고 Excel에서 VBA로 할 수있는 고급 기능을 찾고 있다면 계속 읽으십시오.

Excel에서 VBA 코딩을 사용하는 능력은 전 세계를 열어줍니다 자동화 Excel, 푸시 버튼에서 계산을 자동화하고 이메일을 보낼 수도 있습니다. VBA를 사용하여 일상 업무를 자동화 할 가능성이 더 많습니다.

Microsoft Excel 용 고급 VBA 가이드

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

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

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

사용자가 버튼을 누르면 결과 계산

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

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

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

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

In_content_1 all : [300x250] / dfp : [640x360]->

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 개체를 선택합니다. 개체 및 방법 드롭 다운 상자에서 워크 시트및 활성화를 선택합니다. 스프레드 시트를 열 때마다 코드가 실행됩니다.

하위 워크 시트 _Activate ()함수를 만듭니다. 위 코드를 해당 함수에 붙여 넣습니다.

활성 워크 시트를 시트 1으로 설정하고 시트를 지우고 strFile변수로 정의한 파일 경로를 사용하여 파일에 연결 한 다음 With루프는 파일의 모든 줄을 순환하며 셀 A1에서 시작하여 시트에 데이터를 배치합니다.

이 코드를 실행하면 CSV 파일 데이터가 표시됩니다. 시트 1의 빈 스프레드 시트로 가져옵니다.

가져 오기는 첫 번째 단계 일뿐입니다 . 다음으로 계산 결과를 포함 할 열에 대한 새 헤더를 작성하려고합니다. 이 예에서는 각 품목의 판매에 대해 지불 된 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로 유지하면서 위 섹션의 세금 계산 코드를이 함수에 붙여 넣습니다. 활성 시트를 정의하려면 다음 두 줄을 포함해야합니다.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

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

해당 버튼을 두 번 클릭하고 동일한 코드를 붙여 넣지 만 세금 승수를 0.07로 만듭니다.

이제 어떤 버튼을 누르면 세금 열이 표시됩니다

한 후에는 시트에 두 개의 푸시 버튼이 있습니다. 그들 각각은 다른 세금 계산을 시작하고 결과 열에 다른 결과를 씁니다.

텍스트를 작성하려면 개발자메뉴를 선택하고 디자인 모드를 선택하여 리본의 컨트롤 그룹에서 디자인 모드를 사용 중지합니다. 푸시 버튼이 활성화됩니다.

“세금”결과 열이 어떻게 바뀌는 지 보려면 각 푸시 버튼을 선택하십시오.

전자 메일 계산 결과를 누군가에게

무엇을 스프레드 시트의 결과를 이메일을 통해 다른 사람에게 보내려면

위와 동일한 절차를 사용하여 보스에게 전자 메일 시트라는 다른 버튼을 만들 수 있습니다. 이 버튼의 코드에는 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 = "[email protected]"
strTo = "[email protected]"
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("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 protected]" .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  .Update End With With CDO_Mail Set .Configuration = CDO_Config End With

1 교체및 자신의 계정 세부 정보가 포함 된 비밀번호입니다.

마지막으로 이메일 전송을 시작하려면 다음 코드를 삽입하십시오.

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 자동화를 만들어보십시오.

배열의 연산( operations of Array)

관련 게시물: